8

I am very new to PDO, sorry if you feel I am asking stupid question.
Normal and simple PDO Prepared statement without Bind_param :

$sql = $db->prepare('SELECT * FROM employees WHERE name = ?');
$sql->execute(array($name));
$rows = $sql->fetchAll();

with Bind_param :

$sql->bind_param("s", $name); //s means the database expects a string

I heard people said : "The protection comes from using bound parameters, not from using prepared statement". May I know what is bound parameters? Bind_param is bound parameter? If yes, then the normal and simple PDO Prepared statement without Bind_param CANNOT fully prevent SQL injection?

zac1987
  • 2,721
  • 9
  • 45
  • 61
  • I have not used PDO myself, but I was pretty sure it was safe for (most) injections? And you are missing a quote in your prepare-statement. – OptimusCrime Oct 27 '11 at 12:44
  • Reason why PDO is safe is because it runs PDO::quote() function on any parameters that you pass. In your 1st code example, you passed an array of values to `execute` function. PDO will run the `quote` function and it will treat all parameters as **strings**. So yes, you are doing it right except you won't be able to write NULL values to your db (integers can be quoted and will be treated as such). Alternatively, you can **bind parameters manually** and tell the PDO how to treat those parameters - as integer, string or NULL (and special types for stored procedures such as IN / OUT / INOUT). – N.B. Oct 27 '11 at 12:48
  • @N.B. care to learn the matter better? – Your Common Sense Oct 27 '11 at 12:53
  • @N.B, since pdo will treat all prarameters as strings, so bind_param("s", $name); is just an extra code which is useless... – zac1987 Oct 27 '11 at 12:59
  • 1
    Your `->bind_param("s", ...)` syntax belongs to `mysqli`, not `PDO`. (But same thing applies of course). It's true that you rely on SQL type coercion when passing all parameters via a single `->execute()` versus specifying the type explicitely. (Effect depends on the table scheme at question of course.) – mario Oct 27 '11 at 13:00
  • @Col. Shrapnel - comments allow only 600 characters, I can't explain everything to detail so programming nazis like you can be satisfied. So please take your arrogant attitude somewhere else and learn how to behave before acting smart. Zac, you can use `PDOStatement::bindValue` method to explicitly bind parameters to the proper type. – N.B. Oct 27 '11 at 13:01
  • @N.B. ok, in short - it doesn't use PDO::quote(). at least when in native mode. that's the whole point of native prepared statements - you don't have to quote anything. when it only emulate prepares - it does escaping though, like you said – Your Common Sense Oct 27 '11 at 13:05
  • @Col. Shrapnel - what's the point in going to details such as these when the person asking the question is wondering **how** is that PDO is safe for preventing sql injection? You haven't mentioned those in your answer nor have you given any examples to back up your statements. How's it helping a person new to PDO? Native prepared statements, emulating prepares and so on, those sound like jibberish to someone who doesn't know a thing about them, of course I won't go such details trying to outline how something works in theory. – N.B. Oct 27 '11 at 13:12
  • @N.B. it is not for the OP, it is for you, whose statement `PDO is safe is because it runs PDO::quote() function on any parameters` is not [entirely] true. – Your Common Sense Oct 27 '11 at 13:13
  • Oh, I also heard that emulating prepares is fake prepared statement... Can fake prepared statement prevent SQL injection fully? If it cannot, how do I avoid the fake prepared statement? – zac1987 Oct 27 '11 at 13:19
  • @Col. Shrapnel - aren't you nice, explaining it to, oh poor uknowledgeable me. Thank god we have a genuine Robin Hood to the rescue :) too bad you can't think out of the box tho. – N.B. Oct 27 '11 at 13:21
  • `$db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );` to avoid. However, emulated would serve you as well. The only thing you'll be unable to do is to bind LIMIT clause variables using execute(). – Your Common Sense Oct 27 '11 at 13:34
  • do I really need to set emulate to false? If I don't set it, SQL injection will not be prevented? – zac1987 Oct 27 '11 at 13:54
  • It is okay, I found the answer at http://blog.ulf-wendel.de/?p=187#pdo – zac1987 Oct 28 '11 at 20:06

6 Answers6

8

You're doing it right. The bound parameters are the one declared in a "prepared statement" using ?. Then they are bound using execute() with their value as a parameter to be bound to the statement.

Tom
  • 1,647
  • 11
  • 24
5

The protection comes from using bound parameters, not from using prepared statement

Means it is not enough just to use prepare() but keep all variables in the query like this:

$sql = $db->prepare("SELECT * FROM employees WHERE name ='$name'");
$sql->execute();
$rows = $sql->fetchAll();

Someone who said that meant "although technically you are using a prepared statement, you aren't binding variables to it". So it makes the query vulnerable all the same.

To be protected, you have to substitute all variables in the query with placeholders, and then bind them:

$sql = $db->prepare("SELECT * FROM employees WHERE name = ?");
$sql->bindParam(1, $name);
$sql->execute();
$rows = $sql->fetchAll();

However, PDO has a nice shorthand for binding, allowing you to avoid the repetitive calls to bindParam()/bindValue(), doing all these calls internally when you send variables into execute():

$sql = $db->prepare('SELECT * FROM employees WHERE name = ?');
$sql->execute(array($name));
$rows = $sql->fetchAll();

It does essentially the same binding as bindParam()/bindValue() does. Thus your code is using binding and therefore safe

Finally, bind_param() is actually a mysqli function that has nothing to do with PDO.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
2

That is true.

I have no expert information on this but from what I understand, the problem with SQL injection is that the SQL server receives a string and regards it as true. The server has no means of knowing if, for instance, the DUMP commands were made intentionally or not.

With bound parameters, you say to the SQL server "Hey look, this is the query, and I expect parameters here, here and there. Oh and btw, here are the values". This approach is different because SQL now knows the actual expression it has to execute and what the values are. This allows SQL to insert the values into the expression, without modifying the expression itself.

Mike
  • 2,567
  • 3
  • 23
  • 35
1

OWASP gave me the same doubt, following their guidelines against SQL injection "SQL_Injection_Prevention_Cheat_Sheet" they say:

Defense Option 1: Prepared Statements (with Parameterized Queries):

  • PHP – use PDO with strongly typed parameterized queries (using bindParam())

so it seems as if you should use bind_param() at all times. I don't use it because I converted thousands of vulnerable DAOs with an automated script and bind_param() would require me to hand edit them all.

I have yet to see examples of injection without bind_param() used, so I am confident it is not necessary.

sarah.ferguson
  • 3,167
  • 2
  • 23
  • 31
  • 1
    There are no such examples. OWASP is a highly unreliable source. The page you are referring to is just an awful collection of rumors and superstitions. – Your Common Sense Mar 01 '16 at 18:32
0

Yes, you DON`T have to bind parameters to make sure you are protected from SQL injection attacks. The manual tells us: "Also, calling PDO::prepare() and PDOStatement::execute() helps to prevent SQL injection attacks by eliminating the need to manually quote and escape the parameters." - PDO::prepare. Enough said.

Binding parameters is just a handy way of making your SQL query re-usable - you can bind anything to your 'placeholders' once you put them into your SQL query - a great example is here: W3school example.

But once again, the job is done using prepare and execute.

Vadim Anisimov
  • 121
  • 8
0

You're doing it right.

The wrong way:

$sql = $db->query('SELECT * FROM employees WHERE name = '.$name); //WRONG WRONG HORRIBLE
gnud
  • 77,584
  • 5
  • 64
  • 78