Opened 10 years ago
Closed 9 years ago
#31679 closed defect (bug) (fixed)
dbDelta generates wrong SQL on ALTER TABLE with columns containing hyphens in name
Reported by: |
|
Owned by: |
|
---|---|---|---|
Milestone: | 4.6 | Priority: | normal |
Severity: | normal | Version: | 4.1.1 |
Component: | Database | Keywords: | |
Focuses: | Cc: |
Description
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
@
10 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
#3
@
10 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};")
to
$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)];
to
$cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN `{$tablefield->Field}` " . $cfields[strtolower($tablefield->Field)];
would prevent from changing $cfields keys
Note: See
TracTickets for help on using
tickets.
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.)