12

Do I need to use wpdb prepare before wpdb->insert?

If I am inserting values into a wordpress table using wpdb->insert, do I need to "clean" my data before inserting it or does this method (wpdb->insert) do that for me?

18

No, you shouldn't prepare or escape the data, this is done for you by the wpdb class.

From the wpdb class reference:

data:

(array) Data to insert (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).

If, however, you were writing your own SQL rather than using the insert method, then yes, you should escape using prepare.

  • 8
    To add a note: Both insert & update don't need that. But it should be used with query. – kaiser Aug 15 '11 at 12:27
0

The following is a warning for the wpdb class.

https://codex.wordpress.org/Class_Reference/wpdb

A Warning

Some of the functions in this class take an SQL statement as input. You must SQL escape all untrusted values you incorporate into the SQL query to prevent SQL injection attacks. Check the documentation to see if the function you plan to use escapes SQL for you or expects it to be pre-escaped.

So I read this as - the wpdb class does not automatically prepare or escape the data for you.

I am pretty sure that if you cannot trust 100% the data source in your code, then I suggest using the prepare class(?).

Do not think that using the prepare class will fix it without using the prepare class properly. I am fairly new to this so please post any corrections as a reply if I am not right.

$wpdb->prepare( "SELECT * FROM table WHERE ID = %d AND name = %s", $id, $name );

In the above statement, there are 2 extra attributes. One for the ID and one for the name. As far as I read it, each corresponds in order to the number of items in your query. Also %s = string, %d = integer and %f = float.

Also, from my reading, if you don't put the extra attributes in, then prepare will actually do nothing. There will be a warning, but if you switch that off, perhaps you won't know.

Here is an example from the class reference itself where they add a prepare class into an INSERT below.

https://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks

$wpdb->query( $wpdb->prepare( " INSERT INTO $wpdb->postmeta ( post_id, meta_key, meta_value ) VALUES ( %d, %s, %s ) ", array( 10, $metakey, $metavalue ) ) );

My concern is that the upvoted answer is incorrect according to the same page that 'nobody' references. I am assuming that you use prepare() but not other standard php methods of escape because I took this answer as correct as well... until I dug deeper.

Anyway... perhaps things have changed since the original answer.

  • hmmm, this sounds more like a question than an answer – Mark Kaplun Aug 28 '18 at 7:28
  • Accepted answer is correct. When you use functions like $wpdb->insert(), $wpdb->update()` or $wpdb->delete() data should be RAW. In a situation where you use e.g. $wpdb->query() and pass SQL statement as input, you should escape untrusted data. – nmr Aug 28 '18 at 10:22
  • nmr, I think I understand now. So to clarify... for my sanity... what you used as an example is 'the insert method and 'the delete method' as opposed to the examples that I used of using 'the query method' ... (%wpdb->query). Do I delete my answer? Or leave it? – Felixius Aug 29 '18 at 8:39
0

No you do not need to prevent against SQL injections when you use - wpdb insert or wpdb delete.enter image description here

See the following links:

https://codex.wordpress.org/Data_Validation#Database

https://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks

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.