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?

  • where does $transactionid come from? – Milo Jan 10 '14 at 0:29
  • It is a paypal transaction ID that is returned – fightstarr20 Jan 10 '14 at 0:35
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, privacy policy and cookie policy

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