Multiple parameters in Zend_Db::quoteInto()

Zend_Db has a nice function called quoteInto(). As it is described in the documentation - "The method simply interpolates the value into the string, escapes special characters, and applies quotes around it.".

Let's say you have an sql query which looks like this:

  1. SELECT * FROM users WHERE enabled = ? AND country = ?

You wouldn't be able to use the native quoteInto function to replace both question marks in the sql without processing the same string twice:

  1. $users = new Users();
  2. $sql = 'SELECT * FROM users WHERE enabled = ? AND country = ?';
  3. $sql = $users->getAdapter()->quoteInto($sql, 1);
  4. $sql = $users->getAdapter()->quoteInto($sql, 'UK');

This would get you

  1. SELECT * FROM users WHERE enabled = 1 AND country = 'UK'

Unfortunately this is not the best way to do it. Imagine what the code would look like if you needed to replace 3, 5 or even more placeholders.

This is where our custom extension to Zend_Db_Table_Abstract comes in handy.

We do it by extending the native Zend_Db_Table_Abstract class and adding the quoteInto function:

  1. <?php
  2.  
  3. class Codeaid_Db_Table_Abstract extends Zend_Db_Table_Abstract
  4. {
  5.  
  6.     /**
  7.      * Quote values and place them into a piece of text with placeholders
  8.      *
  9.      * The placeholder is a question-mark; all placeholders will be replaced
  10.      * with the quoted value.
  11.      *
  12.      * Accepts unlimited number of parameters, one for every question mark.
  13.      *
  14.      * @param string $text Text containing replacements
  15.      * @return string
  16.      */
  17.     public function quoteInto($text)
  18.     {
  19.         // get function arguments
  20.         $args = func_get_args();
  21.  
  22.         // remove $text from the array
  23.         array_shift($args);
  24.  
  25.         // check if the first parameter is an array and loop through that instead
  26.         if (isset($args[0]) && is_array($args[0])) {
  27.             $args = $args[0];
  28.         }
  29.  
  30.         // replace each question mark with the respective value
  31.         foreach ($args as $arg) {
  32.             $text = preg_replace('/\?{1}/', $this->_db->quote($arg), $text, 1);
  33.         }
  34.  
  35.         // return processed text
  36.         return $text;
  37.     }
  38.  
  39. }

Save this class in ./library/Codeaid/Db/Table/Abstract.php file.

Next step is to extend Codeaid_Db_Table_Abstract instead of Zend_Db_Table_Abstract in your models. So, for example, if you had a model called Users before then it's declaration is

  1. class Users extends Zend_Db_Table_Abstract

All you have to do is change it to

  1. class Users extends Codeaid_Db_Table_Abstract

After you do that you will be able to quote multiple parameters into one string.

The previous example can now be written as

  1. $users = new Users();
  2. $sql = 'SELECT * FROM users WHERE enabled = ? AND country = ?';
  3. $sql = $users->quoteInto($sql, 1, 'UK');

The number of parameters is unlimited yet the first parameter must allways be the string containing placeholders.

If you don't like or can not pass multiple parameters to the function then there is an alternative to it - you can pass an array containing all replacements as the second parameter.

  1. $users = new Users();
  2. $sql = 'SELECT * FROM users WHERE enabled = ? AND country = ?';
  3. $replacements = array(1, 'UK');
  4. $sql = $users->quoteInto($sql, $replacements);

It will produce exactly the same result as if you did

  1. $users->quoteInto($sql, 1, 'UK');

If you accidentially pass smaller number of replacements than you have question marks in your string then the missing number of replacements will not be changed (you will still have the question marks in the string).

  1. $users->quoteInto('SELECT ?, ?, ? FROM users', 'a', 'b');

will produce

  1. SELECT a, b, ? FROM users

Also, if you pass more replacements than you have placeholders in your string then the extra replacements will be ignored.

  1. $users->quoteInto('SELECT ?, ?, ? FROM users', 'a', 'b', 'c', 'd', 'e');

will produce

  1. SELECT a, b, c FROM users

Remember that you do not call the quoteInto() function on the table's adapter ($users->getAdapter()->quoteInto()) anymore but rather on the table itself ($users->quoteInto()), which makes the code a bit neater as well!

Hope this helps.

Don't forget to check out other Zend Framework articles!

Comments
1
you got a bug in your function, it will be incorrect result if question mark ? will present in sql string.. be carefully.
r3verser, January 13th 2011, 2:47
2
e.g "SELECT * FROM posts WHERE title LIKE ?" and inputed string for like e.g "some blog post?", it will cause error.
r3verser, January 13th 2011, 2:49
3
here is patched one:
	 public function quoteInto($text)
	 {
		  // get function arguments
		  $args = func_get_args();
		  // remove $text from the array
		  array_shift($args);
		  // check if the first parameter is an array and loop through that instead
		  if (isset($args[0]) && is_array($args[0])) {
			   $args = $args[0];
		  }
		  // replace each question mark with the respective value
		  foreach ($args as $arg) {
			   //add slash before ? for prevent double quoting
					$arg = addcslashes($this->quote($arg), '?');
					$text = preg_replace('/(?<!\\\\)\?/', $arg, $text, 1);
		  }
		  // return processed text
		  return $text;
	 }
r3verser, January 13th 2011, 3:26
4
r3verser,
change 
$arg = addcslashes($this->quote($arg), '?');

to
$arg = addcslashes($this->_db->quote($arg), '?');
Fooster, October 5th 2011, 10:56
5
I'm ashamed that I never thought of this scenario. I've been using this function in my projects for quite a while and never encountered this issue. However, that doesn't mean the bug is not there.

I like your solution, r3verser but I also thought about another one. We could "remember" all initial positions of question marks and then use substr_replace() to replace them with the arguments one by one starting with the last one so that the offsets of the previous ones don't change.

That way we wouldn't have to use preg_replace, which I suppose is slower that substr_replace and we wouldn't care what the strings we are inserting contain.

However, the original SQL query could still contain question marks. E.g. "SELECT * FROM table WHERE field1 = 'Who?' AND field2 = ?". In this case we only need to replace the second question mark and not the first one, which makes the whole thing a bit trickier. 

I guess the proper way to do it would be to exclude strings between single quotes and double quotes when replacing question marks. I would expect Zend to do it already. But like I said - I haven't really encountered this issue and hence I haven't tested it.
Andris, October 11th 2011, 12:36
6
Hi! My issue is:

$where = $this->getAdapter()->quoteInto("id = ?", $id);
$where .= " AND ";
$where .= $this->getAdapter()->quoteInto("id = ?", 1);
StanislaS, November 20th 2011, 18:26
7
What is the exact issue? The only issue I can see is "WHERE id = 2 AND id = 1" won't fetch you any results (if $id is not 1).
Andris, November 21st 2011, 12:24
Name
Email (required)
will not be published
Website
Recaptcha
you will only be required to fill it in once in this session

You can use [code][/code] tags in your comments