Skip to content

SQL Injection.md

Latest commit

 

History

History
216 lines (133 loc) · 10.8 KB

SQL Injection.md

File metadata and controls

216 lines (133 loc) · 10.8 KB

SQL INJECTION

Manual SQLi Detection

  • The single quote character ' and look for errors or other anomalies.
  • Some SQL-specific syntax that evaluates to the base (original) value of the entry point, and to a different value, and look for systematic differences in the application responses.
  • Boolean conditions such as OR 1=1 and OR 1=2, and look for differences in the application's responses.
  • OAST payloads designed to trigger an out-of-band network interaction when executed within a SQL query, and monitor any resulting interactions.

SQL injection in different parts of the query

Most SQL injection vulnerabilities occur within the WHERE clause of a SELECT query. Some other common locations where SQL injection arises are:

  • In UPDATE statements, within the updated values or the WHERE clause.
  • In INSERT statements, within the inserted values.
  • In SELECT statements, within the table or column name.
  • In SELECT statements, within the ORDER BY clause.

Warning! Take care when injecting the condition OR 1=1 into a SQL query. Even if it appears to be harmless in the context you're injecting into, it's common for applications to use data from a single request in multiple different queries. If your condition reaches an UPDATE or DELETE statement, for example, it can result in an accidental loss of data.

SQLi for user login with Username and Password credentials

SQL Query includes: SELECT * FROM users WHERE username = 'wiener' AND password = 'bluecheese' Using the SQL comment sequence -- to remove the password check from the WHERE clause of the query: SELECT * FROM users WHERE username = 'administrator'--' AND password = ''

Types of SQLi

1. In-Band SQLi

  • A. Union-Based SQLi
  • B. Error-Based SQli

2. Inferential/Blind SQLi

  • A. Boolean-Based SQLi
  • B. Time-Based SQli

3. Out-of-Band SQLi

A. Union-Based SQLi

  • Effective when the SQL query results are returned to the application's response. It's useful when you need to combine the results of multiple SELECT statements into a single result set, which the application might display.
  • Used to extract data from the database such as retrieving usernames, passwords, or other sensitive information.
  • The UNION keyword enables you to execute one or more additional SELECT queries and append the results to the original query.
  • For example:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

For a UNION query to work, two key requirements must be met: - The individual queries must return the same number of columns. - The data types in each column must be compatible between the individual queries.

Meet these two requirements to carry out attack: - How many columns are being returned from the original query. - Which columns returned from the original query are of a suitable data type to hold the results from the injected query.

Determining the number of columns required

Two effective methods to determine how many columns are being returned from the original query.

  1. Injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, if the injection point is a quoted string within the WHERE clause of the original query, you would submit:

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
etc.

This series of payloads modifies the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you don't need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as:

The ORDER BY position number 3 is out of range of the number of items in the select list.

The application might actually return the database error in its HTTP response, but it may also issue a generic error response. In other cases, it may simply return no results at all. Either way, as long as you can detect some difference in the response, you can infer how many columns are being returned from the query.

  1. Second method involves submitting a series of UNION SELECT payloads specifying a different number of null values:

' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--
etc.

If the number of nulls does not match the number of columns, the database returns an error, such as:

  • All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

We use NULL as the values returned from the injected SELECT query because the data types in each column must be compatible between the original and the injected queries. NULL is convertible to every common data type, so it maximizes the chance that the payload will succeed when the column count is correct.

As with the ORDER BY technique, the application might actually return the database error in its HTTP response, but may return a generic error or simply return no results.
When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column. The effect on the HTTP response depends on the application's code. If you are lucky, you will see some additional content within the response, such as an extra row on an HTML table. Otherwise, the null values might trigger a different error, such as a NullPointerException. In the worst case, the response might look the same as a response caused by an incorrect number of nulls. This would make this method ineffective.

Database-specific syntax

On Oracle, every SELECT query must use the FROM keyword and specify a valid table. There is a built-in table on Oracle called dual which can be used for this purpose. So the injected queries on Oracle would need to look like:

' UNION SELECT NULL FROM DUAL--

The payloads described use the double-dash comment sequence -- to comment out the remainder of the original query following the injection point.

On MySQL, the double-dash sequence must be followed by a space. Alternatively, the hash character `#`` can be used to identify a comment.

For more details of database-specific syntax, see the SQL injection cheat sheet

Identifying Datatypes of Columns

The data that you want to retrieve is normally in string format. Try to find one or more columns in the original query results whose data type is, or is compatible with, string data.

After determining the number of required columns, probe each column to test whether it can hold string data by submitting a series of UNION SELECT payloads that places a string value into each column in turn.

For a query that returns four columns, you would submit:

' UNION SELECT 'a',NULL,NULL,NULL--
' UNION SELECT NULL,'a',NULL,NULL--
' UNION SELECT NULL,NULL,'a',NULL--
' UNION SELECT NULL,NULL,NULL,'a'--

If the column data type is not compatible with string data, the injected query will cause a database error, such as:

Conversion failed when converting the varchar value 'a' to data type int.

If an error does not occur, and the application's response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.

Using a SQL injection UNION attack to retrieve interesting data

After determining the number of columns and their datatypes, one can retrieve interesting data from DB.

Suppose that:

  • The original query returns two columns, both of which can hold string data.
  • The injection point is a quoted string within the WHERE clause.
  • The database contains a table called users with the columns username and password.

Retrieve the contents of the users table by submitting the input:

' UNION SELECT username,password FROM users--

NOTE: To perform the attack, first determine the table name users, in this case.

Retrieving multiple values within a single column

In some cases the query in the previous example may only return a single column.

You can retrieve multiple values together within this single column by concatenating the values together. You can include a separator to let you distinguish the combined values.

For example, on Oracle you could submit the input:

' UNION SELECT username || '=' || password FROM users--

The double-pipe sequence || is a string concatenation operator on Oracle.

Above injected query concatenates together the values of the username and password fields, separated by the = character.

String concatenation for different Databases

Concatenate together multiple strings to make a single string.

  • Oracle 'foo'||'bar'
  • Microsoft 'foo'+'bar'
  • PostgreSQL 'foo'||'bar'
  • MySQL 'foo' 'bar' [Note the space between the two strings] and CONCAT('foo','bar')

Determining and Examining the Database in SQL injection attacks

Must find following necessary information about Database to carry out SQL Injection attacks:

  • The type and version of the database software.
  • The tables and columns that the database contains.
Querying the database type and version

Following are some queries to determine the database version for some popular database types:

  • Microsoft, MySQL SELECT @@version
  • Oracle SELECT * FROM v$version
  • PostgreSQL SELECT version()

Combining above query with UNION attack:

' UNION SELECT @@version

Listing the contents of the database

Most database types (except Oracle) have a set of views called the information schema. This provides information about the database.

  • For example, you can query information_schema.tables to list the tables in the database:

SELECT * FROM information_schema.tables

Which returns the output like:

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  TABLE_TYPE  
===================================================  
MyDatabase     dbo           Products    BASE TABLE  
MyDatabase     dbo           Users       BASE TABLE  
MyDatabase     dbo           Feedback    BASE TABLE

This output indicates that there are three tables, called Products, Users, and Feedback.

  • You can then query information_schema.columns to list the columns in individual tables:

SELECT * FROM information_schema.columns WHERE table_name = 'Users'

Which returns the output like:

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  DATA_TYPE
=================================================================
MyDatabase     dbo           Users       UserId       int
MyDatabase     dbo           Users       Username     varchar
MyDatabase     dbo           Users       Password     varchar

This output shows the columns in the specified table and the data type of each column.