Database reserved keywords need to be quoted as per the ANSI standard

Problem/Motivation

Over many years Drupal has held off checking queries for reserved words in column names, table names and aliases because it was a design decision. The idea is that we just just should not use reserved words. However, time has made it clear that this position (as stated in the long discussions in #371: resolve ANSI SQL-92/99/2003 reserved words conflict in query statements → ) is not really tenable. Here are the problems:

• The list of reserved words is different for different database drivers even those the support the various ANSI SQL standards. For example OFFSET is reserved in PostgreSQL but not in MySQL
• Database drivers add new reserved keywords in new versions. MySQL has been doing this in every new version for example 5.7 and 8

Proposed resolution

• Add a new syntax square bracket syntax to the query builder to know what identifiers (column names/ aliases) are. Leverage work in #371: resolve ANSI SQL-92/99/2003 reserved words conflict in query statements → suggested using [COLUMN_NAME] to match {TABLE_NAME}. This supports db_query(), addExpression() and addWhere()
• Automatically quote fields and aliases in escapeField() and escapeAlias().
• All identifiers should be quoted. This helps us ensure that the database API is properly used when query building and will make edge-cases less likely.

Using [] to identify identifiers (field names / aliases)

There is precedence - this is the quote character in MSSQL.

The one consideration is that :variable[] is used to identify and expand array variables.

Discarded resolution(s)

We could deprecate SELECT, UPDATE, INSERT queries via \Drupal\Core\Database\Connection::query() since the methods like \Drupal\Core\Database\Connection::select() require fields to be specified. If we do that we have to worry about performance - see #1067802: Compare $connection->query() and $connection->select() performance → . Also this does not work for columns added using ->addExpression() or ->addWhere(). Therefore this resolution has been discarded.

Remaining tasks

• #2986334: Add a way to enforce an empty result set for a database condition query →
• #2986539: \Drupal\user\Plugin\EntityReferenceSelection\UserSelection::entityQueryAlter() should escape the fake condition column on replacement →

File follow-ups:

• add coder rule to try to check for missing []
• Ensure core uses new square bracket syntax

User interface changes

None

API changes

At the moment \Drupal\Core\Database\Connection::escapeField() and \Drupal\Core\Database\Connection::escapeAlias() in their core implementations are re-entrant. And some core code relies on this. That said the docs hint that this should not be case. In the new implementation methods also now quote identifiers BUT because the code removes the quotes before adding them they still are re-entrant. The new implementations result in strings like users_data.uid becoming "users_data"."uid".

\Drupal\Core\Database\Query\ConditionInterface::condition()'s first argument $field must be a field and cannot be (ab)used to do ->condition('1 <> 1'). The way to do this is to use the new API added by #2986334: Add a way to enforce an empty result set for a database condition query →

\Drupal\Core\Database\Connection::prepareQuery() now takes additionally takes a boolean that is derived from a query's $options['allow_square_brackets'] value that allows specific queries to use square brackets and to not them for identifier quotes. The default value for $options['allow_square_brackets'] is FALSE.

\Drupal\Core\Database\Connection::prefixTables now quotes tables so {node} becomes "node".

Data model changes

None

Release notes snippet

All identifiers (table names, database names, field names and aliases) are quoted in database queries. Column names or aliases should be wrapped in square brackets when using Connection::query(), db_query(), ConditionInterface::where() or SelectInterface::addExpression. Custom or contrib database drivers should read https://www.drupal.org/node/2986894 → to see what changes are necessary.