SQLi is one of the most common and severe vulnerabilities. Learn what you as a developer can do to prevent SQL injection attacks on your application.

Join the DZone community and get the full member experience.
Join For Free

A Brief SQL Injection History Lesson

In the early days of the internet, building websites was straightforward: no JavaScript, no CSS, and few images. But as the web gained popularity, the need for more advanced technology and dynamic websites grew. This led to the development of CGI and server-side scripting languages like ASP, JSP, and PHP.

Websites changed and started storing user input and site content in databases. It is therefore of no surprise that every popular server-side scripting language added support for SQL databases. However, as with almost every technical advance, hackers discovered new attack vectors, and for as long as relational databases have been used in web applications, so too have SQL Injection attack vectors.

The SQL injection vulnerability is one of the most dangerous issues for data confidentiality and integrity in web applications and has been listed in the OWASP Top 10 list of the most common and widely exploited vulnerabilities since its inception. Read the history of the SQL injection vulnerability for a more detailed explanation of how the SQL Injection vulnerability originated.

You can also refer to the SQL Injection Cheat Sheet for detailed technical information about the many different variants of the SQL Injection vulnerability.

What Is a SQL Injection Vulnerability?

Within this blog post, we are trying to shed light on the technical aspects of SQL injections and what you can do to effectively avoid them.

Non-Technical Explanation of the SQL Injection Vulnerability

Imagine a fully-automated bus that functions based on instructions given by humans through a standard web form. That form might look like this:

Sample Populated Form

Drive through route 66 and stop at bus stops if there are people at the bus stops.

Values in bold are provided by humans and instruct the bus. Imagine a scenario where someone manages to send these instructions:

Drive through route 66 and do not stop at bus stops and ignore the rest of this
form. if there are people at the bus stops.

The bus is fully-automated. It does exactly as instructed: it drives up route 66 and does not stop at any bus stop, even when there are people waiting. Such an injection is possible because the query structure and the supplied data are not separated correctly. The automated bus does not differentiate between instructions and data; it simply parses anything it is fed.

SQL injection vulnerabilities are based on the same concept. Attackers are able to inject malicious instructions into benign ones, all of which are then sent to the database server through a web application.

Technical Explanation of SQL Injection Vulnerability

As the name suggests, a SQL injection vulnerability allows an attacker to inject malicious input into an SQL statement. To fully understand the issue, we first have to understand how server-side scripting languages handle SQL queries.

For example, let's say functionality in the web application generates a string with the following SQL statement:

This SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns a result.

As you might have noticed the statement contains some new, special characters:

Now consider the following example in which a website user is able to change the values of '$user' and '$password,' such as in a login form:$statement = 'SELECT * FROM users WHERE username = '$user' AND password

= '$password';

An attacker can easily insert any special SQL syntax inside the statement if the input is not sanitized by the application:$statement = 'SELECT * FROM users WHERE username = 'admin'; -- ' AND
password = 'anything';


= 'anything';

What is happening here? The admin'; -- part is the attacker's input, which contains two new, special characters:

This SQL injection effectively removes the password verification, and returns a dataset for an existing user - 'admin' in this case. The attacker can now log in with an administrator account, without having to specify a password.

The Different Types of SQL Injection Vulnerability

Attackers can exfiltrate data from servers by exploiting SQL Injection vulnerabilities in various ways. Common methods include retrieving data based on errors, conditions (true/false), and timing. Let's look at the variants.

Error-Based SQL Injection

When exploiting an error-based SQL Injection vulnerability, attackers can retrieve information such as table names and content from visible database errors.

Error-Based SQL Injection Example

This Request Returned an ErrorDuplicate entry 'database1' for key 'group_key'

Types Of Sql Injections Attacks

The same method works for table names and content. Disabling error messages on production systems helps to prevent attackers from gathering such information.

Boolean-Based SQL Injection

Sometimes there is no visible error message on the page when a SQL query fails, making it difficult for an attacker to get information from the vulnerable application. However, there is still a way to extract information.

When a SQL query fails, sometimes some parts of the web page disappear or change, or the entire website can fail to load. These indications allow attackers to determine whether the input parameter is vulnerable and whether it allows extraction of data.

Attackers can test for this by inserting a condition into a SQL query:https://example.com/index.php?id=1+AND+1=1

If the page loads as usual, it might indicate that it is vulnerable to a SQL Injection. To be sure, an attacker typically tries to provoke a false result using something like this:https://example.com/index.php?id=1+AND+1=2

Since the condition is false, if no result is returned or the page does not work as usual (missing text or a white page is displayed, for example), it might indicate that the page is vulnerable to a SQL injection.

Here is an example of how to extract data in this way:https://example.com/index.php?id=1+AND+IF(version()+LIKE+'5%',true,false)

With this request, the page should load as usual if the database version is 5.X. But, it will behave differently (display an empty page, for example) if the version is different, indicating whether it is vulnerable to a SQL injection.

Time-Based SQL Injection

In some cases, even though a vulnerable SQL query does not have any visible effect on the output of the page, it may still be possible to extract information from an underlying database.

Hackers determine this by instructing the database to wait (sleep) a stated amount of time before responding. If the page is not vulnerable, it will load quickly; if it is vulnerable it will take longer than usual to load. This enables hackers to extract data, even though there are no visible changes on the page. The SQL syntax can be similar to the one used in the Boolean-Based SQL Injection Vulnerability.

But to set a measurable sleep time, the 'true' function is changed to something that takes some time to execute, such as 'sleep(3)' which instructs the database to sleep for three seconds:https://example.com/index.php?id=1+AND+IF(version()+LIKE+'5%',sleep(3),false)

Types of sql injection

If the page takes longer than usual to load it is safe to assume that the database version is 5.X.

Out-of-Band SQL Injection Vulnerability

Sometimes the only way an attacker can retrieve information from a database is to use out-of-band techniques. Usually, these types of attacks involve sending the data directly from the database server to a machine that is controlled by the attacker. Attackers may use this method if an injection does not occur directly after the supplied data is inserted, but at a later point in time.

Out-of-Band Example

In these requests, the target makes a DNS request to the attacker-owned domain, with the query result inside the subdomain. This means that an attacker does not need to see the result of the injection, but can wait until the database server sends a request instead.

Impacts of SQL Injection Vulnerability

There are a number of things an attacker can do when exploiting a SQL injection on a vulnerable website. Usually, it depends on the privileges of the user the web application uses to connect to the database server. By exploiting a SQL injection vulnerability, an attacker can:

It all depends on the capabilities of the attacker, but the exploitation of a SQL injection vulnerability can even lead to a complete takeover of the database and web server. You can learn more useful tips on how to test the impact of an SQL injection vulnerability on your website by referring to the SQL injection cheat sheet.

A good way to prevent damage is to restrict access as much as possible (for example, do not connect to the database using the sa or root account). It is also sensible to have different databases for different purposes (for example, separating the database for the shop system and the support forum of your website).

Preventing SQL Injection Vulnerabilities

Server-side scripting languages are not able to determine whether the SQL query string is malformed. All they can do is send a string to the database server and wait for the interpreted response.

Surely, there must be a way to simply sanitize user input and ensure a SQL injection is infeasible. Unfortunately, that is not always the case. There are perhaps an infinite number of ways to sanitize user input, from globally applying PHP's addslashes()to everything (which may yield undesirable results), all the way down to applying the sanitization to 'clean' variables at the time of assembling the SQL query itself, such as wrapping the above $_GET['id']in PHP's mysql_escape_string()function. However, applying sanitization at the query itself is a very poor coding practice and difficult to maintain or keep track of. This is where database systems have employed the use of prepared statements.

Using Prepared Statements as SQL Injection Prevention

When you think of prepared statements, think of how printf works and how it formats strings. Literally, you assemble your string with placeholders for the data to be inserted, and apply the data in the same sequence as the placeholders. SQL prepared statements operate on a very similar concept, where, instead of directly assembling your query string and executing it, you store a prepared statement, feed it with the data, and it assembles and sanitizes it for you upon execution. Great! Now there should never be another SQL injection again. So why, then, are SQL injection attacks still constantly one of the biggest and most prevalent attack methods?

Insecure SQL Queries Are a Problem

Simply put, it perhaps boils down to web application developer laziness and lack of education and awareness. Insecure SQL queries are so extremely easy to create, and secure SQL queries are still mildly complex (or at least more complex than generic and typical in-line and often insecure queries). In the examples above, a malicious hacker can inject anything he or she desires in the same line as the SQL query itself.

Example and Explanation of a SQL Prepared Statement

However, with prepared statements, there are multiple steps. No major database system operates like printf (with everything occurring within the same statement on the same line). MySQL, directly, requires at least two commands (one PREPARE and one EXECUTE). PHP, via the PDO library, also requires a similar stacking approach, such as the following:$stmt = $dbh->prepare('SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?');


$stmt->execute(array($username, $password));

At first glance, this is not inherently problematic and, on average, increases each SQL query by only an extra line or two. However, as this requires extra caution and effort on behalf of already tired and taxed developers, often times they may get a little lazy and cut corners, opting, instead, to just use the easy procedural mysql_query() as opposed to the more advanced object-oriented PDO prepare().

Besides this, many developers just stick with what they know to get the job done and they generally learn the easiest and most straightforward way to execute SQL queries rather than showing genuine interest in improving what they know. But this could also be an issue of lack of awareness.

Deeper Into the Rabbit Hole of SQL Injection Security

Say, however, this isn't a case of lazy developers or even a lack of prepared statements -- or, more precisely, say the software itself and its security is out of your hands. Perhaps it is impractical or infeasible to completely secure the SQL queries in the code you use (by one comparison, Drupal has had over 20,000 lines of code committed, WordPress has had over 60,000 lines, and Joomla! has had over 180,000 lines), or, it may simply be impossible because it is encoded, etc., etc. Whatever the case is if you do not have control over the code you may need to employ different, more advanced 'outside the box' protections.

Non-Development Related SQL Injection Protection

Running Updated Software

First and foremost, always ensure you are running the most up-to-date software you can. If you are using WordPress or any other CMS framework, keep it updated! The same goes for PHP, your web server software such as Apache and nginx, and your database server (MySQL, Postgres, or others). The more recent the version of your software is the less chance of having a vulnerability or at least a widely-known one. This also extends down to your other software as well, such as SSH, OpenSSL, Postfix, and even the operating system itself.

Block URLs at the Web Server Level

Next, you should employ methods to ensure you are as minimally vulnerable to potential SQL injection attacks as possible. You could perhaps go for a quick and easy match against common SQL query keywords in URLs and just simply block them. For example, if you run Apache as your web server, you could use the following two mod_rewritelines in your VirtualHost directive, as explained below:

This is indeed quite clever, but it does not protect against everything. SQL injection parameters can still be passed via POST values or other RESTful-type URLs, not to mention there are tons of different ways to bypass this kind of generic blacklisting.

Securing the Database and Privileges

You can also ensure your database itself is as secure as possible. In the information security field, there exists a concept known as the principle of least privilege. Effectively, this principle states that a user or program should have only the absolute very least amount of privileges necessary to complete its tasks. We already do this practically every day with Linux file permissions, so the concept is in no way foreign, and is equally applicable to databases. There is probably no reason why your logging functionality should have anything beyond INSERT privileges, so you should not simply GRANT ALL PRIVILEGES because it is easier.

Segregating Sensitive and Confidential Data

Similarly, you might consider separation of data as a defense in depth approach, rather than conglomerating it into a single source. When you step back and think about it, it is probably not a very wise idea to keep your (hopefully PCI-compliant) customer credit card data stored in the same database as your forums, which are running an outdated and highly vulnerable version of phpBB, right? Not only would the principle of least privilege be very applicable in this situation, but even going so far as to entirely separate out your more sensitive data is a very sage approach. To think about it another way, would you keep all your most important paperwork inside your house, or would you keep some in a safety deposit box, too? The same concept applies to sensitive data.

Analyzing HTTP Requests Before Hitting the Web Application

Another option is the use of more detailed firewall systems. Typically this might include some adaptive solution that rides on top of iptables or ipfw (depending on whether you are using Linux or a BSD variant, respectively), or perhaps a reactive Host Intrusion Detection System (HIDS) such as OSSEC, although these are often more complicated than desired and not exactly purpose-built for these uses. Instead, you may wish to utilize a Web Application Firewall, which is designed specifically for these tasks. While there exist several enterprise-level solutions that are both a WAF and database firewall (sitting between your web application and your database), there are many open-source solutions, such as ModSecurity and IronBee, that perform remarkably well.

The Truth About SQL Injection Web Vulnerabilities

Even though we have just provided examples of how to prevent the exploitation of SQL injection vulnerabilities, there is no magic wand.

PHP, however, is attempting a new, aggressive approach. Since PHP 5.5, procedural MySQL has been deprecated and will soon be removed entirely. This means that future software projects will need to be switched to either MySQLi or PDO MySQL in order to continue to work. This is a positive development since it forces developers into a system that handles prepared statements with relative ease - though it still requires stacking a few operations. However, since many developers adopt a 'code golf' style (attempting to code in as few lines or characters as possible), many unfortunately will still opt for a single-line straight query over a two-line prepared statement.

There are other options that can account for development shortcomings, including but not limited to: privilege limitations, data separation, web application firewalls, and many other approaches. But until these options are employed as consistently as SQL injection attacks, it may never be the case that injection-style attacks escape OWASP's Top 10 list.

Be the change that is needed to ensure data and web application security, and keep your databases safe from SQL injections!

Vulnerability Classification and Severity Table

ClassificationID/Severity
PCI v3.16.5.1
PCI v3.26.5.1
OWASP 2013A1
CWE89
CAPEC66
WASC19
HIPAA164.306(a), 164.308(a)
CVSS 3.0 Score
Base 10 (Critical)
Temporal 10 (Critical)
Environmental 10 (Critical)
CVSS Vector String
CVSS:3.0/AV:N/AC:L/PR:N/UI:N/S:C/C:H/I:H/A:H

Like This Article? Read More From DZone

sql injection vulnerability ,security ,sql injection ,web application security
Published at DZone with permission of Sven Morgenroth , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Join hundreds of InfoSec professionals at our upcoming
[Global AppSec DC, September 9-13] and [Global AppSec Amsterdam, September 23-27]
(Redirected from SQL injection)
This is an Attack. To view all attacks, please see the Attack Category page.

Last revision (mm/dd/yy): 04/10/2016

Overview

A SQL injection attack consists of insertion or 'injection' of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into for itemName, then the query becomes the following:
The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:
This simplification of the query allows the attacker to bypass the requirement that the query only return items owned by the authenticated user; the query now returns all entries stored in the items table, regardless of their specified owner.

Example 3

This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name hacker enters the string 'name'); DELETE FROM items; --' for itemName, then the query becomes the following two queries:
Many database servers, including Microsoft® SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error in Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, in databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.
Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed. In this case the comment character serves to remove the trailing single-quote left over from the modified query. In a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string 'name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a', the following three valid statements will be created:
One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from a whitelist of safe values or identify and escape a blacklist of potentially malicious values. Whitelisting can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, blacklisting is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers can:
Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.
Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. For example, the following PL/SQL procedure is vulnerable to the same SQL injection attack shown in the first example.
Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.

Related Threat Agents

Related Attacks

Related Vulnerabilities

Related Controls

References

Retrieved from 'https://www.owasp.org/index.php?title=SQL_Injection&oldid=212863'
Join hundreds of InfoSec professionals at our upcoming
[Global AppSec DC, September 9-13] and [Global AppSec Amsterdam, September 23-27]

Summary

An SQL injection attack consists of insertion or 'injection' of either a partial or complete SQL query via the data input or transmitted from the client (browser) to the web application. A successful SQL injection attack can read sensitive data from the database, modify database data (insert/update/delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file existing on the DBMS file system or write files into the file system, and, in some cases, issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into. This said, the values that we'll use as Username and Password are:

In this way, we'll get the following query:
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

This may return a number of values. Sometimes, the authentication code verifies that the number of returned records/results is exactly equal to 1. In the previous examples, this situation would be difficult (in the database there is only one value per user). In order to go around this problem, it is enough to insert a SQL command that imposes a condition that the number of the returned results must be one. (One record returned) In order to reach this goal, we use the operator 'LIMIT <num>', where <num> is the number of the results/records that we want to be returned. With respect to the previous example, the value of the fields Username and Password will be modified as follows:

In this way, we create a request like the follow:

Example 2 (simple SELECT statement):

Consider the following SQL query:

Consider also the request to a script who executes the query above:

When the tester tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:


In this case, probably the application would return some message telling us there is no content available or a blank page. Thenthe tester can send a true statement and check if there is a valid result:

Sims 4 uncensor code

Example 3 (Stacked queries):

Depending on the API which the web application is using and the DBMS (e.g. PHP + PostgreSQL, ASP+SQL SERVER) it may be possible to execute multiple queries in one call.

Consider the following SQL query:

A way to exploit the above scenario would be:

This way is possible to execute many queries in a row and independent of the first query.

Fingerprinting the Database

Even though the SQL language is a standard, every DBMS has its peculiarity and differs from each other in many aspects like special commands, functions to retrieve data such as users names and databases, features, comments line etc.

When the testers move to a more advanced SQL injection exploitation they need to know what the back end database is.
1) The first way to find out what back end database is used is by observing the error returned by the application. The following are some examples of error messages:

MySql:
One complete UNION SELECT with version() can also help to know the back end database.

Oracle:

MS SQL Server:

PostgreSQL:

2) If there is no error message or a custom error message, the tester can try to inject into string fields using varying concatenation techniques:

MySql: ‘test’ + ‘ing’
SQL Server: ‘test’ ‘ing’
Oracle: ‘test’||’ing’
PostgreSQL: ‘test’||’ing’ Gms plugin fl studio free download.

Exploitation Techniques

Union Exploitation Technique

The UNION operator is used in SQL injections to join a query, purposely forged by the tester, to the original query. The result of the forged query will be joined to the result of the original query, allowing the tester to obtain the values of columns of other tables. Suppose for our examples that the query executed from the server is the following:


We will set the following $id value:


We will have the following query:


Which will join the result of the original query with all the credit card numbers in the CreditCardTable table. The keyword ALL is necessary to get around queries that use the keyword DISTINCT. Moreover, we notice that beyond the credit card numbers, we have selected two other values. These two values are necessary because the two queries must have an equal number of parameters/columns in order to avoid a syntax error.

The first detail a tester needs to exploit the SQL injection vulnerability using such technique is to find the right numbers of columns in the SELECT statement.

In order to achieve this the tester can use ORDER BY clause followed by a number indicating the numeration of database’s column selected:


If the query executes with success the tester can assume, in this example, there are 10 or more columns in the SELECT statement. If the query fails then there must be fewer than 10 columns returned by the query. If there is an error message available, it would probably be:


After the tester finds out the numbers of columns, the next step is to find out the type of columns. Assuming there were 3 columns in the example above, the tester could try each column type, using the NULL value to help them:


If the query fails, the tester will probably see a message like:
If the query executes with success, the first column can be an integer. Then the tester can move further and so on:

After the successful information gathering, depending on the application, it may only show the tester the first result, because the application treats only the first line of the result set. In this case, it is possible to use a LIMIT clause or the tester can set an invalid value, making only the second query valid (supposing there is no entry in the database which ID is 99999):

Boolean Exploitation Technique

The Boolean exploitation technique is very useful when the tester finds a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect).

By using inference methods, it is possible to avoid this obstacle and thus to succeed in recovering the values of some desired fields. This method consists of carrying out a series of boolean queries against the server, observing the answers and finally deducing the meaning of such answers. We consider, as always, the www.example.com domain and we suppose that it contains a parameter named id vulnerable to SQL injection. This means that carrying out the following request:


We will get one page with a custom message error which is due to a syntactic error in the query. We suppose that the query executed on the server is:


Which is exploitable through the methods seen previously. What we want to obtain is the values of the username field. The tests that we will execute will allow us to obtain the value of the username field, extracting such value character by character. This is possible through the use of some standard functions, present in practically every database. For our examples, we will use the following pseudo-functions:

SUBSTRING (text, start, length): returns a substring starting from the position 'start' of text and of length'length'. If 'start' is greater than the length of text, the function returns a null value.

ASCII (char): it gives back ASCII value of the input character. A null value is returned if char is 0.

LENGTH (text): it gives back the number of characters in the input text.

Through such functions, we will execute our tests on the first character and, when we have discovered the value, we will pass to the second and so on, until we will have discovered the entire value. The tests will take advantage of the function SUBSTRING, in order to select only one character at a time (selecting a single character means to impose the length parameter to 1), and the function ASCII, in order to obtain the ASCII value, so that we can do numerical comparison. The results of the comparison will be done with all the values of the ASCII table, until the right value is found. As an example, we will use the following value for Id:


That creates the following query (from now on, we will call it 'inferential query'):
Patent design for the leader t2 rifle. Apr 01, 2015  The Leader T2 was not a commercial success, resulting in the dissolution of Charles St. George’s company Armtech Pty Ltd in 1983. The patents were subsequently sold off to the Australian Automatic Arms company (AAA) who produced Leader derivatives in 16.25″, 10.5″ carbine, and a. Has some unique design features, one of which is a triangular bolt head that was later copied by Barrett in their M82A1.50 Caliber sniper rifle. The stocks are super tough Zytel and this is the first rifle to have used this polymer which is now the standard material for numerous military rifles and handgun frames. The Leader T2 MK5 Series weapons were chambered for the 5.56×45mm NATO cartridge and manufactured by Leader Dynamics of Smithfield, NSW, Australia (1978-1982/1983). The Leader was the brainchild of weapons designer Charles St. George.It was originally a contender for a 5.56 mm Australian military service rifle to replace the then-issued Lithgow L1A1-F1 SLR and Colt M16A1 rifles.


The previous example returns a result if and only if the first character of the field username is equal to the ASCII value 97. If we get a false value, then we increase the index of the ASCII table from 97 to 98 and we repeat the request. If instead we obtain a true value, we set to zero the index of the ASCII table and we analyze the next character, modifying the parameters of the SUBSTRING function. The problem is to understand in which way we can distinguish tests returning a true value from those that return false. To do this, we create a query that always returns false. This is possible by using the following value for Id:


Which will create the following query:


The obtained response from the server (that is HTML code) will be the false value for our tests. This is enough to verify whether the value obtained from the execution of the inferential query is equal to the value obtained with the test executed before. Sometimes, this method does not work. If the server returns two different pages as a result of two identical consecutive web requests, we will not be able to discriminate the true value from the false value. In these particular cases, it is necessary to use particular filters that allow us to eliminate the code that changes between the two requests and to obtain a template. Later on, for every inferential request executed, we will extract the relative template from the response using the same function, and we will perform a control between the two templates in orderto decide the result of the test.

In the previous discussion, we haven't dealt with the problem of determining the termination condition for out tests, i.e., when we should end the inference procedure. A techniques to do this uses one characteristic of the SUBSTRING function and the LENGTH function. When the test compares the current character with the ASCII code 0 (i.e., the value null) and the test returns the value true, then either we are done with the inference procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field Id:


Where N is the number of characters that we have analyzed up to now (not counting the null value). The query will be:


The query returns either true or false. If we obtain true, then we have completed the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

The blind SQL injection attack needs a high volume of queries. The tester may need an automatic tool to exploit the vulnerability.

Error based Exploitation technique

An Error based exploitation technique is useful when the tester for some reason can’t exploit the SQL injection vulnerability using other technique such as UNION. The Error based technique consists in forcing the database to perform some operation in which the result will be an error. The point here is to try to extract some data from the database and show it in the error message. This exploitation technique can be different from DBMS to DBMS (check DBMS specific section).

Consider the following SQL query:


Consider also the request to a script who executes the query above:


The malicious request would be (e.g. Oracle 10g):


In this example, the tester is concatenating the value 10 with the result of the function UTL_INADDR.GET_HOST_NAME. This Oracle function will try to return the host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:


Then the tester can manipulate the parameter passed to GET_HOST_NAME() function and the result will be shown in the error message.

Out of band Exploitation technique

This technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. The technique consists of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:


Consider also the request to a script who executes the query above:


The malicious request would be:


In this example, the tester is concatenating the value 10 with the result of the function UTL_HTTP.request. This Oracle function will try to connect to ‘testerserver’ and make a HTTP GET request containing the return from the query “SELECT user FROM DUAL”. The tester can set up a webserver (e.g. Apache) or use the Netcat tool:

Time delay Exploitation technique

The time delay exploitation technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. This technique consists in sending an injected query and in case the conditional is true, the tester can monitor the time taken to for the server to respond. If there is a delay, the tester can assume the result of the conditional query is true. This exploitation technique can be different from DBMS to DBMS (check DBMS specific section).

Consider the following SQL query:


Consider also the request to a script who executes the query above:


The malicious request would be (e.g. MySql 5.x):


In this example the tester is checking whether the MySql version is 5.x or not, making the server to delay the answer by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

Stored Procedure Injection

When using dynamic SQL within a stored procedure, the application must properly sanitize the user input to eliminate the risk of code injection. If not sanitized, the user could enter malicious SQL that will be executed within the stored procedure.

Consider the following SQL Server Stored Procedure:
User input:
This procedure does not sanitize the input, therefore allowing the return value to show an existing record with these parameters.
NOTE: This example may seem unlikely due to the use of dynamic SQL to log in a user, but consider a dynamic reporting query where the user selects the columns to view. The user could insert malicious code into this scenario and compromise the data.
Consider the following SQL Server Stored Procedure:
User input:
This will result in the report running and all users’ passwords being updated.

Automated Exploitation

Most of the situation and techniques presented here can be performed in a automated way using some tools. In this article the tester can find information how to perform an automated auditing using SQLMap:

SQL Injection signature Evasion Techniques

The techniques are used to bypass defenses such as Web application firewalls (WAFs) or intrusion prevention systems (IPSs).Also refer to https://www.owasp.org/index.php/SQL_Injection_Bypassing_WAF

White Space

Dropping space or adding spaces that won't affect the SQL statement. For example
Adding special character like new line or tab that won't change the SQL statement execution. For example,

Null Bytes

Use null byte (%00) prior to any characters that the filter is blocking.
For example, if the attacker may inject the following SQL
to add Null Bytes will be

SQL Comments

Adding SQL inline comments can also help the SQL statement to be valid and bypass the SQL injection filter.Take this SQL injection as example.
Adding SQL inline comments will be.

URL Encoding

Use the online URL encoding to encode the SQL statement
The URL encoding of the SQL injection statement will be

Character Encoding

Char() function can be used to replace English char. For example, char(114,111,111,116) means root
To apply the Char(), the SQL injeciton statement will be

String Concatenation

Concatenation breaks up SQL keywords and evades filters.Concatenation syntax varies based on database engine. Take MS SQL engine as an example
The simple SQL statement can be changed as below by using concatenation

Hex Encoding

Hex encoding technique uses Hexadecimal encoding to replace original SQL statement char.For example, 'root' can be represented as 726F6F74
The SQL statement by using HEX value will be:
or

Declare variables

Declare the SQL injection statement into variable and execute it.
For example, SQL injection statement below
Define the SQL statement into variable SQLivar

Alternative Expression of 'or 1 = 1'

Tools

References

Technology specific Testing Guide pages have been created for the following DBMSs:
Whitepapers
Documentation on SQL injection vulnerabilities in products
Retrieved from 'https://www.owasp.org/index.php?title=Testing_for_SQL_Injection_(OTG-INPVAL-005)&oldid=215999'