CODEWITHSUNDEEP
phpmysqlpdo
zac1987
zac1987

Reputation: 2777

Can I fully prevent SQL injection by PDO Prepared statement without bind_param?

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?

Upvotes: 9

Views: 17537

Answers (6)

Your Common Sense
Your Common Sense

Reputation: 157890

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.

Upvotes: 5

Vadim Anisimov
Vadim Anisimov

Reputation: 121

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.

Upvotes: 0

sarah.ferguson
sarah.ferguson

Reputation: 3257

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.

Upvotes: 1

Mike
Mike

Reputation: 2587

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.

Upvotes: 2

gnud
gnud

Reputation: 78568

You're doing it right.

The wrong way:

$sql = $db->query('SELECT * FROM employees WHERE name = '.$name); //WRONG WRONG HORRIBLE

Upvotes: 0

Tom
Tom

Reputation: 1711

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.

Upvotes: 8

Related Questions