From case #314126: i18nstrings failure on PostgreSQL I learned that PostgreSQL have some system columns that are blocked. See http://www.postgresql.org/docs/8.3/static/ddl-system-columns.html for more information. I think we should check for oid, tableoid, xmin, cmin, xmax, cmax and ctid and throw an error if used in CREATE TABLE statements. I'm not sure if it's allowed to use this columns in select/update/insert.

Such a check seems missing as it wasn't reported when I used coder on the i18n modules.

Comments

stella’s picture

stella CreditAttribution: stella commented

Well in Drupal 6, there was the introduction of the Schema API which means no module should be using a CREATE TABLE sql queries. So I'm unsure if coder of the schema api should be checking for usage of the reserved column names.

hass’s picture

hass CreditAttribution: hass commented
Title: PostgreSQL system columns check on CREATE TABLE » PostgreSQL system columns validation

Ok, new topic. Schema should be validated for invalid and/or reserved column names.

I'm not sure if mysql also have such reserved columns... if so, also check for them.

hass’s picture

hass CreditAttribution: hass commented

Any news?

klausi’s picture

klausi
he/him||they/them
Primary language German
Location 🇦🇹 Vienna
CreditAttribution: klausi commented
Issue summary: View changes
Status: Active » Closed (won't fix)

Coder for Drupal 6 is now frozen and only security fixes will be applied. Feel free to update this issue and reopen against 7.x-2.x or 8.x-2.x.

hass’s picture

hass CreditAttribution: hass commented
Version: 6.x-1.x-dev » 7.x-2.x-dev
Status: Closed (won't fix) » Active
pfrenssen’s picture

pfrenssen
Location Sofia
CreditAttribution: pfrenssen as a volunteer commented
Status: Active » Closed (works as designed)

This seems to be very outdated, using raw queries is strongly discouraged since the database abstraction layer was introduced in Drupal 7. In any case it is not within the scope of Coder Sniffer to check for invalid column names when creating database tables for specific engines. We are solely concerned with coding standards. It would be better to address these in the postgres component of the database layer.

hass’s picture

hass CreditAttribution: hass commented
Status: Closed (works as designed) » Active

This is not a problem of raw querys only. Many if not most developers are not aware of reserved column names. "current" is also such a reserved name in mssql. It was used by fault on workbench module.

It can easily happen that reserved names go into core without getting noticed. Coder does complain about invalid code style, but also about well known other code type issues as I know.

We should really add this. Nodody can know everything of every type of database he may not use. Such issuescan cause serious bugs and we should prevent this. I think there is no better place than coder to do this checks.

pfrenssen’s picture

pfrenssen
Location Sofia
CreditAttribution: pfrenssen as a volunteer commented

I'm not a Postgres expert but if this is so important doesn't it make more sense to you to add checks for these reserved column names to \Drupal\Core\Database\Driver\pgsql\Schema::createTable()?

If an exception would be thrown there then it will simply be impossible for anyone to ever create a table with reserved column names, that's a lot more reliable than hoping that someone will do a coding standards check with the CoderSniffer ruleset before deploying to production.

It's also a lot easier to implement it in the database layer than in Coder. We just do static source code analysis, the code is not actually being executed. Because the database layer is abstracted it is impossible for us to determine which database engine is being used when we encounter calls to db_create_table() or SchemaInterface::createTable().

hass’s picture

hass CreditAttribution: hass commented
Title: PostgreSQL system columns validation » PostgreSQL/MsSQL system columns validation

Well, maybe a good idea. Than this needs to go into Core?

hass’s picture

hass CreditAttribution: hass commented
Title: PostgreSQL/MsSQL system columns validation » Database schema validation / reserved name validation
Project: Coder » Drupal core
Version: 7.x-2.x-dev » 8.1.x-dev
Component: Code » database system

This should go into https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Extension... and we can block the module enable process if the schema contains disallowed system columns.

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.0-beta1 was released on March 2, 2016, which means new developments and disruptive changes should now be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.0-beta1 was released on August 3, 2016, which means new developments and disruptive changes should now be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.0-alpha1 will be released the week of January 30, 2017, which means new developments and disruptive changes should now be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.0-alpha1 will be released the week of July 31, 2017, which means new developments and disruptive changes should now be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.0-alpha1 will be released the week of January 17, 2018, which means new developments and disruptive changes should now be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.7.x-dev

Drupal 8.6.0-alpha1 will be released the week of July 16, 2018, which means new developments and disruptive changes should now be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.