An Over View Of SQL Inection

SQL Injection:

A SQL injection attack exploits vulnerabilities in input validation to run arbitrary commands in the database. Your code is vulnerable to SQL injection attacks wherever it uses input parameters to construct SQL statements. A SQL injection attack occurs when un-trusted input / user controllable input can modify the logic of a SQL query in unexpected ways. It can also occur if your code uses stored procedures that are passed strings that contain unfiltered/un-sanitized user input.

Impact:

SQL injections can result in unauthorized access, modification, or destruction of SQL data. Using the SQL injection attack, the attacker can execute arbitrary commands in the database. If an application is vulnerable to SQL injection and application uses an over-privileged account to connect to the database then it is possible to run operating system commands using database server and can potentially compromise other severs, in addition to being able to retrieve, manipulate, and destroy data exist in the databases.

Background:

Generally web applications allow legitimate users to submit and retrieve data to/from a database over the Internet using their browser/interface. This data may be anything user information, company statistics, product details, customer details, vendor details, payment information, financial information etc. These applications are vulnerable to SQL Injection if user controllable input is directly used in building the SQL statements without adequate validation to interact with the backend.

In Detail with few examples:

The simplest SQL injection technique is bypassing login forms. See the following web application code used in login form:

Query = "SELECT Uname FROM Users WHERE Uname = ‘" & txtUsername & "‘ AND Pwd = ‘" & txtPassword & "‘"

strCheck = GetDbaseResult(Query)
If (strCheck = "")
boolAuthenticated = False
else
boolAuthenticated = True

By the above logic the GetDbaseResult method will go through the Users table and will return the user name if there is a row exists with the username and password supplied by the user. This username is stored in the variable strCheck. If there is no row that the user supplied data exists in users table, strCheck will be empty and the user will not be authenticated.
In the above code you are constructing SQL statement dynamically using user input (Text box values of login form) without adequate validation or sanitization. This is vulnerable for SQL injection and this authentication logic can be easily bypassed by an attacker in following way.

Enters ‘ OR ‘‘=‘ in text box provided for user name and ‘ OR ‘‘=‘ in text box provided for password.

Now the final query that is going to be executed will be like below:

SELECT Uname FROM Users WHERE Uname = ‘‘ OR ‘‘=‘‘ AND Pwd = ‘‘ OR ‘‘=‘‘

The above statement is always returns true! Since all of the qualifying conditions in the WHERE clause are now met, this will return the username from the first row in the table that is searched. It will pass this username to strCheck, which will ensure our validation. Oops our authentication mechanism is bypassed!

Or just enter ‘ OR ‘a’ = ‘a’;-- in the text box provided for user name in login form to bypass authentication.
Or can create a login for himself by insering a row into the Users table with following statement.

'; insert into users values('Attacker', 'Password', ‘Admin’ )--

Direct Injection vulnerabilities:

In a direct injection, whatever argument you submit will be used in the SQL query without any modification. Direct values can be either numeric value used in WHERE statements, such as fallows…

Query = "SELECT CustNo, CustName, Location FROM Customers WHERE CustNo = " & txtCustNumber

…or the argument of an SQL keyword, such as table or column name:

Query = "SELECT CustNo, CustName, Location FROM Customers ORDER BY " & txtColumnName

Quoted Injection vulnerabilities:

In a quoted injection, whatever argument user submits has a quote prefixed and appended to it by the application, such as fallows...

Query = "SELECT CustNo, CustName, Location FROM Customers WHERE CustName = = ‘" & txtCustName & "‘"

In case of Quoted injection vulnerability attacker must use injection string that contains a single quote before an SQL keyword. Attacker can also use special symbols like “;--“ to comment out the rest of the logic. Everything after this “--“ will be treated as comments in SQL server, attacker uses appropriate symbols depending on the backend used for the application. Attacker can know these details by reverse-engineering several parts of the vulnerable web application’s SQL query from the returned error messages.

There are many ways to inject SQL code to get unintended results from the application. For Example attacker simply modifies a WHERE clause by injecting a UNION SELECT, to make the database server return records other than those intended. This allows multiple SELECT queries to be specified in one statement.

SELECT CustNo, CustName FROM Customers WHERE CustNo = -1 UNION ALL SELECT ProdCode, ProductName, Price FROM Products WHERE 1 = 1

The above statement will return the recordsets from the first query and the second query together. In the above the key word ALL is used to escape certain kinds of SELECT DISTINCT statements.
In the above the actual query within the code is like below:

Query = “SELECT CustNo, CustName FROM Customers WHERE CustNo = “ &txtCustNo

Simply attacker injected the following code to get the details of products:

1 UNION ALL SELECT ProdCode, ProductName, Price FROM Products WHERE 1 = 1

The above constructed query will not return any records from the first table since it won’t find a record with the customer number negative one(assuming that there will be no negative customer numbers), but will return all records from the second table which is injected into the actual query.
Most SQL compliant databases, including SQL Server, store metadata in a series of system tables with the names sysobjects, syscolumns, sysindexes, and so on. This means that an attacker could use these system tables to grab schema information for a database to assist him in the further compromise of the database. For example the following code might be used to reveal the names of the user tables in the database:

' UNION SELECT id, name, '', 0 FROM sysobjects WHERE xtype ='U' --

How attacker gathers information:

In order to manipulate the data in the database, the attacker will have to determine the structure of certain databases and tables. Well, how attacker will get this information?
If detailed error messages are returned from the application, the attacker can determine the entire structure of the database, and read any value that can be read by the account the application is using to connect to the Database Server.
Suppose the attacker wants to establish the names of the tables that the query operates on, and the names of the fields. For this, he uses the 'having' clause of the 'select' statement by injecting code into the select statement by entering malformed code into the text box or query string like below:

' having ‘a’= ‘a’-- Or
‘ having 1=1 --

The above statement returns an error saying Column 'XXXXXX' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. So now the attacker knows the first column name in the query. He can continue through the columns by introducing each field into a 'group by' clause, like below:

' group by XXXXX having 1=1-- (Here XXXXX is column name retrived in error message)

The above statement then throws an error message saying Column 'YYYYY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Now the attacker knows the second column name of the query and moves on. After knowing all columns he can determine the types of each column by using a 'type conversion' error message, with the help of aggregate functions like below:

' union select sum(XXXX) from TableName --

The above statement reveals data type of the column by returning error message like the sum or average aggregate operation cannot take a varchar data type as an argument, which tells him that the 'XXXX field has type 'varchar'. He can use this technique to approximately determine the type of any column of any table in the database. The attacker can take advantage of any error message that reveals information about the environment, or the database.

What attacker can do further?

Once an attacker has control of the database or compromised the database, he can use that access to obtain further control over the network. He can do anything like below but not limited to:

• Using the xp_cmdshell extended stored procedure to run operating system/arbitrary commands on the database server.
• Use the 'bulk insert' statement to read any file on the server.
• Can use the system stored procedures like sp_OACreate, sp_OAMethod and sp_OAGetProperty to create ActiveX applications.
• Using the xp_regread extended stored procedure to read registry keys, including the SAM (if Database Server Service is running as the local system account).
• Can create custom extended stored procedures to run exploit code on the database server from within the Database Server process.
• Can manipulate/drop the data, can retrieve sensitive information.
• Can stop or disable required services or can even shut down the server.

Recommendations:

1. Avoid usage of dynamic SQL; consider using parameters passed to stored procedures instead of dynamic SQL. Parameters passed to a stored procedure are generally much safer than using dynamic SQL.
2. Perform Input Validation on all inputs constraining the input in terms of length, character set, and format, so that application accepts only valid characters. Please refer to my post Assume all input is malicious until proven otherwise on input validation for more details.
3. Do not rely on client-side code to validate input, as it can be bypassed by attackers.
4. Use a white list approach to validate the inputs.
5. Along with input validation consider sanitizing the input that they do not contain dangerous codes as a defense in depth strategy.
6. Limit database permissions and segregate users.
7. Lockdown the server: Run Database server service on a least privileged account and use least privileged account to connect to the database from the application.
8. Isolate the web server.
9. Display only generic error messages to the users of the application, implement proper error handling and fail safely in case of any failure.

The above are the few methods to protect applications from SQL injections. Implement all or combination of few to defense against SQL injection but don’t depend only inadequate techniques like sanitizing/escaping few characters like single quotes these can be easily bypassed by the attackers like by using Char function to overcome the single code escape (Char(0x63)).
It is better to implement at least parameterized stored procedures instead of dynamic SQL along with the adequate input validation like regular expressions with a white list approach to validate input at server side to prevent SQL injections.

Conclusion:

I believe that web application developers often simply do not think about "surprise and malicious inputs", they generally focus only on application functionality rather than security, but attackers and security people do, so it is always a good practice to implement security best practices while developing and designing the applications to develop robust and secure applications.