[wp-trac] [WordPress Trac] #34222: Impossible to correctly make a REGEXP meta query's value safe from untrusted input

[WordPress Trac]

#34222: Impossible to correctly make a REGEXP meta query's value safe from
untrusted input
--------------------------+-----------------------------
 Reporter:  johnbillion   |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:
 Severity:  normal        |   Keywords:  dev-feedback
  Focuses:                |
--------------------------+-----------------------------
 Given a meta query that places untrusted input into the `value` field and
 uses `REGEXP` for its comparison, it's not possible to make the query safe
 if it contains preg quotable characters (such as `$`) without invalidating
 the query.

 Example:

 {{{
 add_action( 'init', function() {

         $unsafe = wp_unslash( $_GET['foo'] );
         $safe   = preg_quote( $unsafe );
         $value  = '^' . $safe;

         new WP_Query( array(
                 'meta_query' => array(
                         array(
                                 'key'     => 'foo',
                                 'value'   => $value,
                                 'compare' => 'REGEXP',
                         ),
                 ),
         ) );

 } );
 }}}

 The above code uses `preg_quote()` to escape the user input, as it'll be
 passed to the query as `REGEXP '{value}'` and needs to be escaped. In
 `WP_Meta_Query::get_sql_for_clause()`, the value then gets inserted into
 the SQL query using `wpdb::prepare()`, which results in escaping of the
 slash that `preg_quote()` adds to the value, thus invalidating the query.

 Given the URL `example.com?foo=$100`, the following SQL will be generated.
 Note the double escaped `$` character.

 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
 FROM wp_posts
 INNER JOIN wp_postmeta
 ON ( wp_posts.ID = wp_postmeta.post_id )
 WHERE 1=1
 AND ( ( wp_trunk_postmeta.meta_key = 'foo'
 AND CAST(wp_trunk_postmeta.meta_value AS CHAR) REGEXP '^\\$100' ) )
 [snip]
 }}}

 '''tl;dr''' You can't use `preg_quote()` on a value that subsequently gets
 inserted into an SQL query using `wpdb::prepare()`.

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