Mitigate inability of escaping column names in Drupal dynamic queries

Working in a Drupal agency you do a lot of drupal development and come against various problems. Recently, on a migration to Drupal I encountered a new problem with the Drupal database abstraction layer. It turns out that drupal dynamic select queries do not escape column names.

This problem does not touch each database type, but it definitely can be problematic when it comes to mysql

$query = Database::getConnection('default', 'legacy') ->select('prod_group', 'pg') ->fields('pg', array('porductid, 'active', 'order'));

will create the following query:

SELECT pg.productid AS productid, pg.active AS active, pg.order AS order FROM {prod_group} pg;

We are missing the `` around fields and in this scenario we had a very unfortunate column name "order" which is a reserved keyword in mysql used to sort  results (ORDER BY). This is how mysql interprets the query, which is therefore incorrectly structured and throws an error.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

We should be creating a query with table names escaped with backticks:

SELECT pg.productid AS `productid`, pg.active AS `active`, pg.order AS `order` FROM {prod_group} pg;

but Drupal does not support this in dynamic queries, which are the only ones supported by migrate module.

Solution - field aliases

Drupal supports aliasing. You can even see this above "pg.order AS order" is a query which aliases the order field automatically, unfortunately with it's own restricted name.

But we can set these aliases automatically with SelectQueryInterface::addField.

$query = Database::getConnection('default', 'legacy') ->select('prod_group', 'pg') ->fields('pg', array('groupid', 'active')) ->addField('pg', 'order', 'escaped_order');

Which gives us a query without a restricted keyword:

SELECT pg.productid AS productid, pg.active AS active, pg.order AS escaped_order FROM {prod_group} pg;.

This solves the problem. Just remember that when you fetch your results, the order will be called escaped_order.