How am I supposed to use prepared statements in Wordpress with variables in a query?

Question

I'm currently learning Wordpress and PHP, I'm also using WooCommerce. I currently have a form with three input fields, and I would like to check if the user inputted data about their order is true so the user can proceed to the next page.

My current code looks like this, and I am not sure if I am even going the right direction here, any help?

if(isset($_POST['submit'])) {
global $wpdb;

$ordernumber = $_POST['ordernmbr'];
$orderfirstname = $_POST['firstname'];
$orderpostnumber = $_POST['postnmbr'];

$ordernumber = stripslashes_deep($ordernumber);
$orderfirstname = stripslashes_deep($orderfirstname);
$orderpostnumber = stripslashes_deep($orderpostnumber);


$result = $wpdb->get_results($wpdb->prepare( "SELECT * FROM         $wpdb->wp_postmeta
  WHERE post_id = '$ordernumber' AND meta_value = '$orderfirstname'"));

There is only any point is preparing a query if it has parameters that you will replace on the execute. You are concatenating values, which is very unsafe [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Sep 04 '18 at 11:31

treyBake · Accepted Answer · 2018-09-04T12:21:43.107

6

You can do it using prepare:

$sql = 'DELETE FROM `wp_table` WHERE `id_field` = %d';
$wpdb->query($wpdb->prepare($sql, array($_POST['id']))

Good things to know:

%d - number
%s - string
%f - float

the array of passed variables works in sequential order, so if you had a query like:

SELECT * FROM `wp_table` WHERE `string_field` = %s AND `id_field` = %d

you'd do

array(
    $_POST['string'],
    $_POST['id']
)

if it's a DELETE/UPDATE use query and prepare. If a select use prepare and get_results.

SELECT:

$sql = 'SELECT * FROM `wp_table` WHERE `id` = %d';
$sql = $wpdb->prepare($sql, array($_POST['id']));
$res = $wpdb->get_results($sql);

edited Sep 04 '18 at 12:21

answered Sep 04 '18 at 11:49

treyBake

6,440
6
26
57

In the select statement at the end, if I have stored the $_POST data in to variables, could I use those instead in the array without it causing any problems later on? – Puppe Sep 05 '18 at 07:02
@Puppe yeah, the $_POST was just for examples sake :) – treyBake Sep 05 '18 at 07:20
Just making sure since I'm super new, thank you very much! – Puppe Sep 05 '18 at 07:26

score 4 · Answer 2 · answered Sep 04 '18 at 11:49

It's best practice to always use prepare but the main use of it is to prevent against SQL injection attacks, and since there is no input from the users/visitors or they can't effect the query then that is not an issue in your current example.

But like I said before it's best practice to use it and once you start using it you never stop, so in your example you can use it like so:

global $wpdb;
$tablename = $wpdb->prefix . "my_custom_table";
$sql = $wpdb->prepare( "SELECT * FROM %s ORDER BY date_created DESC",$tablename );
$results = $wpdb->get_results( $sql , ARRAY_A );

to read more about how to use it head to the codex

score 0 · Answer 3 · answered Sep 04 '18 at 11:42

you need to add your query conditional values as prepare function parameters. like the below statement

$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}postmeta WHERE post_id = %d AND meta_value = %s", $ordernumber, $orderfirstname);

for string values we use %s

for numeric values we use %d

How am I supposed to use prepared statements in Wordpress with variables in a query?

3 Answers3

Linked