SQL Injection

Hello Folks, Long time no see !! I know I was also missing writing since couple of months. To fulfill my hunger of writing and your hunger of reading, here I am ready with another topic to shed some light on...

Our Todays topic is SQL Injection..

Another common vulnerability which is result of slipshod input validations is SQL Injection. Generally cross-site scripting vulnerabilities are actually directed to your site's visitors whereas SQL Injection is something which directly attacks on your site itself – to be specific its Database.

Target of SQL Injection activity is to insert arbitrary data, mostly DB Queries, which are eventually executed by the Database using your script only. The subtle query may attempt any no. of actions starting from retrieving the records till removing significantly all records from database including altering or modifying the same.

[In this blog, I have used PHP Scripting Lang. extensively, but note, it is possible with any Scripting language and PHP is used as I love it.. ;P ]

To demonstrate it in more detail, look at the below example :

Consider a simple query where I want to retrieve the user name and password of user named 'Sandip'.

$strSelectQuery ="Select username, password from tbl_Users where user_name='".$userName."'"

For this Query, in my script I am taking user name as in a variable:

$userName = "sandip"

So that it would make my call as :
mysql_query("Select username, password from tbl_Users where user_name='".$userName."'");

Simple ain't it ? But here's the glitch..

What would happen if instead of simple 'sandip' input some enters:

$userName = "sandip’; DELETE FROM users;"

By appending an entirely new query to $userName, the call to the database turns into disaster: the injected DELETE query removes all records from users.

Still Confused how ? Ok here, if notice the single quote and semi-colon given in user input, those two entities will complete the first query successfully causing next query for Delete User stacked in queue and getting it executed will remove all of those users off the table.

Hoohh !!! Scary huh ??

But don't worry, PHP is here to rescue, Magic Quotes is something provided by PHP. This is PHP's automatic input escaping mechanism. magic_quotes_gpc, provides some basic protection. If "magic quotes" enabled, it adds a backslash in front of single-quotes, double-quotes and other characters that could be used to break out of a value identifier. If not enabled, you can still use addslashes feature given by PHP in combination to protect at moderate level.

E.g.
if (!get_magic_quotes_gpc()) {
$userName= addslashes($userName);
}

$strSelectQuery ="Select username, password from tbl_Users where user_name='".$userName."'"

Wondering how escaping would help ! Ok, carefully note, in first illustration, it was single quote which caused our first query completed making second one stacked and executed. How if we do not let our first query itself completed ?? Yeah, you got it !! escaping single quotes from input string would add slashes in it and would in turn hold the query to be completed forcefully by user input.

There are many of the database extensions available for PHP include dedicated, customized

escape mechanisms. E.g. the MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL and if you are not much interested in using addslashes you can surely tweak the code as :

if (get_magic_quotes_gpc()) {
$userName = stripslashes($userName);
}

$userName = mysql_real_escape_string($userName);

$strSelectQuery ="Select username, password from tbl_Users where user_name='".$userName."'"

Note, here before giving a call to the function, checking magic code state is important in else case input would be escaped twice.

Ouch !! Unfortunately, escaping the single quote does not always guarantee you security of your code from SQL Injection. There are certain queries that still allows SQL Injection despite of you escaping the input. Consider following example, you would know what I mean :

$user_id = "0; DELETE FROM tbl_Users";

$user_id = mysql_real_escape_string($user_id); // 0; DELETE FROM

If you note here, we are expecting a numeric value as an input to our query, and as you know it’s not necessary to enclose the value inside single quotes. Thus, even if we escape the input, there is nothing to escape and hence still causing the second injected query of delete execute successfully causing our Database severe damage.

But don't worry, we still have solution to this problem as well. How about Casting feature of PHP. As we know, we gotto have numeric value as an input. Why don't we cast it ?? Feeling good to know this...

So, if an integer is required, cast the incoming datum to an int; if a complex number is required, cast to a float. As simple as that Lets see one illustration for this as well :

$user_id = "0; DELETE FROM tbl_Users";
$user _id = (int) $user_id; // 123
$strSelectQuery ="Select username, password from tbl_Users where user_name='".$userName."'"

A cast forces PHP to perform a type conversion. If the input is not entirely numeric, only the leading numeric portion is used. If the input doesn’t start with a numeric value or if the input is only alphabetic and punctuation characters, the result of the cast is 0. On the other hand, if the cast is successful, the input is a valid numeric value and no further escaping is needed.

Numeric casting is not only very effective, it’s also efficient, since a cast is a very fast, function-free operation that also obviates the need to call an escape routine.

Aahh !! Relax, Now we are much safe here !!!

Hope you all have read the article and found helpful to take care of those things in your development !! Feel Free to share your feedback...