WordPress.org

1. Gabriel Koen
   Member
   Posted 2 years ago #
   

   Came across an issue where only the first query (autoload options) was being sent to the read slave(s). Turns out a couple plugins had the syntax 'SHOW TABLES LIKE...' which HyperDB thought was a read query, since it didn't match 'SELECT' as the first word. Made a quick fix for what I think are some common read statements to db.php:
   

   function is_write_query( $q ) {
   		$pattern = '/^\s*(SELECT|SHOW|DESCRIBE|EXPLAIN)\s+/i';
   		return (preg_match($pattern, $q)) ? false : true;
   	}

   As you can see my regex-fu is weak, I spent a good 40 minutes trying to negatively match those words. Gave up, instead positively match them and then return true if there's *not* a match.

   http://wordpress.org/extend/plugins/hyperdb/

2. chadedge
   Member
   Posted 1 year ago #
   

   Also, please note that newlines, tabs and sql comments will return a false-positive.

   Some of our queries passed to the is_write_query() use sql-comments, such as the following:

   $q = "/* a_function_call_name -- cached, CE */ SELECT foo FROM bar ...";

   You'll need to do a preg_replace (or sommat) to the $q query BEFORE the return to ensure that you've escaped spaces, tabs, etc.

   My regex is weak, too, but I've got testing to do, so here's mine:

   function is_write_query($q){
                           $q = preg_replace('/\/\* *([a-zA-Z0-9\-\.\_ ]*)\*\//',' ',$q);  // trim comments
                           $q = ltrim($q,"\t\r\n (");      // trim whitespace
                           return !preg_match('/^(?:SELECT|SHOW|DESCRIBE|EXPLAIN)\s/i',$q);
                   }       // end function is_write_query()

   Hope that helps someone else out there,

   --Chad

Topic Closed

This topic has been closed to new replies.