1

I am attempting to use $wpdb->prepare to protect against SQL injection attacks and am lost. I have the following snippet below which checks the h_transactions table for a transaction id. If it doesn't find the id then it adds it to the table...

<?
global $wpdb;
$table_name = $wpdb->prefix . "h_transactions";
$registered = $wpdb->get_var( "SELECT COUNT(*) FROM $table_name WHERE transactionid = '{$transactionid}'" );

if ($registered == 0) {
  $wpdb->insert( $table_name, array( 'transactionid' => $transactionid ), array('%s')  );
}
else
?>

Do I need to use $wpdb->prepare on both the SQL query and the SQL insert?

2
  • where does $transactionid come from?
    – Milo
    Jan 10, 2014 at 0:29
  • It is a paypal transaction ID that is returned Jan 10, 2014 at 0:35

1 Answer 1

1

First, prepare() allows you to specify two different types of data:

  • %s for String Types
  • %d for Integer/Numeric Types

The reason is simple: MySQL only knows two (and a half) data types: Strings and Numerics (and Date/Time).

So this way you specify what type it is and make sure only that type gets trough.

Still it's important to get rid of possibly malicious parts of data by properly sanitize your inputs: esc_*() functions.

Here's your example:

global $wpdb;

// The actual statement - in a readable way
$SQL = <<<SQL
    SELECT COUNT(*)
    FROM {$wpdb->prefix}h_transactions
    WHERE transactionid = %d
SQL;

$preparedSQL = $wpdb->prepare( $SQL, esc_attr( $_POST['transactionID'] ) );
// Let's run it
$registered = $wpdb->get_var( $preparedSQL );

Note: Make sure that you use your real names. I changed some for readability.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.