Make Sure Your Plugin Protects Against SQL Injections with $wpdb->prepare()

What are SQL Injection attacks?

SQL injection attacks are things that every web developer should know about and should learn how to prevent.  Simply, a SQL injection attack is when a user inputs executable SQL code into an entry field that queries the database.  For example: instead of the user entering their username, they enter some executable SQL code that is most likely malicious.  Below is a generic example from Wikipedia:

What a normal user will enter:

"SELECT * FROM users WHERE name =‘username’;"

What is entered in an SQL injection attack:

"SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't’;”

So, as you can see, the second statement will drop the “users” table and then will display all the data in the “userinfo” table.  This is not good!

How can they be prevented?

Now that we understand what SQL injection is at a basic level, lets talk about how to prevent this when developing WordPress plugins.  First of all, this post should only apply to developers who are creating a custom table in the database, not just adding an option.  Since you can already use the add_option()update_option()get_option() and delete_option() for interfacing with a an option, there is no need to write actual queries.

Assuming you have created your own custom table in the database, you now need to run SQL queries on the database in order to add, update, get and delete data.  This is where you need to be very careful and where SQL injections can take place.  WordPress has its own API to access the database and to run queries, which are accessed from the $wpdb object.  With $wpdb you can perform any query you want, which is both good and bad.  Its only bad if you perform unsafe queries which could result in an SQL injection attack.  So, how do we prevent an SQL injection attack using the $wpdb object?

Using the $wpdb->prepare() function

On a very basic level we have to use the $wpdb->prepare() function.  This function has two parameters which are the SQL statement and the input that needs to be prepared.  By preparing a statement we mean that the inputed data will NOT run as executable code.  Instead it will be considered a string.  This prevents SQL injections and makes your plugin safe to use.  Lets look at a simple example:

global $wpdb;
$wpdb->query($wpdb->prepare( "SELECT * FROM table WHERE id = %d", $id ));

Thats all there is to it.  Just use this $wpdb->prepare() function whenever you are querying with dynamic data.

Let us know your thoughts and comment below with questions or feedback.

3 thoughts on “Make Sure Your Plugin Protects Against SQL Injections with $wpdb->prepare()”

  1. Hello! I just wanted to ask if you ever have any trouble with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no data backup. Do you have any solutions to prevent hackers?

  2. Thanks for commenting! The first thing I would do is get in the habit of backing up your site (whether it be manually or automatically). Secondly, I would look at all the plugins you have installed and make sure that they are being updated frequently. WordPress comes standard with great security features so your problem could have come from a security hole in a plugin or theme.

  3. Hi,
    I’m new to sql, php etc. but have been using wordpress for a number of years mainly on the design (css, html) side of things.
    I’ve started delving into the internal code, and about to launch the first wordpress site where I have created the code.
    I am updating and inserting data using multiple forms should I use the “$wpdb->prepare()” in “$wpdb->update()” and also “$wpdb->insert()” as well as “$wpdb->get_results()” and should the “$wpdb->prepare()” be enough for me to feel safe about my queries?

Leave a Reply