using a reserved word column name for unique key or primary key results in sql error

example

  $schema['test'] = array(
    'fields' => array(
      'key' => array(
        'type' => 'serial',
      ),
    ),
    'primary key' => array('key'),
  );

sql result

CREATE TABLE test ( `key` INT auto_increment DEFAULT NULL, PRIMARY KEY (key) )

so there should be an apostrophe around column names everywhere

CommentFileSizeAuthor
#5 column_quote_pkey.patch751 bytesPasqualle
#3 column_quote.patch739 bytesPasqualle
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented
Status: Active » Closed (won't fix)

In the current Core API, using a reserved word as a column name is *not* supported (anywhere). Don't do that.

Pasqualle’s picture

Pasqualle
Location 🇭🇺 Budapest
CreditAttribution: Pasqualle commented
Title: schema api: missing apostrophe » schema api: remove quotes from columns
Version: 6.x-dev » 7.x-dev
Priority: Normal » Minor
Status: Closed (won't fix) » Active

ok, if reserved word as column name is not supported then we do not need quotes on columns..

it is sloppy coding to have quotes on one place but do not have on other places..

Pasqualle’s picture

Pasqualle
Location 🇭🇺 Budapest
CreditAttribution: Pasqualle commented
Status: Active » Needs review
FileSize
column_quote.patch739 bytes
Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented
Title: schema api: remove quotes from columns » Schema API: properly quote column names
Priority: Minor » Normal
Status: Needs review » Needs work

Well. My remark was for 6.x.

In 7.x and beyond, columns using reserved words will be supported one day. The query builder that is being prepared in http://drupal.org/node/225450 will allow us to do that for general queries.

So you can use your energy in making sure that the schema API also properly escape column names, thus fixing the bug you spotted initially.

Pasqualle’s picture

Pasqualle
Location 🇭🇺 Budapest
CreditAttribution: Pasqualle commented
Title: Schema API: properly quote column names » schema api: missing apostrophe
Version: 7.x-dev » 6.x-dev
Status: Needs work » Needs review
FileSize
column_quote_pkey.patch751 bytes

this patch should quote columns listed as primary keys, unique keys or as indexes

Pasqualle’s picture

Pasqualle
Location 🇭🇺 Budapest
CreditAttribution: Pasqualle commented
Title: schema api: missing apostrophe » Schema API: properly quote column names
Version: 6.x-dev » 7.x-dev
Pasqualle’s picture

Pasqualle
Location 🇭🇺 Budapest
CreditAttribution: Pasqualle commented
Status: Needs review » Needs work

locale.install 

    'indexes' => array(
      'list' => array('weight', 'name'),
    ),

resulted in sql error

INDEX source (source(`30`))
Crell’s picture

Crell CreditAttribution: Crell commented
Version: 7.x-dev » 6.x-dev

I'm certain this no longer applies, but there's a new thread for dealing with this in D7: #315047: All schema API operations should be reserved-word safe.

I'm marking this down D6 rather than duplicate in case Gabor wants to do something with it, but for D7 let's use the other thread.

dpearcefl’s picture

dpearcefl CreditAttribution: dpearcefl commented

Is this still an issue using current Drupal 6?

Murz’s picture

Murz
Primary language English
Location Yerevan, Armenia
CreditAttribution: Murz commented

This bug is still here on Drupal 6.22 (see bug #304421: drupal_install_schema() function must always enclose field names).
I test this bug now in Drupal 7.2 and can't reproduce this bug, all works normally. So seems it fixed in 7.x, maybe you can backport db creating process from 7.x to 6.x?

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented

The database layer in Drupal 7 is vastly different then Drupal 6, so the solution has to be different. #5 has a patch that needs review, and should probably be extended to cover PostgreSQL and the other schema operations (alter table, add index, etc.).

danblack’s picture

danblack CreditAttribution: danblack commented
Issue summary: View changes

Status: Needs work » Closed (outdated)

Automatically closed because Drupal 6 is no longer supported. If the issue verifiably applies to later versions, please reopen with details and update the version.