Wordpress wpdb->prepare sql string from form

Question

I'm currently using a custom wordpress table to store an external xml feed and this information needs to be filterable by a basic html form with several options.

What is the best way to go about this and build the string using wpdb->prepare? I'm using the below for my pagination and the $user_query is currently set to $user_query .= "ANDquery1LIKE $query1 "; etc.

However i feel like this could lead to problems as i'm not doing it through the second parameter such as %d, $variable etc.

//Get Results
$results = $wpdb->get_results(
    $wpdb->prepare("SELECT * FROM `feed` WHERE `price` != 0 $user_query  LIMIT %d,
       %d", $offset, $items_per_page, OBJECT)
  );

I hope the above makes sense. I'm just trying to build the SQL query from the form $_GET values with no SQL injection issues.

Many thanks

Answer 1

You can call $wpdb->prepare on partial queries:

$user_query = $wpdb->prepare('AND query1 LIKE %s', $query1);

You can also call esc_sql directly on user input to sanitize it.

Also, LIKE expressions need to be escaped separately:

https://codex.wordpress.org/Class_Reference/wpdb/esc_like

$wpdb->esc_like escapes character specific to like expressions (%, \, _), but does not do any additional escaping. You still need to call prepare or esc_sql after escaping a like expression.

Update: Using this example from the comments:

$user_query = $_GET['query1']; 
$user_query2 = $_GET['query2']; 

$user = $wpdb->prepare('AND query1 = %s ', $user_query); 
$user2 = $wpdb->prepare('AND query2 = %s ', $user_query2); 

$results = $wpdb->get_results( $wpdb->prepare('SELECT * FROM test WHERE price != 0' . $user . $user2 . 'LIMIT 20') );   

Here there isn't any point to building the query in parts, you could just build your query like this:

$query = 'SELECT * FROM test 
            WHERE price != 0 
                AND query1 = %s 
                AND query1 = %s 
            LIMIT 20';
$results = $wpdb->get_results( $wpdb->prepare($query, $user_query, $user_query2) ); 

For the sake of example, I'll assume that the user queries are optional. If that is the case then you need to prepare your WHERE conditions separately only if the parameter is provided:

$query = 'SELECT * FROM test 
            WHERE price != 0';

if($user_query) {
    $cond = $wpdb->prepare(' AND query1 = %s', $user_query);
    $query .= $cond;
}

if($user_query2) {
    $cond = $wpdb->prepare(' AND query2 = %s', $user_query2); 
    $query .= $cond;
}

$query .= ' LIMIT 20';
$results = $wpdb->get_results( $query );

Note that there is no need to call prepare on the query when passing it to get_results as all user input has already been sanitized.