Intro
This article is created for completeness in this Blog as far as the Web Application Security is concerned and it is mainly focused in MS SQL injections.
What is SQL?
What is SQL?
SQL was originally developed at IBM in the early 1970s but was not officially formalized until 1986 by the American National Standards Institute (ANSI). SQL was initially designed as a data query and manipulation language with limited functionality when compared to today’s feature-rich SQL dialects.
SQL Microsoft SQL Server
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements. These additional features make Transact-SQL Turing complete.
Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.
What is SQL injection?
SQL injection is a technique to maliciously exploit applications that use client-supplied data in SQL statements. Attackers trick the SQL engine into executing unintended commands by supplying specially crafted string input, thereby gaining unauthorized access to a database in order to view or manipulate restricted data.
Where to look for SQL Injection
You should look for SQL injections practically and realistically speaking in all variables included in a Web Application. SQL injection is an attack in which SQL code is inserted or appended into application user input parameters (the web application might also populate variables automatically that feed back end database) that are later passed to a back-end SQL server for parsing and execution. Any procedure that constructs SQL statements could potentially be vulnerable,as the diverse nature of SQL and the methods available for constructing it provide a wealth of coding options.
The primary form of SQL injection consists of direct insertion of code into parameters that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL
command, the malicious code is executed.
Why SQL Injection Happens
When a Web application fails to properly sanitize the parameters which are passed to dynamically created SQL statements it is possible for an attacker to alter the construction of back-end SQL statements. When an attacker is able to modify an SQL statement, the statement will execute with the same rights as the application user.
SQL Injection Happens usually for two reasons:
- Dynamically generated SQL queries using concatination strings operators.
- Un-sanitized input to this SQL queries.
Note: When using the SQL server to execute commands that interact with the operating system, the process will run with the same permissions as the component that executed the command.
Types of SQL Injections
According to my experience there are three types of SQL injections:
- Error Based SQL injections (no input validation or output database error filtering).
- Semi Blind/Error Based SQL injections (minor or no input validation but output database error filtering).
- Blind SQL injections (strict both input and output filtering).
How you identify Error Based SQL Injections
You can identify an SQL injection by injecting the following five characters: ' , " , ) , ; , -- and all the combination of this five characters e.g );-- or '); -- e.t.c. If you inject one of this characters to a vulnerable variable then if the web application is not filtering the database SQL injection generated error more info is going to be revealed about the back end database.
Identify Number of Columns using the NULL data type
After successfully identifying a vulnerable variable the next best thing to do is to understand the structure of the select query. The structure of the SELECT query is revealed through the SQL verbose errors, so in order to find the structure we use the NULL character, because the NULL character can be casted into any data type each column of the abused SELECT query is. So by progressively increasing the amount of NULL characters eventually the query will execute as if there was a valid query (No database error will be returned ).
| MSSQL: | ‘ UNION SELECT NULL-- |
| MSSQL: | ‘ UNION SELECT NULL,NULL-- |
| MSSQL: | ‘ UNION SELECT NULL,NULL,NULL-- |
Poof -- no error comes back from SQL, the query was executed.
Note: You might also have to play with the comment characters at the end of the injected query some times.
Identify number of Columns using ORDER BY Clause (Transact-SQL)
In order to identify the name of the columns we use the ORDER BY Clause (Transact-SQL) in MSSQL. ORDER BY Clause specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
| Syntax: | [ ORDER BY { order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] } [ ,...n ] ] |
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in SQL Server (90) compatibility mode, the expression cannot resolve to a constant. Column names and aliases can be qualified by the table or view name.
In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.
The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list. Additionally, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.
COLLATE {collation_name}
Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name, and not according to the collation of the column as defined in the table or view. collation_name can be either a Windows collation name or a SQL collation name. For more information, see Collation Settings in Setup and Using SQL Server Collations. COLLATE is applicable only for columns of the char, varchar, nchar, and nvarchar data types.
ASC
Specifies that the values in the specified column should be sorted in ascending order, from lowest value to highest value. ASC is the default sort.
DESC
Specifies that the values in the specified column should be sorted in descending order, from highest value to lowest value.
Note1: ntext , text, image, or xmlcolumns cannot be used in an ORDER BY clause.
Note2: Null values are treated as the lowest possible values.
Note3: There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.
Note4: When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.
Extended malicious SELECT query using ORDER BY:
| MSSQL: | ' ORDER BY 1 -- |
| MSSQL: | ' ORDER BY 2 -- |
| MSSQL: | ' ORDER BY 3 -- |
We do that untill an error occures (just like the NULL queries) and that way you learn the number of columns.
Identify Type of Columns using version variable
Similar technique can be used with the version system variable:
| MSSQL: | ‘ UNION SELECT @@version,NULL,NULL-- |
| ORACLE: | ‘ UNION SELECT banner,NULL,NULL FROM v$version-- |
Note: that Oracle doesn’t support this schema. When targeting an Oracle database, the attack would be identical in every other way. However, you would use the query. When multiple columns are returned from a target table, these can be concatenated into a single column. This makes retrieval more straightforward,
because it requires identifi cation of only a single varchar field in the original query:
Identify Name of Columns using HAVING (Transact-SQL)
HAVING (Transact-SQL) specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
| Syntax: | [ HAVING | Arguments search_condition |
Specifies the search condition for the group or the aggregate to meet. The text, image, and ntext data types cannot be used in a HAVING clause.
Malicious queries using HAVING to identify columns:
| MSSQL: | ‘ HAVING 1=1-- |
| MSSQL: | ‘GROUP BY table.column_name1 HAVING 1=1 -- |
| MSSQL: | ‘GROUP BY table.column_name1, table.column_name2 HAVING 1=1 -- |
Note:Now when successfully enumerating all column names no error should be returned (meaning that the query should be successful).
Identify Data Type of Columns using different data types
The next step would be to identify the type of the data in each column. Lets say that based on our experience the query is possible to contain string type characters. So we "scan" each column with char 'a':
| MSSQL: | ‘ UNION SELECT ‘a’, NULL, NULL-- |
| MSSQL: | ‘ UNION SELECT NULL, ‘a’, NULL-- |
| MSSQL: | ‘ UNION SELECT NULL, NULL, ‘a’ -- |
Poof -- no casting error comes back from SQL.
Note: In Oracle databases, every SELECT statement must include a FROM attribute, so injecting UNION SELECT NULL produces an error regardless of the number of columns. You can satisfy this requirement by selecting from the globally accessible table DUAL. For example in Oracle you can inject:
| ORACLE: | ‘ UNION SELECT NULL FROM DUAL-- |
| ORACLE: | ‘ UNION SELECT NULL,NULL,'a' FROM DUAL-- |
| ORACLE: | ‘ UNION SELECT NULL,'a',NULL FROM DUAL-- |
Poof -- no casting error comes back from SQL.
Identify Data Type of Columns using SUM (Transact-SQL)
SUM (Transact-SQL) returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. May be followed by the OVER Clause (Transact-SQL).
| MSSQL: | ‘ UNION SELECT SUM(column_name1) FROM table -- |
| MSSQL: | ‘ UNION SELECT SUM(column_name2) FROM table -- |
| MSSQL: | ‘ UNION SELECT SUM(column_name3) FROM table -- |
Poof -- no casting error comes back from SQL.
Note: The SUM function attempts to perform a second query and combine the results with those of the original.
References:
