PDO not filter SQL Injection

Question

I have an Application with PHP 5.3.29 and MySQL 5.6.35. I used SQLQUERY to execute SQL instrucctions, then change to PDO with prepared Statements to avoid SQL-i, but whe i test my app with ZAP 2.6.0, i can confirm that the SQL-I still happens, despite the use of "PDO" and "prepare". I activated the general log at MySQL and looked for all statements that were executed.

My code is:

function cerrar_sesion($usuario) { 
$pdo = new 
PDO("mysql:"."host=".DB_SERVIDOR.";"."dbname=".DB_BASEDATOS,DB_USUARIO, DB_CLAVE);
$query = $pdo->prepare('UPDATE ADMIN_USUARIO SET USERID=\' \' WHERE C_USUARIO= :usuario'); 
$query->bindParam(':usuario',$usuario,PDO::PARAM_INT); 
$query->execute(); 
$pdo = null;
.........
}

Checking the DB log i see the parameter "C_USUARIO" changed, the following 3 lines were extracted from MySQL Log:

227726 Query UPDATE ADMIN_USUARIO SET USERID=' ' WHERE C_USUARIO= '54/2' 227730 Query UPDATE ADMIN_USUARIO SET USERID=' ' WHERE C_USUARIO= '108/2' 227732 Query UPDATE ADMIN_USUARIO SET USERID=' ' WHERE C_USUARIO= '108/2'

Note the values for C_USUARIO should't have "/2", that was injected by ZAP

I expected PDO to prevent the injection, but this wasn't the case, how can i do this using PDO?

Please help me, i´ll apreciate it.

Answer 1

By default, PDO "emulates" prepared statements, by interpolating the bound variables into your SQL query string, and then executing that SQL directly, without using parameters.

PDO does apply correct escaping as it interpolates your variables in the query, so it is safe with respect to SQL injection.

If you want real parameterized queries, disable emulation:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

See http://php.net/manual/en/pdo.setattribute.php for more information.

If you disable emulation, your MySQL query log will show the PREPARE and EXECUTE as separate steps. But MySQL will also log the full query including parameter values. This is also safe, it's just a convenience that MySQL does for the sake of logging, because it's useful to show the query with values. See example in my answer to https://stackoverflow.com/a/210693/20860.