SQL Injection Attacks - Are You Safe?

Preventing SQL Injection Attacks

If you design your scripts and applications with care, SQL injection attacks can be avoided most of the time. There are a number of things that we as developers can do to reduce our site's susceptibility to attack. Here's a list (in no particular order) of our options:

Limit User Access

The default system account (sa) for SQL server 2000 should never be used because of its unrestricted nature. You should always setup specific accounts for specific purposes.

For example, if you run a database that lets users of your site view and order products, then you should set up a user called webUser_public that has SELECT rights on the products table, and INSERT rights only on the orders table.

If you don't make use of extended stored procedures, or have unused triggers, stored procedures, user-defined functions, etc, then remove them, or move them to an isolated server. Most extremely damaging SQL injection attacks attempt to make use of several extended stored procedures such as xp_cmdshell and xp_grantlogin, so by removing them, you're theoretically blocking the attack before it can occur.

Escape Quotes

As we've seen from the examples discussed above, the majority of injection attacks require the user of single quotes to terminate an expression. By using a simple replace function and converting all single quotes to two single quotes, you're greatly reducing the chance of an injection attack succeeding.

Using ASP, it's a simple matter of creating a generic replace function that will handle the single quotes automatically, like this:

<%    
   
function stripQuotes(strWords)    
stripQuotes = replace(strWords, "'", "''")    
end function    
   
%>

Now if we use the stripQuotes function in conjunction with our first query for example, then it would go from this:

select count(*) from users where userName='john' and      
userPass='' or 1=1 --'

...to this:

select count(*) from users where userName='john'' and      
userPass=''' or 1=1 --'

This, in effect, stops the injection attack from taking place, because the clause for the WHERE query now requires both the userName and userPass fields to be valid.

Remove Culprit Characters/Character Sequences

As we've seen in this article, certain characters and character sequences such as ;, --, select, insert and xp_ can be used to perform an SQL injection attack. By removing these characters and character sequences from user input before we build a query, we can help reduce the chance of an injection attack even further.

As with the single quote solution, we just need a basic function to handle all of this for us:

<%    
   
function killChars(strWords)    
   
dim badChars    
dim newChars    
   
badChars = array("select", "drop", ";", "--", "insert",      
"delete", "xp_")    
newChars = strWords    
   
for i = 0 to uBound(badChars)    
newChars = replace(newChars, badChars(i), "")    
next    
   
killChars = newChars    
   
end function    
   
%>

Using stripQuotes in combination with killChars greatly removes the chance of any SQL injection attack from succeeding. So if we had the query:

select prodName from products where id=1; xp_cmdshell 'format      
c: /q /yes '; drop database myDB; --

and ran it through stripQuotes and then killChars, it would end up looking like this:

prodName from products where id=1 cmdshell ''format c:      
/q /yes '' database myDB

...which is basically useless, and will return no records from the query.

Limit the Length of User Input

It's no good having a text box on a form that can accept 50 characters if the field you'll compare it against can only accept 10. By keeping all text boxes and form fields as short as possible, you're taking away the number of characters that can be used to formulate an SQL injection attack.

If you're accepting a querystring value for a product ID or the like, always use a function to check if the value is actually numeric, such as the IsNumeric() function for ASP. If the value isn't numeric, then either raise an error or redirect the user to another page where they can choose a product.

Also, always try to post your forms with the method attribute set to POST, so clued-up users don't get any ideas --- they might if they saw your form variables tacked onto the end of the URL.

Conclusion

In this article we've seen what an SQL injection attack is and also how to tamper with forms and URLs to product the results of an attack.

It's not always possible to guard against every type of SQL injection attack, however, hopefully you now know about the various types of SQL injection attacks that exist and have also planned ways to combat them on your servers.

Although we've only looked at SQL injection attacks with Microsoft SQL server in this article, keep in mind that no database is safe: SQL injection attacks can also occur on MySQL and Oracle database servers -- among others.