Doctrine - difference between bindValue() and setParameter() on prepared statements

This pissed me off and I couldn't figure out what I was doing wrong. So I'm blogging about my ignorance.

Imagine you're using Symfony and Doctrine to access a database. You are using prepared statements to prevent any SQL injection problems.

There are two main ways of doing this - and they disagree about how positional variables should be specified.

Data Retrieval And Manipulation

Here's a fairly trivial SQL statement with a couple of variables:

 PHP$sql = "SELECT `userID` FROM `users` WHERE `firstname` LIKE ? AND `surname` LIKE ?";

$stmt = $conn->prepare($sql);

$stmt->bindValue(1, $user_input_1st_name);

$stmt->bindValue(2, $user_input_2nd_name);

$results = $stmt->executeQuery();

Pretty easy, right? Write your SQL as normal, but place ? where you want user supplied variables to be. This uses bindValue() to set the variables in the query.

The approach using question marks is called positional, because the values are bound in order from left to right to any question mark found in the previously prepared SQL query. That is why you specify the position of the variable to bind into the bindValue() method Doctrine: Data Retrieval And Manipulation

Query Builder

Doctrine also offer an SQL Query Builder - it looks like this:

 PHP$queryBuilder = $conn->createQueryBuilder();

$queryBuilder

    ->select("userID")

    ->from("users")

    ->where("firstname LIKE ? AND surname LIKE ?")

    ->setParameter(0, $user_input_1st_name)

    ->setParameter(1, $user_input_2nd_name);

$results = $queryBuilder->executeQuery();

Notice the difference? Yes! setParameter() is zero based!

The numerical parameters in the QueryBuilder API start with the needle 0. SQL Query Builder

Why the difference?

Because the universe hates you, I guess?

Solving the issue

I've sent a pull request to make the documentation clearer. In the meantime, both methods accept named parameters.

 PHP$sql = "SELECT `userID` FROM `users` WHERE `firstname` LIKE :first";

$stmt->bindValue("first", $user_input_1st_name);



...



$queryBuilder

    ->select("userID")

    ->from("users")

    ->where("firstname LIKE :first")

    ->setParameter("first", $user_input_1st_name)

I hope that helps remove some confusion for future users. Even if it's only me!