What is SQL Injection?

Go to main post

Definition of SQL Injection

SQL injection takes place when an application processes user-provided data to build an SQL statement without validating or conforming the input and submits the statement to a database server for execution.

When successfully utilized, SQL injection allows an attacker to access back-end database content, remotely execute system commands and in some circumstances, take control of the server that is hosting the database.

SQL injection can occur in any part of an application and it is not restricted to Web applications. Any application that creates a database query based on user input is potentially susceptible to SQL injection. SQL injection attacks are not restricted to applications running against a particular database server. Oracle, SQL Server etc. are all potentially susceptible.

Below you can read more about:
1. SQL Injection examples,
2. Goal of SQL Injection,
3. SQL Injection vulnerabilities,
4. Testing SQL Injection,
5. Types of SQL Injection attacks,
6. SQL Injection mitigation and more.

Example 1 of SQL Injection

An example would be that if the SQL query looks like this:

SELECT FieldList FROM Table WHERE field = ‘userclause’;

Now, if the clause supplied by the attacker is “myclause’” (notice the trailing single quote), the SQL query now looks like this:

Select FieldList FROM Table WHERE field = ‘myclause’ ‘;

Typically, this will result in a syntax error because the trailing single quote will confuse the SQL server. Now, what if we try to change the nature of the WHERE clause by injecting a clause designed to always be true, such as “anything’ or ‘z’=’z”. Our query then becomes:

SELECT FieldList FROM Table WHERE field = ‘anything’ or ‘z’=’z’;

This version, because the conditions will always be met, will return all rows in the table.

The attacker can continue on from there with some guesses at table names and even get to where, instead of just copying or stealing the information in the database, the attacker is writing data to the database, if that is not forbidden. [Source]

Example 2 of SQL Injection

The following script shows a simple SQL injection. The script builds an SQL query by concatenating hard-coded strings together with a string entered by the user:

var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable where ShipCity = '" + ShipCity + "'";

The user is prompted to enter the name of a city. If she enters Redmond, the query assembled by the script looks similar to the following:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond'

However, assume that the user enters the following:

Redmond'; drop table OrdersTable--

In this case, the following query is assembled by the script:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond';drop table OrdersTable--'

The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in OrdersTable where ShipCity is Redmond. Then, SQL Server will drop OrdersTable.

As long as injected SQL code is syntactically correct, tampering cannot be detected programmatically. [Source]

Here are few more examples on SQL Injection.

Goal of SQL Injection

The goal of an SQL injection attack is to attempt to manipulate queries or information sent to an SQL back end to gain control of the SQL server. This control can mean the ability to modify queries to get unauthorized information, but it can also go as far as modifying data on the server, invoking stored procedures, or even shutting down the SQL server, depending on the skill of the attacker and the level of control that can be gained.

Main goal of SQL Injection, in simple terms, is to bypass (get around) authorization and carry out data manipulation or read arbitrary data.

Successful SQL injection attacks can result in the attacker possibly being able to:

a. Run commands as the SQL Server user on the database server, using the xp_cmdshell extended stored procedure.

b. Run queries on linked servers.

c. Create new custom extended stored procedures to run explicit code with the SQL server process.

d. Use the “bulk insert” statement to read any file on the server.

e. Use bcp (Bulk Copy Program) to create arbitrary text files on the server etc. [Source]

SQL Injection Vulnerabilities

The attacker looks for vulnerability in a site or application before start of an SQL injection attack.

To test the vulnerability, the attacker often starts with what is the most common vulnerability: the single quote. In SQL, a single quote is the string literal delimiter. If user input is submitted directly to the SQL back end without sufficient validation or input “scrubbing,” it’s incredibly easy to gain control of the SQL server.

Unfortunately, error pages can reveal a lot of information about exactly what is happening behind the scenes. If you know what to look for, they’re a great tool to use when trying to diagnose and refine attack attempts.
The first thing to do is see just what error page is returned. If it’s an ODBC error page, the attacker knows right away that this is a true vulnerability because the error was generated from the SQL, which means the single quote inserted in the prior step was successful in passing from the front end to the SQL back end.

If an error page is returned that includes links, the attacker will search the links to see of they contain more details of the error. If a 302 Page Redirect is called, the attacker will try to capture the page it is redirected from; this may have more error details in it that can’t be easily read before the redirect occurs.
If a 500 Error Page is returned, the attacker knows that the injection may have been successful as this is the default error page for most Web servers. Though the attacker may not be able to determine much from this page, it will spur him on to greater attempts to exploit a possible weakness. [Source]

More on SQL Injection Vulnerabilities

1. SQL Injection Vulnerabilities Discovery
2. WordPress 2.3.1 SQL Injection Vulnerability
3. Vulnerabilities inside the database server
4. Understanding SQL Server Vulnerabilities
5. Testing For SQL Injection Vulnerabilities

Testing SQL Injection

Every parameter of every call must be tested separately to have a good picture of the Web service’s/site’s SQL injection vulnerabilities. Below is the list of Characters you need to test with:

Character Name	Unicode Value	Reason
Single quote/ Apostrophe (')	[U+0027]	SQL character used to escape strings. Remember to test all fields, not just string fields • Use alone • Use as the first character in otherwise valid input • Use in the middle of otherwise valid input • Use as the last character in a maximum length otherwise valid input• Use in places specific formatting is required after formatting requirements are met
Dash/hyphen(-)	[U+002D]	Use two as a SQL server single line comment indicator

For more on other Character Names and Reasons see here.

Black-Box Testing

For black-box testing, you need to use a browser and perform parameter tampering in any query or parameter you can locate on the site. The basic process is to replace the argument in every parameter on every script on the server with a test argument, one parameter at a time. Leave all the other parameters with valid data intact. Submit this parameter (or set of parameters) to the server, and then examine the returned page for the signs of possible vulnerabilities.

White-Box Testing

Determine if the Web site is performing any type of input validation. Strings tend to be more susceptible to parameter injection than other data types. One parameter at a time, replaces the argument of each parameter on each API, with each of the listed single-quote test scenario, and submits it. Then, examine the entire response from the server to see if you received an error and whether you can tell if it’s an SQL error.

Types of SQL Injection Attacks

1. Types of SQL Injection Attacks
2. SQL Injection Introduction, Categories of SQL Injection Attacks, SQL Injection Methods, What’s Vulnerable and What’s Not
3. Blind SQL Injection attacks explained
4. Some more SQL Injection Attack Types
5. SQL Injection Attacks And Their Ramifications
6. SQL Injection Attacks Explained

SQL Injection Mitigation

To start with, run all strings that come from users through a string checker looking for anything out of the ordinary. If you expect a string to contain only letters, numbers and spaces, then write a regular expression that verifies that and get in the habit of rejecting all input that doesn't conform. That should make it impossible for attackers to put special characters like quotation marks in there.

Assume that a clever attacker will subvert that. Assume that you've made a mistake and forgotten to put a check in somewhere. Look for every place in the code that uses that user-supplied string. Don't stop at SQL construction.
Anything that gets passed to JScript's eval could be an injection attack.
Anything that gets echoed back to the user could be a cross-site scripting attack. Anything that gets written to disk could be an attempt to write a script onto the server's disk to trick an admin into running it.
Eliminate as many of these as you can.

But how do you eliminate them? A great way to mitigate the risk of a SQL injection attack is to use stored procedures. Stored procedures ensure that only the query that you want to run actually runs. But they have nice properties in addition to being more secure against injection. They can be updated in the database, so that when the database structure changes, you change the stored procedure rather than searching through your code for SQL statement construction. And stored procedures often run faster because the database can optimize itself for them. [Source]

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:

SqlDataAdapter myCommand = 
        new SqlDataAdapter("LoginStoredProcedure '" + 
                                     Login.Text + "'", conn);

If you use stored procedures, you should use parameters as their input.[Source]

Wrapping Parameters with QUOTENAME() and REPLACE()
In each selected stored procedure, verify that all variables that are used in dynamic Transact-SQL are handled correctly. Data that comes from the input parameters of the stored procedure or that is read from a table should be wrapped in QUOTENAME() or REPLACE(). Remember that the value of @variable that is passed to QUOTENAME() is of sysname, and has a maximum length of 128 characters.

@variable	Recommended wrapper
Name of a securable	QUOTENAME(@variable)
String of <= 128 characters	QUOTENAME(@variable, '''')
String of > 128 characters	REPLACE(@variable,'''', '''''')

When you use this technique, a SET statement can be revised as follows:

--Before:

SET @temp = N'select * from authors where au_lname='''

+ @au_lname + N''''

--After:

SET @temp = N'select * from authors where au_lname='''

+ REPLACE(@au_lname,'''','''''') + N''''

Injection Enabled by Data Truncation

Any dynamic Transact-SQL that is assigned to a variable will be truncated if it is larger than the buffer allocated for that variable. An attacker who is able to force statement truncation by passing unexpectedly long strings to a stored procedure can manipulate the result. For example, the stored procedure that is created by the following script is vulnerable to injection enabled by truncation.

CREATE PROCEDURE sp_MySetPassword

@loginname sysname,

@old sysname,

@new sysname

AS

-- Declare variable.

-- Note that the buffer here is only 200 characters long.

DECLARE @command varchar(200)

-- Construct the dynamic Transact-SQL.

-- In the following statement, we need a total of 154 characters

-- to set the password of 'sa'.

-- 26 for UPDATE statement, 16 for WHERE clause, 4 for 'sa', and 2 for

-- quotation marks surrounded by QUOTENAME(@loginname):

-- 200 – 26 – 16 – 4 – 2 = 154.

-- But because @new is declared as a sysname, this variable can only hold

-- 128 characters.

-- We can overcome this by passing some single quotation marks in @new.

SET @command= 'update Users set password=' + QUOTENAME(@new, '''') + ' where 

username=' + QUOTENAME(@loginname, '''') + ' AND password = ' + QUOTENAME(@old, '''')

-- Execute the command.

EXEC (@command)

GO

By passing 154 characters into a 128 character buffer, an attacker can set a new password for sa without knowing the old password.

EXEC sp_MySetPassword 'sa', 'dummy', 

'123456789012345678901234567890123456789012345678901234567890123456789012345678
901234567890123456789012''''''''''

'''''''''''''''''''''''''''''''''''''''''

For this reason, you should use a large buffer for a command variable or directly execute the dynamic Transact-SQL inside the EXECUTE statement.

Securing your database will greatly mitigate SQL injection hacks. By partitioning access and restricting capabilities via standard hardening techniques (such as removing unnecessary procedures), you will greatly reduce (or completely negate) what can be done with SQL Injection. Beware, though don't forget to harden the web application code as well or you could find other vulnerabilities slipping through. [Source]

Some More Mitigation Techniques:

1. unixwiz
2. SQL Injection Mitigation: Using Parameterized Queries
3. SQL Injection Mitigation: Using Parameterized Queries Part 2

Also See:
Cookie Testing
Cross Site Scripting