It’s best practice to always use ->prepare to prevent against SQL injection attacks via user supplied data. This is simply a string checker function, string in > string out with variables added as it works.

You only need to use it for values you have obtained from somewhere else.

You should NOT use it for sql strings you are not adding variables too

$wpdb->prepare( the_sql_statement, one_or_more_values_to_include, , …)

The following placeholders can be used in the query string:

  • %d (integer)
  • %f (float)
  • %s (string)

A %s string simply has single quotes added around it (as well as the safety checks) so you can use it for numeric values too if you want

  global $wpdb;
  $sql = $wpdb->prepare("UPDATE {$wpdb->prefix}my_tablename SET
						my_fieldname1 = 123,
						my_fieldname2 = %s
					WHERE indexer = %d
					", 'Bert', $some_value);
  $wpdb->query($sql);

Variables to be added

It is fine to use it with no variables if there are none in the string (although not necessary to use it at all in this case of course!).

With variables in the string, there must be either multiple individual variables or a single array of variables:

  $sql .= $wpdb->prepare("SELECT * FROM my_table WHERE user_id = %d", $user_id);

  $sql .= $wpdb->prepare("SELECT * FROM my_table WHERE user_id = %d AND something_else = %s", $user_id, something_else);

  $sql .= $wpdb->prepare("SELECT * FROM my_table WHERE user_id = %d AND something_else = %s", $my_array_of_values);

Examples

Example using multiple times to create a final string to be used
/*
      $FieldNamesValuesArray = array();
      $FieldNamesValuesArray['UserType'] = 2;
      $FieldNamesValuesArray['BornYear'] = 1973;
      write_user_fields(bp_loggedin_user_id(), $FieldNamesValuesArray);
*/
function db_write_user_fields($user_id, $FieldNameValueArray)
{
  global $wpdb;
  
  $sql = "UPDATE {$wpdb->prefix}users SET ";

  $FirstField = true;
  foreach ($FieldNameValueArray as $key => $value)
  {
    if ($FirstField)
      $sql .= $wpdb->prepare(" $key = %s ", $value);
    else
      $sql .= $wpdb->prepare(", $key = %s ", $value);
    $FirstField = false;
  }
  
  $sql .= $wpdb->prepare(" WHERE user_id = %d", $user_id);

  $wpdb->query($sql);
  
}
UK Electronic Designer
USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.

Comments

Your email address will not be published.