Home arrow MySQL arrow Page 4 - SQL Injection Attacks: Are You Safe?

SQL Injection Attacks: Are You Safe?

Is your database safe from SQL injection attacks? In this article Mitchell tells us exactly what they are and shows us how to prevent them from occuring.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 58
May 29, 2002
  1. · SQL Injection Attacks: Are You Safe?
  2. · What is an SQL injection attack?
  3. · Injection attack examples
  4. · Preventing SQL injection attacks
  5. · Conclusion

print this article

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), "")

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

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials