[wp-trac] [WordPress Trac] #32090: Bug: Can not update tables in non-wordpress-installation databases when the query contains non ASCII characters

[WordPress Trac]

#32090: Bug: Can not update tables in non-wordpress-installation databases when the
query contains non ASCII characters
--------------------------+-----------------------------
 Reporter:  willstedt     |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  4.1.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 When writing a query that connects to a database other than the database
 used to install Wordpress, and tries to store a string value which is non-
 ascii, the update is never performed.

 This bug was implemented in the 4.1.2 security update and exists also in
 the 4.2 update.

 Here is an example query that fails to execute:
 UPDATE mydatabase.wp_customers SET firstname='Örjan', lastname = 'Åkesson'
 WHERE friendid = '55'

 The reason for the bug is that the table name is used to get the collation
 from the table when non-ascii characters are being stored in the table.
 The regular expression used to get the table name in the function
 get_table_from_query doesn't allow a dot (.) in the table name and
 therefore returns only the database name without the trailing table name.
 When the function is called for, the example above would return the
 database name (mydatabase) instead of the full table name with database
 (mydatabase.wp_customers).

 The solution to this part is to allow a dot in the table name, as written
 below.

 wp-db.php (2693):

 {{{
 if ( preg_match( '/^\s*(?:'
         . 'SELECT.*?\s+FROM'
         .
 '|INSERT(?:\s+LOW_PRIORITY|\s+DELAYED|\s+HIGH_PRIORITY)?(?:\s+IGNORE)?(?:\s+INTO)?'
         . '|REPLACE(?:\s+LOW_PRIORITY|\s+DELAYED)?(?:\s+INTO)?'
         . '|UPDATE(?:\s+LOW_PRIORITY)?(?:\s+IGNORE)?'
         . '|DELETE(?:\s+LOW_PRIORITY|\s+QUICK|\s+IGNORE)*(?:\s+FROM)?'
         . ')\s+`?([\w-.]+)`?/is', $query, $maybe ) ) {
                 return $maybe[1];
         }
 }}}

 When the database name is returned instead of the table name, the function
 fails to execute the SQL: SHOW FULL COLUMNS FROM ´mydatabase´ in the
 get_table_charset function (as you can't get columns from a database). The
 function then returns wpdb_get_table_charset_failure and the update is
 never performed.

 When allowing a dot in the regex, the query "SHOW FULL COLUMNS FROM
 ´$table´" has to be updated, as the apostrophes shouldn't be used when a
 database name is given. Therefore the function get_table_charset also has
 to be updated with something like this:

 wp-db.php (2236):
 {{{
                 if (strpos($table,'.') === false) {
                     $table = "`$table`";
                 }
                 $results = $this->get_results( "SHOW FULL COLUMNS FROM
 $table" );
 }}}


 For all systems that allows multilingual stuff sorted in different
 databases, this bug really destroys the possibility to use Wordpress
 altogether as there is no workaround except changing the bug directly in
 the core. I have done the above fix as a temporary solution so that our
 employees can work, but the system will of course break every time
 Wordpress gets updated - until the bug is fixed.

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