Use square brackets syntax in sql queries

Created on 25 December 2020, almost 4 years ago
Updated 11 December 2024, 8 days ago

Problem/Motivation

It would be useful to use the square brackets syntax to mark identifiers also in expressions. It's not a significant change for core since the Database API already escapes the identifiers with the relevant quote characters. For Oracle, viceversa, it's pretty fundamental since the DBMS uppercases all the identifiers if they are not quoted, and that makes it impossible to use unquoted lowercase identifiers in expressions when you have created db assets with quoted lowercase field names.

Proposed resolution

All field names and all alias names must be encapsulated with square brackets.

Remaining tasks

TBD

User interface changes

None

API changes

None

Data model changes

None

Release notes snippet

Commit credits

📌 Task
Status

Fixed

Version

9.2

Component

database system

Created by

🇮🇹Italy mondrake 🇮🇹

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • 🇺🇸United States mikelutz Michigan, USA

    I banged my head on this for an hour this morning, just to figure out it was Drupal killing my brackets and not a problem with my regex...

    The answer is to add the 'allow_square_brackets' option to the query

    `\Drupal::database()->select('table', 't', ['allow_square_brackets' => TRUE]);`

    Unfortunately, my regex was `'[0-9]{2,}'`, and I don't see a way to preserve squiggly brackets as those are used as table placeholders by the query builder. I'll get around it with `'[0-9][0-9]+` for today, but it's good to realize I can't use squiggly brackets in regex.

Production build 0.71.5 2024