Not sure how many of these I've written but I decided to start this article on this site as I've been replying to other peoples web articles on SQL-Injection telling them how their prevention methods won't work. I feel that there are lots of articles on the web explaining how SQL-Injection works but not how to prevent these kind of attacks. I can't make a system unhackable but I can list a number of ways to make it much harder and also what to look for in your system to see what's vulnerable. For a quick checklist and simple tests, please read my article: Basic tests for SQL-Injection vulnerabilities.

Now I don't usually find worth in writing an article criticizing others but I'm not aiming to criticize. Hopefully by following this you can see where the vulnerabilities will be in your system. Webdevelopers have to know what this is because according to OWASP it accounts for 60% of website hacks with Tech Herald saying half a million sites were hacked in 2008 using this technique.

The Risks
An entry that allows users to enter text that will be used to interact with a website database? Well depending on the permissions of the database user running the script, they can drop/alter/create tables and content, execute/. You could come in to work to find your company website rebranded (usually with the title "Hacked by ..."), defaced (referencing files on remote sites). If vulnerable, you can expect that all your customers details can be exposed (see Amusing Examples below).

If your website is database-driven then basically your website will need to be restored along with an upgrade to prevent future hacks. Factor in your IT staff spending a minimum of a day analysing 60'000 rows of logs (manually) to examine how the hacker got in and how to prevent it.

The Query
We're going to cover SQL-injection here (and not XSS-Injection because Cross-site scripting or XSS, depending on your server setup, is more client-side orientated -- arguable). Consider the following query for a website login form:

WHERE Username='admin' 
AND Password='secret'
The HTML form will be asking for your username and password. So in the example above, we entered "admin" as the username and "secret" as the password. This query will fail if the password is not "secret"... Sounds straightforward enough.

For those of you that don't know your SQL, think of it as a statement which has to evaluate to true or false. If true (so both username and password are the ones in the database), the system will let you in.

Herein lies the problem
Notice how the SQL query is written with apostrophes, well what if the user typed in their own apostrophes? This changes the query so we'll put as username "admin' OR '1'='1" (notice how I don't need a last apostrophe). Submitted to the same form as before the query becomes:
WHERE Username='admin' or '1'='1'
AND Password='secret'
If you know your SQL, you'll admit the above is a valid query which will evaluate to TRUE. Even if the password is NOT "secret" this will always evaluate to TRUE because 1 is definitely equal to 1. So in plain english, the password can be any value if the username field is not checked for SQL-Injections.

The Challenge by Author #1: VB Helper
Find a way to circumvent my anti-SQL-injection script which replaces apostrophes with double apostrophes (escapes apostrophes as strings).
// Yields
WHERE Username='admin'' or ''1''=''1'
AND Password='secret'

My message to the author
Escaping the apostrophes in any of the languages is not a silver bullet, and unfortunately would not affect url-encoded or account logins dependant on numbers.

Consider the following (also valid):
• Username = "admin%27%20OR%20%271%27=%271"
• Password = "whatever"
-- Yields
WHERE Username='admin' OR '1'='1'
AND Password='whatever'
Systems that expect/use a number could be the following:
• 1&1 Username = "105876572587%20OR%201=1"
• Password = "who cares?"
-- Yields
WHERE Username=105876572587 OR 1=1
AND Password='who cares?'

What's vulnerable?
Basically any variables in any HTML form on your site, especially free text fields (where the user can type any text) as well as any dynamic variables (which always catches Microsoft out). Dynamic variables tend to be a variable that was generated on-the-fly and a tell-tale sign is usually when it is suffixed with a number (eg. item1, item2, item3...).

Ignore anyone who tells you it's only variables that can be added to the URL/web address of the site because it's a small matter to create a form on a test server which posts (using POST method) these variables to your website. And just because you're using SSL on a HTTPS domain, this doesn't prevent these kind of attacks!

Prevention Basics
  • Setup at least 2 database users. One which your developers will use when dealing with the database directly. Another with just SELECT permissions, so Read-Only! (I also include USE TEMPORARY TABLES in the case of some sites driven by a CMS).
  • Nearly all Microsoft SQL Server web applications by default are vulnerable. Oracle's MySQL is not!
  • If you are expecting a received form variable to be a number, multiply it by 1 (just in case). Anything that is not a number should return -1 (negative 1).
  • Examine website pages with more than 1 HTML form. Check the variable names are all accounted for (especially dynamic ones).
  • Blind SQL-Injection: make your system database and its usernames unrelated or made of words that could not have been extrapolated from your website. Verify what standards you ought to conform to, and where in the interests of security, you deviate from the norm.
General Anti-SQL-Injection Method #1:
My favorite is checking the username, looking up the password in the database, then comparing that password to the one the user has posted. Your query become:
SELECT Password FROM Logins WHERE Username='admin';
You can check the username posted by the user to no end because most usernames should be alphanumeric and not contain any symbols (ie. escape apostrophes, trim, check they're in the format you use). (For emails just allow an @ symbol and that's it). Now using the VB or PHP you are using, compare the retrieved value to the one the user posted.
// PHP
If ($_REQUEST['Password']==mysql_result($results, 0, "Password")) then login

// VBScript/Asp
If (request.form("Password")==Request.ServerVariables("AUTH_PASSWORD")) then login

General Anti-SQL-Injection Method #2:
On most of my websites this is the one I use. A huge function checking for various forms of SQL-Injection which then emails all system administrators with as many details on the client that the system can find. If you can, my "secret" weapon (scuse the quotes but it's not really a secret) is to automatically block these IP addresses when they
  1. try to send more than 100 queries per second to the database
  2. Include a SQL command in the HTML form
so create a small audit table in the database (small as in minimal setup, eg. ID, Event, IP, User, Date, # of events) - this will get big and will need housekeeping, I update and group by IP address so that one IP address sending 10000 queries will only take up 1 row in the database, as opposed to 1 for each event. You can then use this to prevent anonymous website visitors from running too many queries in one go. I block on 100+ queries per second - this will depend on how many queries one page on your website sends each time it loads - think realistically, if your webpage sends 20 queries when it loads and a customer clicks 5 times in 1 second (out of frustration) then that's 100 queries.

Other Techniques
  • Stored Procedures (I personally feel these have to include variable validation otherwise these are vulnerable no matter what your webdeveloper says)

Amusing Examples
  • June 29, 2007 Official Microsoft UK website gets defaced using SQL-Injection.
  • April 13, 2008 10,597 social security numbers belonging to sex offenders had been downloaded via an SQL injection attack
  • April-August 2008 An estimated 500,000 webpages are exploited with a new attack. The attack does not require guessing the name of a table or column, and instead corrupts all text columns in all tables in a single request. A HTML string that references a malware JavaScript file is appended to each value. When that database value is later displayed to a website visitor, the script attempts several approaches at gaining control over a visitor's system.
  • August 17, 2009 A US citizen (along with 2 Russian associates) are charged with the theft of 130 million credit card numbers using an SQL injection attack.
  • March 27th 2011:, the official homepage for MySQL, was compromised using SQL blind injection.

Recommended Reads

Add comment



0 James C Tuesday, 19th July 2011, 12:12 pm
In this article the description is very clear and helpful. It describes everything clearly. I enjoyed the article very much. I also have read a article about this topic here "http://www.tec /sql-step-step- images" which is very helpful also.
0 Webo Wednesday, 1st June 2011, 7:46 pm
Thanks for this useful article.