Make WordPress Core

Opened 6 years ago

Closed 5 years ago

#31679 closed defect (bug) (fixed)

dbDelta generates wrong SQL on ALTER TABLE with columns containing hyphens in name

Reported by: fulippo Owned by: ocean90
Milestone: 4.6 Priority: normal
Severity: normal Version: 4.1.1
Component: Database Keywords:
Focuses: Cc:


Upgrading a table using dbDelta a wrong SQL statement is generated when existing table's names contain hyphens.

Example of wrong SQL statement generated by dbDelta:

ALTER TABLE wp_stores CHANGE COLUMN _store-lng `_store-lng` text NULL

This is because the result for the query

$tablefields = $wpdb->get_results("DESCRIBE {$table};");

contains column names without backticks causing the SQL query to fail.

Since there is no way to filter column names it is impossible to upgrade tables.

Change History (5)

#1 @fulippo
6 years ago

  • Summary changed from dbDelta generates wrong SQL on ALTER TABLE with fields containing hyphens in name to dbDelta generates wrong SQL on ALTER TABLE with columns containing hyphens in name

#2 @jdgrimes
6 years ago

Related: #30655, #20263

dbDelta() just doesn't support backticks. I think the function could really use a complete rewrite. (I'd even be willing to undertake it.)

#3 @fulippo
6 years ago

As regards the problem reported above, I think that just changing the code around line 1694 from

$tablefields = $wpdb->get_results("DESCRIBE {$table};")


$tablefields = array_map(function($row){
    if( strpos( $row->Field, '`' ) === false ) {
        $row->Field = '`'.$row->Field.'`';
    return $row;
}, (array)$wpdb->get_results("DESCRIBE {$table};"));

Could solve the problem. Or, even better, changing line 1764 from

$cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN {$tablefield->Field} " . $cfields[strtolower($tablefield->Field)];


$cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN `{$tablefield->Field}` " . $cfields[strtolower($tablefield->Field)];

would prevent from changing $cfields keys

Version 1, edited 6 years ago by fulippo (previous) (next) (diff)

#4 @ocean90
5 years ago

  • Component changed from Plugins to Database
  • Milestone changed from Awaiting Review to 4.6

This was fixed in [37583]. Going to commit a unit test for this case.

#5 @ocean90
5 years ago

  • Owner set to ocean90
  • Resolution set to fixed
  • Status changed from new to closed

In 38044:

Database: Add unit test to test that a column type change for a table name with a hyphen is working after [37583].

Fixes #31679.

Note: See TracTickets for help on using tickets.