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

Mathew Tinsley · Accepted Answer · 2015-04-07 20:22:18Z

8

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.

edited Apr 7, 2015 at 20:22

answered Apr 7, 2015 at 17:33

Mathew Tinsley

7,0162 gold badges30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2391468 Over a year ago

Hi mtinsley, thanks for the answer. So you would just build a query like (with the user form values instead etc): $user = $wpdb->prepare(' field1 = %s', '123'); $results = $wpdb->get_results( $wpdb->prepare('SELECT * FROM test WHERE' .$user) ); Does that look about right?

Mathew Tinsley Over a year ago

In that particular case it doesn't look like there is a point to building your query in parts, but that should work. If you post more of your code I can give you a more complete answer as to the correct approach to sanitizing your parameters.

user2391468 Over a year ago

Thanks mtinsley, ye sorry that was a bit of a crappy example. The code i've been working on is on my work computer. The idea is that i just need to add partial queries to the main query if the $_GET values are present. For example: $user_query = isset($_GET['query1']); $user_query2 = isset($_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') ); Is that how you would construct it?

user2391468 Over a year ago

Hi mtinsley. Thanks for the above. That makes good sense. Sorry i should of mentioned that it is a filterable search in essence so the user queries are optional. Thanks once again for the effort you've put in to explain this to me, it's much appreciated. I've updated the above to the accepted answer. I hope you have a good evening!

Collectives™ on Stack Overflow

Wordpress wpdb->prepare sql string from form

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related