Changeset 39275

Timestamp:

11/17/2016 04:20:22 AM (10 years ago)

Author:

pento

Message:

Database: Add support for LIKE-escaped tables in ::get_table_from_query().

The SHOW TABLES LIKE query can be used to search for tables that match a pattern, wp\_123\_%, for example. While this isn't the name of an actual table, the wp_123_ prefix can be used by database drop-ins to direct the query correctly. This change removes the escaping and % modifier, to provide this usable prefix.

Props andy, pento.
Fixes #38751.

Location:

trunk

Files:

• src/wp-includes/wp-db.php (modified) (1 diff)
• tests/phpunit/tests/db.php (modified) (2 diffs)

trunk/src/wp-includes/wp-db.php

@@ -3038 +3038 @@
                 }
 
-                // SHOW TABLE STATUS and SHOW TABLES
-                if ( preg_match( '/^\s*(?:'
-                                . 'SHOW\s+TABLE\s+STATUS.+(?:LIKE\s+|WHERE\s+Name\s*=\s*)'
-                                . '|SHOW\s+(?:FULL\s+)?TABLES.+(?:LIKE\s+|WHERE\s+Name\s*=\s*)'
-                                . ')\W((?:[0-9a-zA-Z$_.`-]|[\xC2-\xDF][\x80-\xBF])+)\W/is', $query, $maybe ) ) {
-                        return str_replace( '`', '', $maybe[1] );
+                // SHOW TABLE STATUS and SHOW TABLES WHERE Name = 'wp_posts'
+                if ( preg_match( '/^\s*SHOW\s+(?:TABLE\s+STATUS|(?:FULL\s+)?TABLES).+WHERE\s+Name\s*=\s*("|\')((?:[0-9a-zA-Z$_.-]|[\xC2-\xDF][\x80-\xBF])+)\\1/is', $query, $maybe ) ) {
+                        return $maybe[2];
+                }
+
+                // SHOW TABLE STATUS LIKE and SHOW TABLES LIKE 'wp\_123\_%'
+                // This quoted LIKE operand seldom holds a full table name.
+                // It is usually a pattern for matching a prefix so we just
+                // strip the trailing % and unescape the _ to get 'wp_123_'
+                // which drop-ins can use for routing these SQL statements.
+                if ( preg_match( '/^\s*SHOW\s+(?:TABLE\s+STATUS|(?:FULL\s+)?TABLES)\s+(?:WHERE\s+Name\s+)?LIKE\s*("|\')((?:[\\\\0-9a-zA-Z$_.-]|[\xC2-\xDF][\x80-\xBF])+)%?\\1/is', $query, $maybe ) ) {
+                        return str_replace( '\\_', '_', $maybe[2] );
                 }
 

trunk/tests/phpunit/tests/db.php

@@ -566 +566 @@
                         "DELETE `a` FROM $table a",
 
-                        // STATUS
-                        "SHOW TABLE STATUS LIKE '$table'",
-                        "SHOW TABLE STATUS WHERE NAME='$table'",
-
-                        "SHOW TABLES LIKE '$table'",
-                        "SHOW FULL TABLES LIKE '$table'",
-                        "SHOW TABLES WHERE NAME='$table'",
-
                         // Extended
                         "EXPLAIN SELECT * FROM $table",
@@ -672 +664 @@
 
         /**
+         * @ticket 38751
+         */
+        function data_get_escaped_table_from_show_query() {
+                return array(
+                        // Equality
+                        array( "SHOW TABLE STATUS WHERE Name = 'test_name'", 'test_name' ),
+                        array( "SHOW TABLE STATUS WHERE NAME=\"test_name\"", 'test_name' ),
+                        array( "SHOW TABLES WHERE Name = \"test_name\"",     'test_name' ),
+                        array( "SHOW FULL TABLES WHERE Name='test_name'",    'test_name' ),
+
+                        // LIKE
+                        array( "SHOW TABLE STATUS LIKE 'test\_prefix\_%'",   'test_prefix_' ),
+                        array( "SHOW TABLE STATUS LIKE \"test\_prefix\_%\"", 'test_prefix_' ),
+                        array( "SHOW TABLES LIKE 'test\_prefix\_%'",         'test_prefix_' ),
+                        array( "SHOW FULL TABLES LIKE \"test\_prefix\_%\"",  'test_prefix_' ),
+                );
+        }
+
+        /**
+         * @dataProvider data_get_escaped_table_from_show_query
+         * @ticket 38751
+         */
+        function test_get_escaped_table_from_show_query( $query, $table ) {
+                $this->assertEquals( $table, self::$_wpdb->get_table_from_query( $query ) );
+        }
+
+        /**
          * @ticket 21212
          */