SQL injection is a very popular technique to hack into database of a website by using incorrect user input with unescaped characters. It occurs when developer designs a bad database layer for an application. In WordPress, the database layer is $wpdb class, which is well-designed for this purpose. This class and WordPress itself have some built-in functions to help developers get rid of any SQL injection attack.
All these functions can be divided into 2 groups: input validation and query preparing.
Input validation functions
The input validation functions are useful when we need to take the right value from user input. They include:
esc_html($input): for getting plain text. This function will escape all HTML entities. The difference from PHP built-in function htmlspecialchars() is that htmlspecialchars() will double encode html entities if run twice.
wp_kses($string, $allowed_html): for getting HTML string. This will remove all unwanted HTML tags. You can read more about it at the Codex.
esc_url($input): for getting url. You don't need to use regular expressions to check the url anymore. This function will do everything for you.
is_email($email): for checking email address.
intval($input): for getting integer. This is built-in PHP function.
There're more validation functions that you can see at the Codex.
After getting the right values, you can now work with them, but it's not enough to use them in SQL queries. We need to escape them before pass into the query string.
Query preparing functions
Before query the database, make sure that the query string is escaped. This is can be easily done by using WordPress built-in functions: esc_sql($query) or $wpdb->escape($query).
They will escape a single string for use in a SQL query. Basically, they'll add slashes to single quotes, double quotes. They're very similar to PHP addslashes() function. If you really need a version of addslashes(), you should use the WordPress built-in function addslashes_gpc(), it will check the magic quotes is set or not before adding slashes.
These function work will fully query string. That means the query string should contains all needed variables. For example:
$query = $wpdb->escape("SELECT * from $wpdb->posts WHERE post_id=$id");
As you see, $id are passed directly into the query string. It is ok when all these variable have been escaped using functions that I mentioned above. But sometimes, you want a higher check for the data type of these variables. That's when we need to use $wpdb->prepare function:
$wpdb->prepare($query, (scalar) $value1, (scalar) $value2, ...);
where $query is a sprintf() like format string. It only understands %s (string) and %d (number), neither of which needs to be enclosed in quotation marks. Note that all values must not already be SQL-escaped.
For example, to get a comment from a known user, we can use the following code:
$wpdb->get_row($wpdb->prepare("SELECT * FROM $wpdb->comments WHERE comment_ID=%d and comment_author_email=%s", $id, $email));
Now, by using data validation functions and some escape functions for preparing SQL query you can work with the database without worry about SQL injection attacks, your code is protected! Here is the sample code of the process:
$data = esc_html($_POST['data']);
$result = $wpdb->get_results($wpdb->prepare("SELECT * FROM table WHERE field=%s", $data));
For better performance and compatibility, you should use built-in query functions of $wpdb class. All of them are well-documented at the Codex.
Leave a Reply