SQL Injection Attacks: Are You Safe?

SQL Injection Attacks: Are You Safe? - Preventing SQL injection attacks
(Page 4 of 5 )

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 stop the likeliness of an attack happening. Here's a list in no particular order:

Limiting 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're running a database that lets users of your site view and order products, then you should setup a user called webUser_public that has SELECT rights on the products table, and INSERT rights only on the orders table.

If you're not making use of extended stored procedures, or have unused triggers, stored procedures, user-defined functions, etc, then remove then or move them to an isolated server. Most of the 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 in this article, 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 attach from working successfully.

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 --'

Which in effect stops the injection attack from taking place, because the clause for the WHERE query is now requiring both valid userName and userPass fields.

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 building 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 an 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 only cause no records to be returned 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 will comparing 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 into their heads by seeing your form variables tacked onto the end of the URL.
blog comments powered by Disqus