Changeset 32162

Timestamp:

04/20/2015 04:45:12 AM (9 years ago)

Author:

pento

Message:

WPDB: When sanity checking read queries, there are some collations we can skip, for improved performance.

Props pento, nacin.

See #21212.

Location:

trunk

Files:

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

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

@@ -170 +170 @@
      */
     protected $check_current_query = true;
+
+    /**
+     * Flag to ensure we don't run into recursion problems when checking the collation.
+     *
+     * @since 4.2.0
+     * @access private
+     * @see wpdb::check_safe_collation()
+     * @var boolean
+     */
+    private $checking_collation = false;
 
     /**
@@ -2039 +2049 @@
         $this->func_call = "\$db->get_var(\"$query\", $x, $y)";
 
+        if ( $this->check_safe_collation( $query ) ) {
+            $this->check_current_query = false;
+        }
+
         if ( $query ) {
             $this->query( $query );
@@ -2067 +2081 @@
     public function get_row( $query = null, $output = OBJECT, $y = 0 ) {
         $this->func_call = "\$db->get_row(\"$query\",$output,$y)";
+
+        if ( $this->check_safe_collation( $query ) ) {
+            $this->check_current_query = false;
+        }
+
         if ( $query ) {
             $this->query( $query );
@@ -2104 +2123 @@
      */
     public function get_col( $query = null , $x = 0 ) {
+        if ( $this->check_safe_collation( $query ) ) {
+            $this->check_current_query = false;
+        }
+
         if ( $query ) {
             $this->query( $query );
@@ -2131 +2154 @@
     public function get_results( $query = null, $output = OBJECT ) {
         $this->func_call = "\$db->get_results(\"$query\", $output)";
+
+        if ( $this->check_safe_collation( $query ) ) {
+            $this->check_current_query = false;
+        }
 
         if ( $query ) {
@@ -2360 +2387 @@
 
     /**
+     * Check if the query is accessing a collation considered safe on the current version of MySQL.
+     *
+     * @since 4.2.0
+     * @access protected
+     *
+     * @param string $query The query to check.
+     * @return bool True if the collation is safe, false if it isn't.
+     */
+    protected function check_safe_collation( $query ) {
+        if ( $this->checking_collation ) {
+            return true;
+        }
+
+        $table = $this->get_table_from_query( $query );
+        if ( ! $table ) {
+            return false;
+        }
+
+        $this->checking_collation = true;
+        $this->get_table_charset( $table );
+        $this->checking_collation = false;
+
+        $table = strtolower( $table );
+        if ( empty( $this->col_meta[ $table ] ) ) {
+            return false;
+        }
+
+        foreach( $this->col_meta[ $table ] as $col ) {
+            if ( empty( $col->Collation ) ) {
+                continue;
+            }
+
+            if ( ! in_array( $col->Collation, array( 'utf8_general_ci', 'utf8_bin', 'utf8mb4_general_ci', 'utf8mb4_bin' ), true ) ) {
+                return false;
+            }
+        }
+
+        return true;
+    }
+
+    /**
      * Strips any invalid characters based on value/charset pairs.
      *

trunk/tests/phpunit/tests/db/charset.php

@@ -464 +464 @@
         $this->assertFalse( $wpdb->query( "INSERT INTO {$wpdb->posts} (post_content) VALUES ('foo\xf0\xff\xff\xffbar')" ) );
     }
+
+    /**
+     * @ticket 21212
+     */
+    function data_table_collation_check() {
+        $table_name = 'table_collation_check';
+        $data = array(
+            array(
+                // utf8_bin tables don't need extra sanity checking.
+                "( a VARCHAR(50) COLLATE utf8_bin )", // create
+                true                                  // expected result
+            ),
+            array(
+                // Neither do utf8_general_ci tables.
+                "( a VARCHAR(50) COLLATE utf8_general_ci )",
+                true
+            ),
+            array(
+                // utf8_unicode_ci tables do.
+                "( a VARCHAR(50) COLLATE utf8_unicode_ci )",
+                false
+            ),
+            array(
+                // utf8_bin tables don't need extra sanity checking,
+                // except for when they're not just utf8_bin.
+                "( a VARCHAR(50) COLLATE utf8_bin, b VARCHAR(50) COLLATE big5_chinese_ci )",
+                false
+            ),
+            array(
+                // utf8_bin tables don't need extra sanity checking
+                // when the other columns aren't strings.
+                "( a VARCHAR(50) COLLATE utf8_bin, b INT )",
+                true
+            ),
+        );
+
+        foreach( $data as &$value ) {
+            $this_table_name = $table_name . '_' . rand_str( 5 );
+
+            $value[0] = "CREATE TABLE $this_table_name {$value[0]}";
+            $value[2] = "SELECT * FROM $this_table_name";
+            $value[3] = "DROP TABLE IF EXISTS $this_table_name";
+        }
+        unset( $value );
+
+        return $data;
+    }
+
+
+    /**
+     * @dataProvider data_table_collation_check
+     * @ticket 21212
+     */
+    function test_table_collation_check( $create, $expected, $query, $drop ) {
+        self::$_wpdb->query( $drop );
+
+        self::$_wpdb->query( $create );
+
+        $return = self::$_wpdb->check_safe_collation( $query );
+        $this->assertEquals( $expected, $return );
+
+        self::$_wpdb->query( $drop );
+    }
 }