WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

#36422 closed defect (bug) (fixed)

Specific table prefixes causes sql errors

Reported by: voldemortensen Owned by: pento
Milestone: 4.6 Priority: normal
Severity: normal Version:
Component: Database Keywords: has-patch
Focuses: Cc:

Description

$table_prefix = '7e1_';

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7e1_users ( ID bigint(20) unsigned NOT NULL auto_increment, user_login varch' at line 1]

It seems that mysql is trying to parse 7e1_users in engineering notation (numeric value), instead of as an allowed table name. This requires the table name to always be quoted to force correct parsing. Unsure what the path forward is, i.e. detect and display a better error message, or auto quote the table name.

Attachments (1)

36422.diff (799 bytes) - added by pento 4 years ago.

Download all attachments as: .zip

Change History (6)

#1 @dd32
4 years ago

The correct thing to do would be to always quote the table names, but realistically that's not an option here (plugins, etc).

Maybe we can preflight the prefix name during the DB credentials checks somehow? just check that the DB interprets an unquoted prefix the same as it would a quoted one?
I assume we'd just say it's an invalid prefix if it failed, I'd err on the side of not trying to explain why it's invalid though.

@pento
4 years ago

#2 @pento
4 years ago

  • Keywords has-patch added
  • Milestone changed from Awaiting Review to 4.6
  • Owner set to pento
  • Status changed from new to assigned

Taking suggestions for a better error message before I commit this. :-)

#3 @rmccue
4 years ago

Can we do it earlier without MySQL as well? is_numeric( $prefix ) ?

Re: message, I wouldn't quote table prefix, just ERROR: Table prefix is invalid.

#4 @pento
4 years ago

Nope, is_numeric() doesn't match the same things.

"Table Prefix" is quoted to match the style of similar error messages earlier in the file. I agree that it feels less awkward without the quotes, but I'd prefer to maintain a consistent style.

#5 @pento
4 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

In 37581:

Setup: Sanity check for invalid table prefixes.

There are some table prefixes (for example, 7e1_), which MySQL will try and parse as values when they're note quoted in backticks. Because not everything remembers to quote their table names, it's best if we just discourage their use during setup.

Fixes #36422.

Note: See TracTickets for help on using tickets.