Column names or aliases should be wrapped in square brackets when using Connection::query()
, db_query()
, ConditionInterface::where()
or SelectInterface::addExpression
. This allows the database layer to correctly quote these identifiers.
For example:
$connection->query('SELECT [nid] FROM {node} WHERE [nid] = :id', [':id' => 1']);
This change allows us to support multiple database types without enforcing a list of words that cannot be used as table or field names.
Queries built using Connection::select()
, Connection::insert()
, Connection::delete()
, Connection::update()
, Connection::upsert()
, Connection::merge()
do not need to add any square brackets for calls to ->fields()
or ->condition()
. The new square bracket notation should be used when adding a raw SQL literal to the query object via ->addExpression
or ->where()
or the select object via ->having()
.
For example:
$connection->select('node')->where('[nid] = :id', [':id' => 1']);
// Note this is a bad example because using ->condition() would be better.
In extremely rare instances queries need square brackets. This can be used to create a function. In this case set the $option['allow_square_brackets']
to TRUE
.
For example:
$connection->query('CREATE OR REPLACE FUNCTION "substring_index"(text, text, integer) RETURNS text AS
\'SELECT array_to_string((string_to_array($1, $2)) [1:$3], $2);\'
LANGUAGE \'sql\'',
[],
['allow_delimiter_in_query' => TRUE, 'allow_square_brackets' => TRUE]
);
For database driver authors
Need to set the class variable \Drupal\Core\Database\Connection::$identifierQuotes
in their driver. This variable has to be an array of 2 strings. The first array being start identifier quote and the second being the ending identifier quote. As of Drupal 10.0 will this variable be required. In Drupal 9 when the variable is not set, it will get set to an array with 2 empty strings.
Review any existing overrides of \Drupal\Core\Database\Connection::escapeDatabase()
, \Drupal\Core\Database\Connection::escapeTable()
, \Drupal\Core\Database\Connection::escapeField()
, and \Drupal\Core\Database\Connection::escapeAlias()
. They might no longer be necessary.
If the driver is using \Drupal\Core\Database\Connection::$escapedNames
the code should be changed to use either \Drupal\Core\Database\Connection::$escapedTables
or \Drupal\Core\Database\Connection::$escapedFields
instead.