SQL Injection Protection

So not too long ago I was asked at work to see why on one of our webservers a database that was hosting some not-so-critical information appeared to be blank. The database was for a web application that hosted some calanders for specific types of events (i’m choosing to remain vague because it’s not really that important to the lesson that is to be learned here). Well when I ran a couple of SELECT calls on the DB I did in fact notice that the `user` and `schedule` tables were blank. They appeared to be completely truncated. Before restoring a backup of the DB I decided to take a look at the code for the web app. It was a ASP based application, and taking a look at the database calls I noticed a huge security flaw. Taking a look at the http request log confirmed what I was thinking, SQL Injection attack.

SQL Injection Defined: SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application (like queries). The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It happens from using poorly designed query language interpreters.

    Basically someone wrote some poor code, suprise suprise.

So first, lets look at some bad code. I will be coding in PHP but the methods here are easily portable to any dynamic web scripting language.

What Not To Do:

$username = $_POST['username'];
$password = $_POST['password'];

$row = mysql_fetch_assoc(mysql_query("SELECT * FROM `users` WHERE `username`='{$username}' AND `password`='{$password}'"));

Why is this horrific? So many reasons. It’s a huge vulnerability waiting to be exploited. When you allow the end user of the web application to send their variables right into the Query without first examining and/or cleaning what they have passed some pretty disasterous things can and will happen.

For instance, say I’m some malicious end user, (muahhahaha) I stumble upon your site, I see a nice login box and now I decide to test the security you have in effect. Hmm… lets see if I can log in as administrator ? 😉

For the user name field, I enter the following: administrator’; —
And the password field, I enter the following: nothing

This now changes the query to the following:

SELECT * FROM `users` WHERE `username`='administrator'; --' AND `password`='nothing';

I have now successfully logged in to your webapp as administrator, bypassing the security, with no strings attached.

Let’s just say though I would like to be a bit more malicious. And for the username field I enter the following: a’; DROP TABLE `users`; —

Now the query would read:

SELECT * FROM `users` WHERE `username`='a'; DROP TABLE `users`; --' AND `password`='';

And now suddenly your entire Users table is dropped. I could sit here for hours taking random guesses at table names and just dropping your valuable long accumulated information in the database. But now lets take a simple way in which we could prevent some of these attacks.

Along with preventing SQL injection we could also do a rudimentary check for XSS (Cross Site Scripting) attacks as well. They work along the same lines only instead of being a database injection, if you had a website that took user input as a post of theirs and displayed it on your page they could enter a <script> instead and every time their post loaded the script would run from the web server, perhaps providing an individual with some cookie based information.

The functions look as follows:

function secureInput(&$value, $key) {
    $value = htmlspecialchars(stripslashes($value));
    $value = str_ireplace("script","blocked",$value);
    $value = mysql_real_escape_string($value);

    return $value;

function secureGlobals() {
    array_walk($_GET, 'secureInput');
    array_walk($_POST, 'secureInput');

secureGlobals(); // can be used at the beginning of every page load to secure globals
$inline = secureInput("a'; DROP TABLE `users`; --", ''); // an inline example

What this does:

  • Each of the functions that are included with a standard install of PHP are hyper linked to the PHP manual if this is the first time you are seeing any of them.
  • When secureGlobals() is called we walk both the $_GET super global and $_POST super global and pass each value (by reference) and key to the function secureInput.
  • secureInput() starts to clean each of the values, first striping and slashes, then converting the HTML special characters over to an encoded format to make it visual instead of HTML markup. Then we do an case insensitive string replacement on anything in the input named ‘script’ changing it to ‘blocked’ further preventing XSS attacks. Then finally we take the value and cleanse it for SQL by using mysql_real_escape_string which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.

Now what are we left with? Pretend we run another SQL injection attack, let’s say we try to drop the user table again. So the user name input field I put: a’; DROP TABLE `users`; —

Run the script and this Is now what the SQL Query would look like:

SELECT * FROM `users` WHERE `username`='a\'; DROP TABLE `users`; --' AND `password`='';

The script runs correctly finding no user by the name “a’; DROP TABLE`users`; –“.

Although this was just a quick run through and the secureInput() function could be made even more functional, having just this simple protection on your SQL queries will save you plenty of headaches in the future.


Oh and yes, I named the post that because it rhymed.

Hello world!

Seems appropriate. “Hello World” typically every programmers first application in any virgin learned language.

So a little about myself. Bachelor in computer science, completely self taught and first learned programming on a Commodore 64 (remember those?). This blog will not only be about programming, but anything that I deem interesting in the terms of technology. Currently I work as a software engineer for Nassau BOCES, am a member manager of Counter Clock Software LLC, and maintain several opensource projects. Enough about me, you will learn little things here and there I’m sure as I post more often.