MODX :: xPDO queries and MySQL reserved words

15,112

How MODx Evo Works

Log in to an Evo Manager username guest, password guestuser.

sottwell Reply #1, 4 months, 1 week ago

Link to this post#1

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

9,770

- MODX Chief Architect

Jason Coward | MODX Chief Architect

jasoncoward.com | @drumshaman

opengeek Reply #2, 4 months, 1 week ago

Link to this post#2

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.

15,112

How MODx Evo Works

Log in to an Evo Manager username guest, password guestuser.

sottwell Reply #3, 4 months, 1 week ago

Link to this post#3

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?

9,770

- MODX Chief Architect

Jason Coward | MODX Chief Architect

jasoncoward.com | @drumshaman

opengeek Reply #4, 4 months, 1 week ago

Link to this post#4

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

xPDO queries and MySQL reserved words#

sottwell Reply #1, 4 months, 1 week ago

opengeek Reply #2, 4 months, 1 week ago

sottwell Reply #3, 4 months, 1 week ago

opengeek Reply #4, 4 months, 1 week ago