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 .= "AND
query1LIKE $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
You can call
$wpdb->prepare
on partial queries: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 callprepare
oresc_sql
after escaping a like expression.Update: Using this example from the comments:
Here there isn’t any point to building the query in parts, you could just build your query like this:
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:
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.