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

[fulippo]

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.

[jdgrimes]

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.)

[fulippo]

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

[ocean90]

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

[ocean90]

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.