All identifiers should be 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. 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.