Blog: Propel Queries: Now With Manual Binding, Too

The Propel Team – 10 November 2011

Propel is quite good at guessing the binding type to use in your queries. But sometimes you need to force a binding type which is not the one Propel would have guessed. Starting with the next minor release (1.6.4), Propel will be able to do it.

Propel Guesses Binding Types From Your Schema

Consider the following query:

$books = BookQuery::create()
    ->filterByTitle('War%')
    ->filterByPrice(array('max' => 20))
    ->find();

Propel translates this query into the following SQL prepared statement:

SELECT book.* FROM book
 WHERE book.TITLE LIKE ?
   AND book.PRICE < ?

Then, when you call find(), Propel uses PDO to bind the question mark placeholders with the values used in the filterByXXX() methods. Propel uses the binding type of the column as declared in the schema. Continuing on the previous example, where the book.TITLE column is a VARCHAR and the book.PRICE column is a INTEGER, Propel binds the values as follows:

$stmt = $con->prepare($sql);
$stmt->bindValue(1, 'War%', PDO::PARAM_STR); // book.TITLE is a  VARCHAR
$stmt->bindValue(2, 20, PDO::PARAM_INT);     // book.PRICE is an INTEGER
$stmt->execute();

But what if you want to use another binding type?

Cases When You Need a Custom Binding Type

The filterByXXX() methods are always tied to a column, so for these Propel always knows what binding to use. However, when you use the relational API, you can create conditions on more than just columns.

For instance:

$books = BookQuery::create()
  ->where("LOCATE('War', Book.Title) = ?", true)
  ->find();

In this case, the binding should use PDO::PARAM_BOOL, and not PDO::PARAM_STR, which is the type Propel uses for the book.TITLE column, declared as VARCHAR.

Another example is when using having():

$books = BookQuery::create()
  ->withColumn('SUBSTRING(Book.Title, 1, 4)', 'title_start')
  ->having('title_start = ?', 'foo')
  ->find();

Here, Propel simply refuses the query, failing with a loud:

PropelException: Cannot determine the column to bind to the parameter in clause 'title_start = ?'

This is because the virtual column title_start has no intrinsec type, so Propel cannot determine which binding to use.

Concretely, that means that the having() support is somehow broken in Propel 1.6. Apart from concatenating the value to the SQL clause (and risking SQL injection), you cannot add a HAVING clause using ActiveQuery...

Forcing a Custom Binding Type.

...until now. A way to force a custom binding type has just made its way to the Propel 1.6 master branch - and that means that it will be available in Propel 1.6.4.

It's as simple as it should be: just add the desired binding type as third parameter of either where() or having(), and you're good to go:

// custom binding in where()
$books = BookQuery::create()
  ->where("LOCATE('War', Book.Title) = ?", true, PDO::PARAM_BOOL)
  ->find();

// custom binding in having()
$books = BookQuery::create()
  ->withColumn('SUBSTRING(Book.Title, 1, 4)', 'title_start')
  ->having('title_start = ?', 'foo', PDO::PARAM_STR)
  ->find();

No more errors, no more SQL injection risk. You're in control of the binding type when you need it.

More than ever, there is no limit to what you can do with Propel - and less limits to what you can do with the awesome PropelQuery API.