Protecting MySQL from SQL Injection Attacks with PHP
A guide on how to properly protect your MySQL database from SQL injection attacks by using a little common sense, good programming practices and PHP.
What is a SQL injection?
A SQL injection attack occurs when a malicious user attempts to add their own SQL statements into your SQL statements. This could be used to return account information, add Spam links to content or to mess up the database causing the site to crash.
A common activity on websites is to query a database for page information and pull off the content dynamically from the database. In this instance a query string parameter would be passed into SQL select statement to retrieve details for the requested page.
For example, you could have a URL like this (it could be developed like this or rewritten using mod_rewrite):
Within the php code you can retrieve these values and query the database like this:
- $reqPage = $_REQUEST['page'];
- $reqSection = $_REQUEST['section'];
- $query = "SELECT * FROM pages WHERE pageName = '$reqPage' AND pageSection = '$reqSection'";
Now this will work fine as long as valid parameters are passed into the query, and for the majority of the time this will be true, but what will happen if a malicious user decides to hack your website and cause problems? This can easily be done by injecting SQL into the parameters and could be used to delete all the records in the table for example.
If a malicious user was to type into the browser:
http://www.domain.com/index.php?page=tutorials§ion='; DROP TABLE users;
This would cause the $reqSection to contain the SQL injection and when combined into the query the final SQL as executed by the server will become:
- SELECT * FROM pages WHERE pageName = 'tutorials' AND pageSection = '''; DROP TABLE users;
This is obviously a very dangerous situation to be in and should be avoided at all costs.
What are the solutions?
A golden rule when programming is to validate/escape/prepare ALL user inputs, query string parameters, form fields and so on.
PHP Magic Quotes
Magic quotes were introduced into early versions of PHP to combat these threats. Magic Quotes does this by escaping single and double quotes however this method should not be used for a number of reasons.
Firstly, there is no guarantee that a server will have this enabled, by default PHP 5.2 onwards disables the option and PHP 6 has no support for it.
Second, you cannot depend on magic quotes to protect against all attacks.
Third it cannot protect against extended characters.
Finally it causes all quotes entered into the database to be escaped, polluting the data meaning that you will have to filter them out when you query the data again.
It is always safer to escape strings yourself and there is a quick and dirty method which uses the PHP htmlspecialchars function. This function will convert all non-alphanumeric characters into their html equivalents, so a single quote becomes ' and a double quote becomes " but only if it is set up properly (see htmlspecialchars for reference on this).
This method has been distributed as a way of escaping strings for SQL, however this is not the purpose of the function. There are still characters that will not be escaped, such as the forward slash, and as such it should not be used.
Some methods of protection focus on escaping or removing bad characters, but one of the most effective methods is to remove everything but known good data.
If you are asking for an age or date then the string should only contain numerical values plus a forward slash. If there are any other characters - such as a quote, remove them.
This cannot always work, because malicious injection attacks do not always use special characters, especially if numbers are being compared, since numeric values are not enclosed in a single quote in the statement.
- $parameter= "1 OR 1=1";
- $query = "SELECT * FROM accounts WHERE id = $parameter";
This can result in the following sql being executed, a statement which will always return all rows.
- SELECT * FROM accounts WHERE id = 1 OR 1=1
However if the parameter is checked for numeric characters only, anything else dropped from the string or returned to the user as a validation error, then you can be reasonably sure that the value is safe. However the same cannot be true for alphanumeric strings.
Proper SQL String Preparation
The best solution (IMO the only solution) is to use a function to prepare your SQL query. From PHP version 5 onwards there is support for MySql prepared statements using the mysqli library. This preparation is a function of the class, which will automatically escape and protect your SQL queries.
- $db_connection = new mysqli("localhost", "user", "pass", "db");
- $statement = $db_connection->prepare("SELECT pageTitle FROM pages WHERE pagename = ?");
- $statement->bind_param("s", $reqPage);
WordPress versions greater than 2.6 have an integrated prepare function to protect against injection attacks and features a string format style parameter.
- $query = $wpdb->prepare("SELECT pageTitle FROM pages WHERE pagename = %s", $reqPage);
- $result = $wpdb->get_var($query);
Most other database providers provide a prepare function; please refer to the documentation for your product for exact usage.