[wp-trac] [WordPress Trac] #36924: dbDelta(): Support more than one whitespace between field name and its type definition

[WordPress Trac]

#36924: dbDelta(): Support more than one whitespace between field name and its type
definition
--------------------------+-----------------------------
 Reporter:  matt_fw       |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  4.5.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 dbDelta() fails to remove multiple spaces between field name and field
 type definition in ALTER / CREATE statements. In result some table
 definitions may lead to constant ALTER statements to be executed which may
 easily crash MySQL server.

 Compare:
 == Correct
 {{{#!php
 $sql = "CREATE TABLE some_table (
     id bigint(20) NOT NULL KEY AUTO_INCREMENT,
     test varchar(100) NOT NULL,
     );";
 require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
 dbDelta($sql);
 }}}

 vs

 == Wrong, due to multiple spaces between 'test' and 'varchar' ALTER query
 is executed
 {{{#!php
 $sql = "CREATE TABLE some_table (
     id bigint(20) NOT NULL KEY AUTO_INCREMENT,
     test  varchar(100) NOT NULL,
     );";
 require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
 dbDelta($sql);
 }}}

 == Possible fix:
 - in dbDelta() call trim() on $tablefield->Type,
 - or remove extra spaces during preg_match:
 replace:
 {{{#!php
 preg_match("|".$tablefield->Field." ([^ ]*( unsigned)?)|i",
 $cfields[strtolower($tablefield->Field)], $matches);
 }}}
 with:
 {{{#!php
 preg_match("|".$tablefield->Field."\s+([^ ]*( unsigned)?)|i",
 $cfields[strtolower($tablefield->Field)], $matches);
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/36924>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform