Changeset 32162

Timestamp:

04/20/2015 04:45:12 AM (11 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 );
+        }
 }