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?
asked Aug 15, 2011 at 11:57
Add a comment
|
3 Answers
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.
-
10To add a note: Both
insert&updatedon't need that. But it should be used withquery.– kaiserCommented Aug 15, 2011 at 12:27
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.
-
-
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.– nmrCommented Aug 28, 2018 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?– FelixiusCommented Aug 29, 2018 at 8:39
No you do not need to prevent against SQL injections when you use - wpdb insert or wpdb delete.
See the following links:
https://codex.wordpress.org/Data_Validation#Database
https://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks
