Make WordPress Core

Opened 8 years ago

Last modified 6 years ago

#36924 new defect (bug)

dbDelta(): Support more than one whitespace between field name and its type definition

Reported by: matt_fw's profile matt_fw Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.5.2
Component: Database Keywords:
Focuses: Cc:

Description

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

$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

$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:

preg_match("|".$tablefield->Field." ([^ ]*( unsigned)?)|i", $cfields[strtolower($tablefield->Field)], $matches);

with:

preg_match("|".$tablefield->Field."\s+([^ ]*( unsigned)?)|i", $cfields[strtolower($tablefield->Field)], $matches);

Change History (3)

#1 @swissspidy
8 years ago

See also #10404.

#2 @dd32
8 years ago

In result some table definitions may lead to constant ALTER statements to be executed which may easily crash MySQL server.

As a quick driveby note, dbDelta() shouldn't be called often, only when a plugin or core detects that one of it's tables potentially needs to be updated. Core handles this through it's db_version variable.

#3 @netweb
6 years ago

Related: #44767 dbDelta allow spaces between column names and between lines

Note: See TracTickets for help on using tickets.