Search code examples
phpdoctrinesql-injectionquery-builderdbal

DBAL QueryBuilder : what is the safier way to avoid SQL injection?

According to the DBAL Documentation, only the method setFirstResult and setMaxResults are designed to be safe from SQL injections. But in QueryBuilder section they mention that to safely work with the QueryBuilder we have to pass user inputs with the setParameter method. So i see 2 ways to doing that, but i don't know if they have real difference or not : Way 1 :

$qb->select('USR_id', 'USR_email')
->from('T_user_USR')
->where('USR_email = ? ')
->setParameter(0, $email);
$stmtQb = $qb->execute();
$results = $stmtQb->fetchAll();

Way 2 :

$qb->select('USR_id', 'USR_email')
->from('T_user_USR')
->where('USR_email = ? ');
$stmtQb = $dbal->prepare($qb->getSQL());
$stmtQb->bindValue(1, $email);
$stmtQb->execute();
$results = $stmtQb->fetchAll();

What's the best way to do it? Thanks in advance

Solution

  • The two examples eventually do the same thing. That is, when you use setParameter() the parameter value is bound to the statement using bindValue().

    See https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Connection.php#L1326-L1356 for the internal function that does this in DBAL.

    The only reason to use the second form is if you want to use bindParam() instead of bindValue(). You would do this if you wanted to bind the parameter to a PHP variable by reference, for example so you could prepare a query once before starting a loop, and then execute it many times in the loop.

    Both methods give the same amount of protection with respect to SQL injection.