{"id":2023,"date":"2019-05-04T13:12:07","date_gmt":"2019-05-04T12:12:07","guid":{"rendered":"https:\/\/ibex.tech\/cloud\/?p=2023"},"modified":"2022-02-17T07:13:49","modified_gmt":"2022-02-17T07:13:49","slug":"wpdb-prepare","status":"publish","type":"post","link":"https:\/\/ibex.tech\/cloud\/wordpress\/database\/queries-database\/wpdb-prepare","title":{"rendered":"$wpdb->prepare"},"content":{"rendered":"\n<p>It&#8217;s best practice to always use -&gt;prepare to prevent against SQL injection attacks via user supplied data.  This is simply a string checker function, string in &gt; string out with variables added as it works.<\/p>\n\n\n\n<p>You only need to use it for values you have obtained from somewhere else.<\/p>\n\n\n\n<p><strong><em>You should NOT use it for sql strings you are not adding variables too<\/em><\/strong><\/p>\n\n\n\n<p>$wpdb-&gt;prepare( the_sql_statement, one_or_more_values_to_include, , &#8230;)<\/p>\n\n\n\n<p>The following placeholders can be used in the query string:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>%d (integer)<\/li><li>%f (float)<\/li><li>%s (string)<\/li><\/ul>\n\n\n\n<p>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<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  global $wpdb;\n  $sql = $wpdb->prepare(\"UPDATE {$wpdb->prefix}my_tablename SET\n\t\t\t\t\t\tmy_fieldname1 = 123,\n\t\t\t\t\t\tmy_fieldname2 = %s\n\t\t\t\t\tWHERE indexer = %d\n\t\t\t\t\t\", 'Bert', $some_value);\n  $wpdb->query($sql);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Variables to be added<\/h4>\n\n\n\n<p>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!).<\/p>\n\n\n\n<p>With variables in the string, there must be either multiple individual variables or a single array of variables:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  $sql .= $wpdb->prepare(\"SELECT * FROM my_table WHERE user_id = %d\", $user_id);\n\n  $sql .= $wpdb->prepare(\"SELECT * FROM my_table WHERE user_id = %d AND something_else = %s\", $user_id, something_else);\n\n  $sql .= $wpdb->prepare(\"SELECT * FROM my_table WHERE user_id = %d AND something_else = %s\", $my_array_of_values);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Examples<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">Example using multiple times to create a final string to be used<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>\/*\n      $FieldNamesValuesArray = array();\n      $FieldNamesValuesArray&#91;'UserType'] = 2;\n      $FieldNamesValuesArray&#91;'BornYear'] = 1973;\n      write_user_fields(bp_loggedin_user_id(), $FieldNamesValuesArray);\n*\/\nfunction db_write_user_fields($user_id, $FieldNameValueArray)\n{\n  global $wpdb;\n  \n  $sql = \"UPDATE {$wpdb->prefix}users SET \";\n\n  $FirstField = true;\n  foreach ($FieldNameValueArray as $key => $value)\n  {\n    if ($FirstField)\n      $sql .= $wpdb->prepare(\" $key = %s \", $value);\n    else\n      $sql .= $wpdb->prepare(\", $key = %s \", $value);\n    $FirstField = false;\n  }\n  \n  $sql .= $wpdb->prepare(\" WHERE user_id = %d\", $user_id);\n\n  $wpdb->query($sql);\n  \n}<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>It&#8217;s best practice to always use -&gt;prepare to prevent against SQL injection attacks via user supplied data. This is simply a string checker function, string in &gt; 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 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[169],"tags":[],"class_list":["post-2023","post","type-post","status-publish","format-standard","hentry","category-queries-database"],"_links":{"self":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/2023","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/comments?post=2023"}],"version-history":[{"count":9,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/2023\/revisions"}],"predecessor-version":[{"id":2857,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/2023\/revisions\/2857"}],"wp:attachment":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/media?parent=2023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/categories?post=2023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/tags?post=2023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}