6

I am using key as a column name in a MySQL table.

Since this is reserved, it needs to be escaped properly to be used in a query:

… WHERE `key` = 'test'

Manually this is no problem, but I am using the Zend Framework and want to have it handle the escape correctly, like this:

$table = new Application_Model_ATable();
$table->fetchRow ( $table->select()->where('key = ?','test') );

So the question is:

How to quote/escape column names with Zend_Db_Table?

favo
  • 5,426
  • 9
  • 42
  • 61
  • hm, that's strange... It should have worked... Well, I don't have time to inspect ZF sources, sorry then... PS: as a workaround you could just do that manually: $table->fetchRow ( $table->select()->where('` key ` = ?','test') ); (added spaces around `key` because SO highlights it otherwise) – zerkms Jan 28 '12 at 09:48
  • I am doing exactly that workaround at the moment, but was looking for a "cleaner" solution :-) Thank you for trying! :-) – favo Jan 28 '12 at 09:51
  • If there will be a valid answer - please ping me with comment started with my name, I'm curious of "right" way of doing that ;-) – zerkms Jan 28 '12 at 09:53
  • I think problem is that `key` is the reserved word in MySQL. Try specifying it as $table->select()->where('\`key\` = ?','test') – zysoft Jan 28 '12 at 10:05
  • @zysoft: haha, captain to the rescue – zerkms Jan 28 '12 at 10:19
  • @RockyFord: yep, thanks. I knew it is something like `quoteIdentifier` but couldn't recall it – zerkms Jan 28 '12 at 10:30
  • @zerkms: $table->fetchRow ( $table->select()->where($table->getAdapter()->quoteIdentifier('key') . ' = ?','test') ); was it :-) – favo Jan 28 '12 at 10:39
  • @favo: yep, and this made the string twice longer ;-) I would personally go with quoting field manually in this particular case – zerkms Jan 28 '12 at 10:42
  • @zerkms: yes, looks nicer – if i should use dynamic columns in the future i now have at least a nice solution :-) – favo Jan 28 '12 at 10:44

3 Answers3

5

avoiding MySQL injections with the Zend_Db class

The guy explains it here actually but ill just pull out the quote quickly...

Any other part of that expression that needs to be quoted or delimited is your responsibility. E.g., if you interpolate any PHP variables into the expression, safety is your responsibility. If you have column names that are SQL keywords, you need to delimit them yourself with quoteIdentifier(). Example:

$select->where($db->quoteIdentifier('order').'=?', $myVariable)

Hope this helps!!

Community
  • 1
  • 1
Billy G
  • 66
  • 1
  • 2
1

One must quote column names when uppercase letters have been used. It is usefull to quote those names with $db->quoteIdentifier($columnName) when you plan to switch databese adapter in the future.

Bartek Kosa
  • 842
  • 1
  • 14
  • 25
1

try something like:

$table = new Application_Model_ATable();
$where = $table->getAdapter()->quoteInto('key = ?', 'test');
$table->fetchRow ( $where );

*--excerpt from Zend_Db_Table reference--*
Note The values and identifiers in the SQL expression are not quoted for you. If you have values or identifiers that require quoting, you are responsible for doing this. Use the quote(), quoteInto(), and quoteIdentifier() methods of the database adapter.

RockyFord
  • 8,529
  • 1
  • 15
  • 21
  • 2
    This does not escape the column name "key", it stays untouched but you provided the answer by quoting some of the documentation. The solution is: $table->fetchRow ( $table->select()->where($table->getAdapter()->quoteIdentifier('key') . ' = ?','test') ); – Thank you! – favo Jan 28 '12 at 10:37
  • quoteIdentifier() method is key to success here – Bartek Kosa Jul 25 '13 at 03:03