Column name "next" is a reserved keyword [#2585073]

Problem/Motivation

In some SQL implementations (NuoDB in our case) "next" is a keyword, which causes errors when executing the db_select in job_scheduler_cron().

Proposed resolution

Change driver to escape keywords.

Remaining tasks

Escape potential keywords

User interface changes

API changes

Data model changes

Original report by xenophyle

In some SQL implementations (NuoDB in our case) "next" is a keyword, which causes errors when executing the db_select in job_scheduler_cron().

PDOException: SQLSTATE[42000]: Syntax error or access violation: -1 syntax error on line 4 WHERE (scheduled = ?) AND (next <= ?) ^ expected VALUE got < : SELECT job_schedule.* FROM {job_schedule} job_schedule WHERE (scheduled = :db_condition_placeholder_0) AND (next <= :db_condition_placeholder_1) ORDER BY next ASC OFFSET 0 FETCH 200; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => 1444398681 ) in job_scheduler_cron() (line 65 of sites/all/modules/contrib/job_scheduler/job_scheduler.module)

Comments

Comment #1

9 October 2015 at 15:05

xenophyle created an issue. See original summary.

Comment #2

intrafusion

English

Edinburgh, UK

CreditAttribution: intrafusion commented 10 February 2018 at 10:11

Status:

Active

» Postponed (maintainer needs more info)

Please follow the Issue Summary Template standards

Comment #3

mmjvb CreditAttribution: mmjvb as a volunteer commented 10 February 2018 at 11:54

Issue summary:	View changes
Status:	Postponed (maintainer needs more info)	» Active

Would expect the driver to escape keywords. Or do it yourself in the query. Even for NuoDB keywords can be escaped by backticks.

Comment #4

intrafusion

English

Edinburgh, UK

CreditAttribution: intrafusion commented 11 February 2018 at 10:06

Status:

Active

» Needs work

I think the correct way to deal with this would be to rename the fields using reserved keywords as the module already uses dB_select, etc. and you cannot escape keywords in these functions.

Comment #5

mmjvb CreditAttribution: mmjvb as a volunteer commented 11 February 2018 at 13:57

https://cgit.drupalcode.org/d7_nuodb_driver/tree/nuodb/database.inc
Checked the driver itself and found:

  /**
   * Escapes a field name string.
   *
   * Force all field names to be strictly alphanumeric-plus-underscore
   * and wrap the field name in backquote '`' characters.
   *
   * @param string $field
   *   The field name to escape.
   *
   * @return string
   *   The sanitized field name string.
   */
  public function escapeField($field) {
    return preg_replace('/[^A-Za-z0-9_.]+/', '', '`' . $field . '`');
  }

Looks like it has already been taken care of.