Untitled Document

Protecting Against SQL Injections
By Dale Cover

Building dynamic, embedded, SQL queries is common in many web based applications. It is a quick and easy way to add flexility to an application without having to deal with the management of static queries and stored procedures. However, if not implemented correctly, it could potentially open up the database to malicious behavior.
One of the most common exploits of dynamic SQL queries is SQL Injections. With little skill, a malicious hacker could gain access to sensitive data, destroy the integrity of the data, and many other forms of misconduct and security breaches.

Take for example the following code snippet for generating an SQL statement:
------------------------------------------------------------------
<script language="javascript">

var strSQL;

function buildSQL(){

strSQL = new String("Select * from Customers where customer_ID = ");
strSQL += document.form1.sqlinjection.value;
alert(strSQL);

}

</script>

<form name="form1">

</form>
------------------------------------------------------------------

Copy the previous code snippet and simply paste it into an HTML document. Open the document and execute the example by inserting information into the text box and clicking the button. An alert box will appear with the newly concatenated SQL string. It should read:

Select * from Customers where customer_ID = 20745

Now, insert into the text box the following text:

20745 and customer_ID > 0

the newly concatenated SQL string should now read:

Select * from Customers where customer_ID = 20745 and customer_ID > 0

You should now have a record set with information for ALL of the customers in the database. Obviously, we don't want that to happen. However, that MAY not be so bad. But what if something like this is inserted?:

20745; Drop Table Customers;

Now we have a real problem!!!!!

To protect your data, you must implement strong security procedures and practices. To find out how to do that, read my previous article Securing Your Most Valuable Asset. This will give you guidance on how to setup secure database practices at the database level. At the application level, you can protect against these malicious injections by simply validating the users input. If you know, for example, customer_ID is expected, test the input data to ensure that only a valid range of numeric data is input. In this case, any character besides a numeric character should raise an error. When the error is raised, the application can now respond and protect the data.