xPDO queries and MySQL reserved words

☆ A M B ☆
24,524 Posts

sottwell Reply #1, 14 years, 8 months ago

I had occasion to use "key" as a field name. Because "key" is a MySQL reserved word, it caused a MySQL error when using this query

$sort = $modx->getOption('sort',$scriptProperties,'key');
$dir = $modx->getOption('dir',$scriptProperties,'ASC');

$c = $modx->newQuery('Coupon');
$c->sortby($sort,$dir);
$coupons = $modx->getCollection('Coupon',$c);

To solve the problem, since I really did want the field name to be "key", I changed the sort specification line to quote the field name:

$sort = $modx->getOption('sort',$scriptProperties,'`key`');

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

MODX Staff
10,725 Posts

opengeek Reply #2, 14 years, 8 months ago

Actually, xPDO provides a db agnostic way to solve this:

$sort = $modx->getOption('sort',$scriptProperties,$modx->escape('key'));

This will now support sqlsrv (and potentially sqlite, pgsql, etc.).

The reason xPDOQuery->sort() does not automatically apply escaping is because it also accepts SQL functions and other syntax that would not necessarily be escaped itself.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

☆ A M B ☆
24,524 Posts

sottwell Reply #3, 14 years, 8 months ago

I don't understand how escaping will solve the problem of using a reserved keyword as the field name, unless it automatically adds the backtick to it?

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

MODX Staff
10,725 Posts

opengeek Reply #4, 14 years, 8 months ago

That is what the escape character for MySQL is, the backtick. In sqlite, it is a single-quote. In sqlsrv, it's brackets.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek