Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.Database reserved words conflict is always happened. It is also happened within Drupal's core, and opened as a pending issue for more than 5 years (http://drupal.org/node/371).
We can resolve this problem by: 1. avoid the use of reserved word among our project, or 2. automatically detect conflict wording within each database driver implementation. The PROS/CONS are both detailed in here. They are both temporary solution which shouldn't be our consideration.
This patch propose a [] syntax for queries, which apply the similar idea as existing {} syntax. Identifiers and names which wrapped by square brackets among queries will be replaced by database-specific escape characters, e.g. ` (MySQL), " (PgSQL/Oracle/DB2) or remain unchange (MSSQL). This proposal is just a clone to phpmyadmin, phppgadmin, Oracle/DB2/MSSQL Enterprise Manager internal query generator: identifiers and names generated by these tools will always wrapped by its required escape characters, no matter it is/isn't a reserved word. This propose is proved as meaningful and functioning based on these research: http://drupal.org/node/371, http://drupal.org/node/172541 and http://edin.no-ip.com/html/?q=node/310.
This is a simplified and backport patch for D6 based on existing founding (http://drupal.org/node/172541). As an example, it comes with a modified version of session.inc, which rewrite all queries with above syntax. We DON'T need to rewrite ALL existing queries within current status: all of our existing queries are functioning with no error message. We can change into this syntax phase by phase and step by step. As a long run, we should consider about applying this [] syntax to all Drupal's queries, as a permanent solution of ANSI SQL-92/99/2003 reserved words conflict.
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | drupal-6.x-dev-db_replace_escape-0.2.patch | 10.47 KB | hswong3i |
| drupal-6.x-dev-db_replace_escape-0.1.patch | 9.97 KB | hswong3i | |
Comments
Comment #1
hswong3i CreditAttribution: hswong3i commentedThis patch is not a hurry issue that must exist within D6, I will come back later if it is not suitable within current status (as it is only a simple backport patch of my D7 research progress).
BTW, sliding this into D6 may give more time for normal contribute developers to prepare themselves for this essential change within our coming on release, as it is a huge issue that not easy to complete within a short period (we need to review ALL SQL and quote them as we can, which is a crazily heavy workload that I faced during research...), and it is the only ideal permanent solution that I am able to explore for (even for the case of MySQL), based on a wide range and indeed research about multiple-cross database compatibility issue. Hope may give some kindly love to this late but important proposal m(_ _)m
Comment #2
hswong3i CreditAttribution: hswong3i commentedPatch update via latest CVS HEAD, tested for both MySQL 5.0.32 and PostgreSQL 8.1.9 on Debian etch 4.0r1 with PHP 5.2.0-8+etch7, passed for core installation + user login/logout. As this should be a long term combat that can't be solve within a short period (even within the whole D7 dev life cycle, I guess...), commit this optional patch earlier may give more time for us (we can encourage developers to follow this syntax though D6, and force them to use when D7 come). This patch is really useful and meaningful for MySQL, where not only for all other databases. Hope may give some love to this patch on time :)
P.S. This patch also show the needs of my other patch: http://drupal.org/node/183910. If we are able to collect all queries pre-process handling inside db_query(), code may not need to be duplicated among 3 functions and so simpler for daily maintenance.
Comment #3
moshe weitzman CreditAttribution: moshe weitzman commentedComment #4
hswong3i CreditAttribution: hswong3i commentedThis may really sounds funny and mad if we hope to escape all reserved word by using
[]syntax, as we need to revamp ALL core queries into new standard.BTW, when studying moodle's reserved words related topic (http://docs.moodle.org/en/XMLDB_reserved_words), you will find a huge list or words that need to be careful with. It is too crazy if we need to ask EVERY contribute developer to understand this limitation as indeed as a professional cross database developer. But simply apply
[]syntax can let EVERYONE forget this issue forever :)Other useful links:
# One Universal Checker: http://www.petefreitag.com/tools/sql_reserved_words_checker/
# MySQL: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
# PostgreSQL: http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
# SQL*Server: http://msdn2.microsoft.com/en-us/library/ms189822.aspx
# Oracle: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_key...
# Good site with all the real reserved words for some DB (with version info): http://search.cpan.org/src/CHANSEN/ (under the SQL-ReservedWords-xx dirs).
# Escaping reserved words: http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-034.html
Comment #5
hswong3i CreditAttribution: hswong3i commentedAs both target for D7, this issue is duplicated with http://drupal.org/node/371.