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.