Search code examples
mysqlsymfony-1.4criteriapropelmysql-real-escape-string

Propel / Symfony 1.4: combine securely custom sql subquery with criteria

I want to insert a custom sql subquery into a common Propel criteria. The subquery contains user inputs, so these have to be escaped. How can I realize this?

I have something like this (simplyfied):

$c = new Criteria();
$c->add(myTablePeer::CUSTOMER_ID, 123);
$c->add... //several other criterions, joins etc.
$subQuery = "(SELECT SUM(id) FROM my_other_table WHERE customer_id = 123) > '[USERINPUT]'";
$c->add("", $subQuery, Criteria::CUSTOM);

So, of course, this subquery is vulnerable to SQL injects. How can I secure this query in the best way? I cannot use mysqli_real_escape_string(), can I? So just addslashes() and that's it?

Solution

  • Halleluja, I think I found the solution:

    Propel::getConnection()->quote($userInput)

    This does the magic. Quoting and escaping in one step! So the subQuery would be:

    $subQuery = "(SELECT SUM(id) FROM my_other_table WHERE customer_id = 123) > ".Propel::getConnection()->quote($userInput);

    Can someone verifiy, that this is the best way to secure the input?