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

By on   1 comment 350 words, read ~158 times.

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:

$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:

$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.

$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!


Share this post on…

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

  1. says:

    @Edent Consider using queryBuilder->createPositionalParameter() instead. It handles incrementing the positional value for you so you don't have to track that when passing around a query builder or making a condition optional.

    But yes, safely handling parameterized queries is one of the things I dislike about doctrine that I think they should have made better by default.

    Reply

What are your reckons?

All comments are moderated and may not be published immediately. Your email address will not be published.

␃␄