PDO Prepare statements

Question

Can I just quickly get clarification on this, just a discussion I am having:

Using this as an example:

$conn->prepare ( 'SELECT * FROM table WHERE id = "' . $_POST['id'] . '"' );

Does not prevent SQL injection, you must bind the parameters or sanitize the values before putting into the prepare statement? Or am I wrong, and it's perfectly ok to just use prepare?

Answer 1

Prepared statements use placeholders for values to be inserted. The code snippet in your question already interpolates the value into the query and is thus prone to SQL injection.

The following pseudo-code highlights prepared statements:

$stmt = $conn->prepare('SELECT * FROM `table` WHERE `id` = ?');
$stmt->execute($_POST['id']);

In this example, the logic behind this "code" would take care of properly quoting whatever is in $_POST['id'] and substituting the question mark ? with that. You might also encounter the following placeholders:

$stmt = $conn->prepare('SELECT * FROM `table` WHERE `id` = :id');
$stmt->execute(array(
    'id' => $_POST['id']
));

Note, however, that prepared statements do not relieve you of your duty to validate user-provided input before passing it along to a (My)SQL statement: if id is expected to be an integer, only accept integers as input.

Answer 2

Right, you have to bind the parameters to benefit from PDO's sql injection protection.

And remember that PDO isn't adding htmlspecialchars, so if that's important to you, you have to do it yourself.

Answer 3

Yes, it does not prevent SQL injection, you should use

$conn->prepare ( 'SELECT * FROM table WHERE id = ?' );