Confused by $wpdb->prepare

Question

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?

Answer 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.}