SQL Injection Tutorial

Introduction

Nowadays, web applications are common around the world. Nearly every major company or organization has a web presence. Maximum of these companies and organizations use web applications to provide various services to users. Some of these web applications employ database driven content. The back-end database often contains confidential and sensitive information such security numbers, credit card number, financial data, medical data. Typically the web user supplies information, such as a username and password and web applications receive user request and interact with the back-end database and returned relevant data to the user.

What is SQL injection?

SQL injection is a technique (like other web attack mechanisms) to attack data driven applications. This attack can bypass a firewall and can affect a fully patched system. The attacker takes the advantage of poorly filtered or not correctly escaped characters embedded in SQL statements into parsing variable data from user input. The attacker injects arbitrary data, most often a database query, into a string that’s eventually executed by the database through a web application (e.g. a login form).

Through SQL Injection attacker can obtain unauthorized access to a database and can create, read, update, alter, or delete data stored in the back-end database. Currently, almost all SQL databases such as Oracle, MySQL, PostgreSQL, MSSQL Server, MS Access are potentially vulnerable to SQL injection attacks. In its most common form, a SQL injection attack gives access to sensitive information such as social security numbers, credit card number or other financial data.

Why SQL injection?

- Identify injectable parameters.
- Identify the database type and version.
- Discover database schema.
- Extracting data.
- Insert, modify or delete data.
- Denial of service to authorized users by locking or deleting tables.
- Bypassing authentication.
- Privilege escalation.
- Execute remote commands by calling stored functions within the DBMS which are reserved for administrators.

SQL injection method

Here are some methods through which SQL statements are injected into vulnerable systems
- Injected through user input.
- Injection through cookie fields contains attack strings.
- Injection through Server Variables.
- Second-Order Injection where hidden statements to be executed at another time by another function.

Vulnerabilities

In computer security, a vulnerability is a weakness which allows an attacker to reduce a system's information assurance. Web-based forms allow some access to the back-end database to allow entry of data and a response, this kind of attack bypasses firewalls and endpoint defenses. Any web form, even a simple logon form or search box (where a user can input data), might provide access to back-end database by means of SQL injection.

The common reason that an application is vulnerable to SQL injection is improper filtering and lack of validation for user input. Input forms are quite common to collect data from a user. So, practically it is not feasible to close all the entry points to bar SQL injection. To prevent attacks developers must apply proper filtration/validation on all forms.

Vulnerable Applications

– Almost all SQL databases are potentially vulnerable such as MS SQL Server, DB2, Oracle, PostgreSQL, MySQL, MS Access, Sybase, Informix, etc
– Accessed through applications using :
   ASP, JSP, PHP
   Perl and CGI scripts that access databases
   XML, XSL and XSQL
   JavaScript
   Database specific web applications
   Many other

Web Server Technology

Web servers are computers that deliver Web pages. Every Web server has an IP address and possibly a domain name. For example, if you enter the URL https://www.w3resource.com/index.php in your browser, this sends a request to the Web server whose domain name is w3resource.com. The server then fetches the page named index.php and sends it to your browser. The Web server stores all of the files necessary to display Web pages on the computer.

While passing data to server side form a web page, often GET method is used to append user supplied data at the end of the URL (e.g. example.com?q=userid&p=password). For an attacker, this opens an opportunity to obtain the values passed in this fashion and do some malicious stuff.

Create and run a simple login form :

Here is a simple login form (form.html) which can authenticate a user id and password.

form.html

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"> 
<title>SQL Injection form error example</title> 
<meta name="description" content="Twitter Bootstrap Version2.0 form error example from w3resource.com."> <!--<link href="http://twitter-bootstrap-v2/docs/assets/css/bootstrap.css" rel="stylesheet">--><style type="text/css">body{margin: 30px}:invalid { border-color: #e88;-webkit-box-shadow: 0 0 5px rgba(255, 0, 0, .8);-moz-box-shadow: 0 0 5px rbba(255, 0, 0, .8);-o-box-shadow: 0 0 5px rbba(255, 0, 0, .8);-ms-box-shadow: 0 0 5px rbba(255, 0, 0, .8);box-shadow:0 0 5px rgba(255, 0, 0, .8);}:required {border-color: #88a;-webkit-box-shadow: 0 0 5px rgba(0, 0, 255, .5);-moz-box-shadow: 0 0 5px rgba(0, 0, 255, .5);-o-box-shadow: 0 0 5px rgba(0, 0, 255, .5);-ms-box-shadow: 0 0 5px rgba(0, 0, 255, .5);box-shadow: 0 0 5px rgba(0, 0, 255, .5);}form {width:300px;margin: 20px auto;}input {font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;border:1px solid #ccc;font-size:20px;width:300px;min-height:30px;display:block;margin-bottom:15px;margin-top:5px;outline: none;-webkit-border-radius:5px;-moz-border-radius:5px;-o-border-radius:5px;-ms-border-radius:5px;border-radius:5px;}input[type=submit] {background:#066923;padding:10px;color: #fff;}</style>
</head>
<body>
<form action="action.php" method="POST"><label>User ID:</label><input type="text" id="uid" name="uid" placeholder="User ID" required><label>Password:</label><input type="password" id="passid" name="passid" required><input type="submit" value="Submit" />
</form>
</body>
</html>

When this form is submitted, the username and password are passed to action.php script. A user will be authenticated by providing correct user id and password. The log in process is done by building a SQL query and comparing the user id and password to the login records in the database.

action.php

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>SQL Injection form error example</title>
 <meta name="description" content="Twitter Bootstrap Version2.0 form error example from w3resource.com."> <link href="http://localhost/twitter-bootstrap/twitter-bootstrap-v2/docs/assets/css/bootstrap.css" rel="stylesheet">
 </head>
 <body style="margin-top: 50px">
 <div class="container">
 <div class="row">
 <div class="span6">
 <?php$host="localhost";
 $username="root";$password="
 ";$db_name="hr";$con=mySQL_connect("$host",
 "$username",
 "$password")or 
die("cannot connect"); 
mySQL_select_db("$db_name")or
 die("cannot select DB");
$uid = $_POST['uid'];
$pid = $_POST['passid'];
$SQL = "select * from user_details where userid = '$uid' 
and password = '$pid' ";
$result = mySQL_query($SQL);
if(mySQL_num_rows($result)>0)
{echo "<h4>".
"-- Personal Information -- ".
$row[3]."</h4>",
"</br>";
while ($row=mySQL_fetch_row($result)){echo "
<p>".
"User ID : ".
$row[0]."
</p>";
echo "<p>".
"Password : ".
$row[1]."</p>";
echo "<p>".
"First Name : ".
$row[2]." Last Name : ".
$row[3]."</p>";
echo "<p>".
"Gender : ".
$row[4]." 
Date of Birth :".
$row[5]."</p>
";echo "
<p>".
"Country : ".
$row[6]." 
User rating : ".$row[7].
"</p>
";
echo "<p>
"."Email ID : ".
$row[8].
"</p>
";
echo "--------------------------------------------";}}elseecho "
Invalid user id or password";?
>
</div>
</div>
</div>
</body>
</html>

Structure of the table: user_details

Records of the table : user_details

If the userid and password match a record in the database, the personal information of the user will be displayed. Otherwise, "Invalid user id or password" will be displayed.

Now input a valid userid and password in form.html and check the result :

Output:

-- Personal Information --

User ID: scott123

Password : 123@sco

First Name : Scott Last Name: Rayy

Gender : M Date of Birth :1990-05-15

Country : USA User rating : 100

Email ID : [email protected]

Let input a valid userid and an invalid password in form.html and check the result:

Output:

Invalid user name or password

When a genuine user submits his details (user id and password), an SQL query is generated from these details and submitted to the database for verification. If both user id and password combination are valid, the user is allowed access. On verification, a genuine user is granted appropriate access and display his or her details otherwise it generated an error message.

Example of a SQL injection attack :

Let’s look at the code (action.php) of the above example :

$uid = $_POST['uid'];$pid = $_POST['passid'];$SQL = "select * from user_details where userid = '$uid' and password = '$pid' "; $result = mySQL_query($SQL); 

In the above code userid and password data which are received from an user are stored in $uid and $pid. The interpreter will execute the command (which will be stored into $result) based on the inputs. Now if an attacker provides abcd as userid and anything' or 'x'='x as password, then the query will be constructed as

$SQL = "select * from user_details where userid = 'abcd' and password = 'anything' or 'x'='x' ";

Based on operator precedence, the WHERE clause is true for every row, therefore the query will return all records. In this way, an attacker will be able to view all the personal information of the users. See the following login form and the output.

Output:

-- Personal Information --

User ID : scott123

Password : 123@sco

First Name : Scott Last Name : Rayy

Gender : M Date of Birth :1990-05-15

Country : USA User rating : 100

Email ID : [email protected]

User ID : ferp6734

Password : dloeiu@&3

First Name : Palash Last Name : Ghosh

Gender : M Date of Birth :1987-07-05

Country : INDIA User rating : 75

Email ID : [email protected]

User ID : diana094

Password : ku$j@23

First Name : Diana Last Name : Lorentz

Gender : F Date of Birth :1988-09-22

Country : Germany User rating : 88

Email ID : [email protected]

Types of SQL injection

-- Tautology-based SQL Injection

-- Piggy-backed Queries / Statement Injection

-- Union Query

-- Illegal/Logically Incorrect Queries

-- Inference

-- Stored Procedure Injection

Tautologies

Purpose :

• Identify injectable parameters
• Bypass authentication
• Extract data

In logic, a tautology (from the Greek word ταυτολογία) is a formula which is true in every possible interpretation. In a tautology-based attack, the code is injected using the conditional OR operator such that the query always evaluates to TRUE. Tautology-based SQL injection attacks are usually bypass user authentication and extract data by inserting a tautology in the WHERE clause of a SQL query. The query transform the original condition into a tautology, causes all the rows in the database table are open to an unauthorized user. A typical SQL tautology has the form "or <comparison expression>", where the comparison expression uses one or more relational operators to compare operands and generate an always true condition. If an unauthorized user input user id as abcd and password as anything' or 'x'='x then the resulting query will be:

select * from user_details where userid = 'abcd' and password = 'anything' or 'x'='x'

The example is already explained in "Example of a SQL injection attack".

Piggy-backed Queries / Statement Injection

Purpose :

• Extract data
• Modify dataset
• Execute remote commands
• Denial of service

This type of attack is different than others because the hacker injects additional queries to the original query, as a result the database receives multiple SQL queries. The first query is valid and executed normally, the subsequent queries are the injected queries, which are executed in addition to the first. Due to misconfiguration, a system is vulnerable to piggy-backed queries and allows multiple statements in one query. Let an attacker inputs abcd as usrerid and '; drop table xyz -- as password in the login form :

Then the application will generate the following query :

select * from user_details where userid = 'abcd' and password = ''; drop table xyz -- '

After completing the first query ( returned an empty result set (i.e. zero rows)), the database would recognize the query delimiter(";") and execute the injected second query. The result of executing the second query would be to drop table xyz, which would destroy valuable information.

Union Query

Purpose:

• Bypassing authentication
• Extract data

This type of attack can be done by inserting a UNION query into a vulnerable parameter which returns a dataset that is the union of the result of the original first query and the results of the injected query.

The SQL UNION operator combines the results of two or more queries and makes a result set which includes fetched rows from the participating queries in the UNION.

Basic rules for combining two or more queries using UNION:

1) A number of columns and order of columns of all queries must be same.

2) The data types of the columns on involving table in each query should be same or compatible.

3) Usually returned column names are taken from the first query.

By default the UNION behaves like UNION [DISTINCT] , i.e. eliminated the duplicate rows; however, using ALL keyword with UNION returns all rows, including duplicates.

Structure of the table : user_details

Records of the table : user_details

Structure of the table : emp_details

Records of the table: emp_details

Suppose the attacker enters ' UNION SELECT * FROM emp_details -- in User ID field and abcd in Password filed as userid and password which generates the following query :

SELECT * FROM user_details WHERE userid ='' UNION SELECT * FROM EMP_DETAILS -- ' and password =  'abcd'

The two dashes (--) comments out the rest of the query i.e. ' and password = 'abcd'. Therefore, the query becomes the union of two SELECT queries. The first SELECT query returns a null set because there is no matching record in the table user_details. The second query returns all the data from the table emp_details. Let try it with our login form.

Output :

-- Personal Information --

User ID : 100

Password : Steven

First Name : King Last Name : SKING

Gender : 515.123.4567 Date of Birth :1987-06-17

Country : AD_PRES User rating : 24000.00

Email ID : 0.00

User ID : 101

Password : Neena

First Name : Kochhar Last Name : NKOCHHAR

Gender : 515.123.4568 Date of Birth :1987-06-18

Country : AD_VP User rating : 17000.00

Email ID : 0.00

User ID : 102

Password : Lex

First Name : De Haan Last Name : LDEHAAN

Gender : 515.123.4569 Date of Birth :1987-06-19

Country : AD_VP User rating : 17000.00

Email ID : 0.00

User ID : 103

Password : Alexander

First Name : Hunold Last Name : AHUNOLD

Gender : 590.423.4567 Date of Birth :1987-06-20

Country : IT_PROG User rating : 9000.00

Email ID : 0.00

User ID : 104

Password : Bruce

First Name : Ernst Last Name : BERNST

Gender : 590.423.4568 Date of Birth :1987-06-21

Country : IT_PROG User rating : 6000.00

Email ID : 0.00

User ID : 105

Password : David

First Name : Austin Last Name : DAUSTIN

Gender : 590.423.4569 Date of Birth :1987-06-22

Country : IT_PROG User rating : 4800.00

Email ID : 0.00

The headings (e.g. User ID :, First Name : ect) were previously declared in action.php, but the output shows that the result contains employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commision_pct from the table emp_details.

Illegal/Logically Incorrect Queries

Purpose :

• Identify injectable parameters
• Identify database
• Extract data

In this type of injection an attacker is trying gather information about the type and structure of the back-end database of a Web application. The attack is considered as a preliminary step for further attacks. If an incorrect query is sent to a database, some application servers return the default error message and the attacker takes the advantage of this weakness. They inject code in vulnerable or injectable parameters which creates syntax, type conversion, or logical error. Through type error, one can identify the data types of certain columns. Logical error often exposes the names of tables and columns.

Inference

Purpose :

• Identify injectable parameters
• Identify schema
• Extract data

In this type of injection, the attack is applied on well-secured databases which do not return any usable feedback or descriptive error messages. The attack is normally created in the style of the true false statement. After finding the vulnerable parameter, the attacker injects various conditions (that he wants to know whether they are true or false) through query and carefully observe the situation. If statement evaluates to true, the page continues to function normally. If false, the page behaves significantly different from the normally functioning. This type of injection is called Blind Injection. There is another type of inference attack which is called Time Attack. In this method, an attacker designs a conditional statement and inject through the vulnerable parameter and gather information based on time delays in the response of the database. See the following code:

http://www.example.com/product.php?product_id=100 AND IF(version() like ‘5%’, sleep(15), ‘false’))-- 

Here an attacker checks whether the system is using a MySQL version is 5.x or not, making the server delay the answer in 15 seconds (the attacker can increase the delay's time).

Stored Procedures

Purpose :

• Privilege escalation
• Denial of service
• Execute remote commands

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary. Typical use for stored procedures includes data validation  or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.
Stored procedures type of SQL injection tries to execute stored procedures present in the database. Most of the database have a standard set of procedures (apart from user defined procedures) that extend the functionality of the database and allow for interaction with the operating system. The attacker initially tries to find the database type with another injection method like illegal/logically incorrect queries. Once an attacker determines which databases is used in backend then he try to execute various procedures through injected code. As the stored procedure are written by developers, therefore these procedures do not make the database vulnerable to SQL injection attacks. Stored procedures can be vulnerable to execute remote commands, privilege escalation, buffer overflows, and even provide administrative access to the operating system.
If an attacker injects ';SHUTDOWN; -- into either the User ID or Password fields then it will generate the following SQL code :

select * from user_details where userid = 'abcd' and password = ''; SHUTDOWN; -- '

The above command causes a database to shut down.

Alternate Encodings

Purpose:

• Evade Detection

In this case, the attacker injected encoded text to bypass defensive coding practices. Attackers have arranged alternate methods of encoding through their injected strings such as using hexadecimal, ASCII,
and Unicode character encoding. Scanning and detection techniques are not fully effective against alternate encodings. See the following example :

SELECT * FROM users WHERE login= '' AND pass=' ';exec(char(Ox73687574646j776e)) '

In the above code the char() function and ASCII hexadecimal encoding have used. The char() function returns the actual character(s) of hexadecimal encoding of character(s). This encoded string is translated into the shutdown command by the database when it is executed.

Defending against SQL Injection

Researchers and security managers have proposed various defensive methods to fight against SQL injection attack. The root cause of almost every SQL injection is invalid input checking. Here is a list of prevention methods :

• Input Validation
• Input Checking Functions
• Validate Input Sources
• Access Rights
• Configure database error reporting

Input Validation

– Simple input check can prevent many attacks.
– Always validate user input by checking type, size, length, format, and range.
– Test the content of string variables and accept only expected values.
– Reject entries that contain binary data, escape sequences etc. This can help prevent script injection and can protect against some buffer overrun exploits.
– When you are working with XML documents, validate all data against its schema as it is entered.

Input Checking Functions

– Certain characters and character sequences such as ; , --, select, insert and xp_ can be used to perform an SQL injection attack.
– Remove these characters and character sequences from user input which reduces the chance of an injection attack.
– Scan query string for undesirable word like "insert", "update", "delete", "drop" etc. check whether it represent a statement or valid user input.
– Write a function which can handle all of this.

List of the characters which are used to perform an SQL injection attack :

Validate Input Sources

– There are so many ways to attack a database, therefore the developer should check and authenticate all input sources and disallow unidentified or untrusted users/websites.

Access Rights/User Permissions

– Create "low privileged" accounts for use by applications.
– Never grant instance­level privileges to database  accounts.
– Never grant database­owner or schema­owner privileges  to database accounts.
– Be aware of the permission scheme of your database.

Configure database error reporting

– Some application server's default error reporting often gives away information that is valuable for attackers (table name, field name, etc.).
– The developer should configure the system correctly, therefore this information will never expose to an unauthorized user.

Apart from the above, there are several methods which can prevent from SQL injection.

Reference : http://en.wikipedia.org/wiki/SQL_injection

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL Dual table
Next: SQL Question Answer