4

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?

Ashley Banks
  • 528
  • 5
  • 16
  • duplicate of: http://stackoverflow.com/questions/1996344/is-preventing-xss-and-sql-injection-as-easy-as-does-this – phpmeh Feb 06 '12 at 15:21
  • 1
    @AshleyBanks: I think the simplest way to explain it to the other people is that there is no separation of query and parameters in your example (which is what prevents the injection) you're just merging everything into the query (Hello injection!). The DB engine says to itself, this bit is query, this bit is parameter, they cannot change roles and cannot merge together. If they still don't get it, real live examples will do the trick, seeing is believing after all. – Leigh Feb 06 '12 at 15:45

3 Answers3

5

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.

Linus Kleen
  • 33,871
  • 11
  • 91
  • 99
2

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

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

xdazz
  • 158,678
  • 38
  • 247
  • 274
0

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.

phpmeh
  • 1,752
  • 2
  • 22
  • 41