nazly.me

Weblog of Nazly Ahmed

Nazly Ahmed

Nazly Ahmed

Web Developer. PHP Addict. Wordpress Hacker. FOSS Enthusiast. (Micro)Blogger. Photo Hobbyist. Cricket Fanatic. Husband. Dad.

SQL injection is one of the deadliest techniques attackers use to exploit the weakness in your database code of your website. Regardless of the technology/scripting language you must make sure your code is 100% perfect against SQL injection.

Here I will use PHP and MySQL examples for its wide usage and also I’m much more comfortable with it.

Here is a basic PHP code that most developers will come up with to access the MySQL DB and get the record of a particular username submitted from a form in our website.

<?php
# Get posted username value
$userName = $_POST["usname"];
# MySQL query string to get the record of the user
$queryStr = "SELECT * FROM users WHERE usname = '$userName'");
# Output the string for debugging
echo $queryStr;
# Execute the MySQL query
$result = mysql_query($queryStr) or die(mysql_error());
?>

For example if the username that was submitted is nazly the code will output the following query and execute it.

SELECT * FROM users WHERE usname = 'nazly'

While the query works perfectly and returns the record of that particular user, a attacker can exploit this code by injecting SQL using the submission form.

For example if the attacker submits ‘ OR ‘t’=’t instead of the username the query will be formed like this.

SELECT * FROM users WHERE usname = '' OR 't'='t'

When this query is executed, it will return all the records in the database since t=t will be TRUE always. The impact it will have on the website will be depend on the code after executing the query. But the important thing is someone can make the query behave differently than what we actually expected from it.

It can become deadlier than that if someone submits the following instead of the username
a’;DROP TABLE users; SELECT * FROM userinfo WHERE ‘t’ = ‘t
The query for the above value will look like this

SELECT * FROM users WHERE usname = 'a';
DROP TABLE users;
SELECT * FROM userinfo WHERE 't' = 't'

If the above query is executed, it will delete the whole users table. Similarly an attacker can inject any type of SQL code to modify/delete your tables in the database.

It is a huge security flaw in your code but newbies and even some experienced developers don’t understand the depth of problem. So developers should make sure to take precautionary measures against it.

In PHP you can use the mysql_real_escape_string() function for this task. This function will escape any special characters in the string to be used in a SQL statement.

<?php
# Get posted username value by escaping special characters
$userName = mysql_real_escape_string($_POST["usname"]);
# MySQL query string to get the record of the user
$queryStr = "SELECT * FROM users WHERE usname = '$userName'");
# Output the string for debugging
echo $queryStr;
# Execute the MySQL query
$result = mysql_query($queryStr) or die(mysql_error());
?>

If you try to inject SQL to this example, it will have no affect to the Database since the use of this function

If you are a developing a WordPress plugin for your website, you must make sure to protect the site against SQL injection as well. Since WordPress has its own class for database manipulation you should use the methods available in WordPress.

The escape() function in the WPDB class is much similar to using the standard mysql_real_escape_string() function.

<?php
function myWpPluginFunc($usName){
    global $wpdb;
    $u = $wpdb->escape($usName);
    $wpdb->query("SELECT * FROM users WHERE usname = '$u'");
}
?>

But there is a better option available in WordPress. Rather than escaping individual values you can format the SQL statement and then use the prepare() function in the WPDB class to escape the special characters. The syntax is similar to using sprintf(). Using the prepare() function, the developer is sure that all values are escaped. So less chance for errors.

<?php
function myWpPluginFunc($usName){
    global $wpdb;
    $qstr = $wpdb->prepare("SELECT * FROM users WHERE usname = %s", $usName);
    $wpdb->query($qstr);
}
?>

For more check out Data Validation in WordPress.

I wish MySQL functions in PHP had a similar function like WordPress’s prepare()

Posted on 17th February 2010 17:37:35