Can I fully prevent SQL injection by PDO Prepared statement without bind_param?
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.
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.
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.