Protect Your Code Against Destructive SQL Injection
October 27, 2007
SQL injection is a technique by which an end user exploits insecurities in an application resulting in harmful modifications to database queries. Often times, the security vulnerability exists as a result of lack of knowledge, lack of testing, and/or laziness on the developer’s part.
SQL Injection Example
To demonstrate SQL injection, assume Facebook.com uses a MySQL database with a table named “users” for storing basic user information, including usernames and passwords. In order for users to login to Facebook, a username and password must be provided within a login form, at which point the data is provided to the PHP script. Without accounting for SQL injection, the PHP code might look something like the following.
$username = $_POST['username']; $password = $_POST['password']; $sql = "SELECT * FROM `users` WHERE `username` = '$username' AND `password` = '$password'"; $res = mysql_query($sql, $conn);
For standard data, this query will execute fine. Assuming one user’s name is “john” and his password “johnny5″, the SQL query will convert into the following string prior to execution.
SELECT * FROM `users` WHERE `username` = 'john' AND `password` = 'johnny5'
However, imagine if a destructive individual encountered Facebook and attempted to enter his username as “'; DROP TABLE users; -- “. The PHP script would in turn executing the following SQL query.
SELECT * FROM `users` WHERE `username` = ''; DROP TABLE users; -- ' AND `password` = ''
The SELECT statement would not return any results, and — assuming the MySQL account used for accessing the users table has privileges to do so — the DROP statement would remove the table holding all user account information. The double dash would simply cause the remaining SQL to act as if it is a comment. This is clearly unacceptable and, at the very least, could potentially ruin the reputation of the website or application and its owner.
Protecting Against SQL Injection
Ideally, all parameters that the end user has access to should be filtered for string literal escape characters. Fortunately, defending against SQL injection attacks is relatively easy in many languages thanks to built-in scrubbing functionality. PHP, for example provides functions for MySQL, PostgreSQL, Oracle, and various other frequently used databases.
The following is an example of how to protect inline SQL queries against injection through use of the built-in mysql_real_escape_string function.
$username= mysql_real_escape_string($_POST['username'], $conn); $password = mysql_real_escape_string($_POST['password'], $conn); $sql = "SELECT * FROM `users` WHERE `username` = '$username' AND `password` = '$password'"; $res = mysql_query($sql, $conn);
It should be noted that the above example is dependent upon the specific setting of magic quotes. For an all-encompassing solution that you may consider to be more worthwhile, a conditional can be used to test the usage setting of magic quotes on the server, stripping slashes from the query string parameters as required. The following example demonstrates this solution.
if (get_magic_quotes_gpc()) {
$username = mysql_real_escape_string(stripslashes($_POST['username']), $conn);
$password = mysql_real_escape_string(stripslashes($_POST['password']), $conn);
} else {
$username = mysql_real_escape_string($_POST['username'], $conn);
$password = mysql_real_escape_string($_POST['password'], $conn);
}
$sql = "SELECT * FROM `users` WHERE `username` = '$username' AND `password` = '$password'";
$res = mysql_query($sql, $conn);
Obviously, the code can become somewhat hefty for filtering inline SQL. This can be resolved by creating a function to perform cleaning for any provided parameter. Alternatively, creating prepared statements with MySQL Improved Extension in PHP5 can be helpful not only for preventing SQL injection, but also for speeding up and standardizing commonly used queries.
Comments
5 Responses to “Protect Your Code Against Destructive SQL Injection”
Got something to say?
I made the same article but it’s for coldfusion
thanks for the article, useful
mysql_query has an often inconvenient limitation that only one sql query can be executed in one call. This has the side effect of limiting SQL injection; in the example above, an error would be thrown, rather than dropping the users table.
That aside, checking your input and/or using prepared statments is a much safer way to go.
Further to the above, the one statement per mysql_query call limitation is apparently controlled by the CLIENT_MULTI_QUERIES flag when MySQL is compiled; this is the default behaviour though, so if you’re using a precompiled package then you’re relatively safe.
your tutorials are real helpful ,thanks a lot !!!
but still i have some problem
1. how to change the size of logo pic