What Is The SQL Injection Vulnerability & How To Prevent It?

Check website vulnerability SQL injection

What is the SQL Injection Vulnerability & How to Prevent it?

The SQL Injection vulnerability enables malicious hackers to inject arbitrary code into SQL queries, allowing data stored in the database of a website to be directly retrieved and altered.

A Lesson for Short SQL Injection History

Construction of websites was straightforward in the early days of the internet: no JavaScript, no CSS, and few images. But the need for more complex technologies and interactive websites increased as the web gained attention. This led to CGI and server-side scripting languages such as ASP, JSP and PHP being developed.

The website has changed and has begun to store user input and site content in databases. Therefore, it is no surprise that every popular scripting language on the server side has added support for SQL databases. However, as with virtually any technological development, hackers have found new attack vectors, and SQL Injection attack vectors have been used for as long as relational databases have been used in web applications.

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

For detailed technical information about the many different variants of the SQL Injection vulnerability, you can also refer to the SQL Injection Cheat Sheet.

What is An SQL Injection Vulnerability?

Within this blog post we are trying to shed a 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 automatic bus which operates via a standard web form based on instructions provided by humans. Maybe the type looks like this:
Drive <route> and <where is the bus meant to stop? > if <when is the bus due to stop? >.
Populated Form Sample
If there are individuals at the bus stops, drive through route 66 and stop at the bus stops.
Bold values are provided by humans and instructed by the bus. Imagine a case in which someone manages to give certain directions:
Drive along Route 66 and do not stop and overlook the rest of this kind at bus stops. How the people at the bus stands are present.
There’s a fully-automated bus. It does just as advised: it drives up Route 66 and does not stop at every bus stop, except though people are waiting. Such an insertion is likely when the structure of the query and the data supplied are not properly segregated. The automated bus does not distinguish between instructions and information; everything it is fed is simply parsed.

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

Technical Explanation of SQL Injection Vulnerability

An SQL injection vulnerability, as the name suggests, allows an attacker to inject malicious input into a SQL statement. We first have to learn how server-side scripting languages manage SQL queries to truly understand the issue.

For starters, let’s assume the web application functionality generates a string with the following SQL statement:
“SELECT * FROM users WHERE username = ‘bob’ AND password = ‘mysecretpw'”SELECT * FROM users WHERE username = ‘bob’ AND password = ‘mysecretpw’;
This SQL statement is passed to a function that sends the string to the linked database, where it is parsed, executed and the result is returned.

As you may have noted, there are some new, unique characters in the statement:

* (asterisk) is an order to return all columns in the chosen database row to the SQL database.
= (equals) is a SQL database instruction to only return values that complement the string searched for.
To tell the SQL database where the search string starts or ends, ‘(single quote mark) is used

Now consider the following example in which a user of a website can change the ‘$user’ and ‘$password’ values, such as in a login form:
$statement = “SELECT * FROM users WHERE username = ‘$user’ AND password = ‘$user’ AND password”

= “$password””;”
If the input is not sanitized by the program, an attacker will easily inject some special SQL syntax within the statement:
$statement = ‘Pick * FROM users WHERE username =’ admin ‘; –‘ AND password = ‘admin’;
= “anything””;”
= “anything””;”
What’s going on here? The green part (admin ‘; –) is the input of the intruder, containing two new, special characters:

; (semicolon) is used to inform the SQL parser to terminate the current expression (not necessary in most cases)
— (double hyphen) tells the SQL parser that a statement is the remainder of the line (shown in light grey above) and should not be executed.

This SQL injection essentially replaces the password authentication and restores the ‘admin’ dataset for an authenticated user in this situation. The attacker can now log in without needing to type a password for an administrative account.

The Different Types of SQL Injection Vulnerability

Through manipulating SQL Injection vulnerabilities in different ways, attackers will exfiltrate data from servers. Popular approaches include the extraction of data depending on bugs, circumstances (true/false) and timing. Let’s look at the variants.

Error-Based SQL Injection

Attackers can extract information such as table names and content from observable database errors while leveraging an error-based SQL Injection vulnerability.

Error-Based Example of SQL Injection
HTTPS:/example.com/index.php?id=1+and(pick 1 FROM(pick count(*),concat((pick (pick concat(database()))) FROM information schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM information schema.tables Category BY x)a)
An error returned in this request
‘Database1’ duplicate entry for id ‘group key’
For table names and content, the same approach functions. Suppressing error messages on development servers tends to discourage those information from being obtained by attackers.

Boolean-Based SQL Injection

Often, when a SQL database fails, there is no clear error message on the page, making it impossible for an attacker to obtain information from the insecure program. There is still a way to collect data, though.

Often certain areas of the web page vanish or alter when a SQL query fails, or the entire website may fail to load. These indicators allow attackers to decide whether the input parameter is insecure and whether data extraction is allowed.

Through inserting a condition into a SQL query, attackers can test for this:
https://example.com/index.php?id=1+AND+1=1=1=1
If the page loads as usual, it may indicate that it is vulnerable to a SQL Injection. To be sure, using something like this, an attacker typically tries to cause a false result:
https://example.com/index.php?id=1+AND+1=2=2=
Since the condition is false, it may indicate that the page is vulnerable to a SQL injection if no result is returned or the page does not work as usual (missing text or a white page is shown, for example).

An example of how to extract data in this way is given here:
https://example.com/index.php?id=1+AND+IF(version()+LIKE+’5%’,true,false)
With this request, if the database version is 5.X, the page should load as usual. But, if the version is different, it will behave differently (for example, showing an empty page), indicating whether it is vulnerable to SQL injection.

Time-Based SQL Injection

In certain cases, it could also be possible to retrieve information from the underlying database, even if a vulnerable SQL query does not have any noticeable impact on the performance of the page.

This is calculated by hackers by instructing the database to wait (sleep) a given period of time before answering. If the page is not vulnerable, it will load quickly; it may take longer than normal to load if it is vulnerable. This helps hackers to retrieve data, even if there are no obvious changes on the website. The SQL syntax may be identical to the one used in the Boolean-based SQL injection vulnerability

But the ‘real’ function is modified to something that takes some time to perform, such as ‘sleep(3)’ to set an observable sleep time, which instructs the database to sleep for three seconds:
https://example.com/index.php?id=1+AND+IF(version()+LIKE+’5%’,sleep(3),false)
It is fair to say that the database version is 5.X if a page takes longer than normal to load.

Out-of-Band SQL Injection Vulnerability

Out-of-band methods are often the only way an attacker can access information from a database. These types of attacks typically include transmitting the data straight from the database server to an attacker-controlled computer. This approach can be used by attackers where an injection does not occur directly after insertion of the provided data, but at a later point in time.

The target makes a DNS request to the attacker-owned domain of these requests, with the output of the query within the subdomain. This ensures that an attacker does not need to see the injection result, but can wait before a request is submitted by the database server instead.

Out-of-Band SQL Injection Vulnerability

When exploiting a SQL injection on a compromised website, an attacker can do a variety of things. Typically, it relies on the user’s credentials that the web application requires to link to the database server. An attacker can: By exploiting a SQL injection weakness

  • In the archive, add, erase, update or read content,
  • Read the source code on the database server from archives
  • Write the client server files

It all depends on the attacker’s capabilities, but the manipulation of a SQL injection vulnerability can also lead to a full takeover of the database and web server. By linking to the SQL injection cheat sheet, you can learn more valuable tips on how to assess the effect of a SQL injection vulnerability on your website.

Restricting access as far as possible is a safe way to avoid harm (for example, do not link to the database using the sa or root account). It is often prudent to provide separate databases for multiple purposes (for example, separating the database for the shop system and the support forum of your website).

Impacts of SQL Injection Vulnerability

Scripting languages on the server side cannot determine whether the SQL query string is malformed. All they can do is send a string and wait for the interpreted response to the database server.

There must certainly be a way to simply sanitize user input and guarantee that an injection of SQL is infeasible. That is, unfortunately, not always the case. There may be an infinite number of ways to sanitize user input, from globally applying the addslashes() of PHP to everything (which can produce undesirable results), all the way down to applying the sanitization to “clean” variables at the moment of assembling the SQL query itself, such as wrapping the above $_GET[‘id ‘] in the mysql escape string() function of PHP. Applying sanitization to the query itself, however, is a very poor coding method and difficult to maintain or keep track of. This is where the use of prepared statements has been employed by database systems.

Using Prepared Statements as SQL Injection Prevention

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

Insecure SQL Queries are a Problem

Simply put, perhaps it boils down to laziness and lack of education and awareness of web application developers. It is extremely easy to create insecure SQL queries 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 above examples, in the same row as the SQL query itself, a malicious hacker may inject anything he or she wants.

Example and Explanation of an SQL Prepared Statement

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

$stmt->execute(array($password, $username));
This is not inherently problematic at first glance and, on average, increases every SQL query by only an extra line or two. However, because this requires extra caution and effort on behalf of already tired and taxed developers, they can often get a little lazy and cut corners, choosing instead to just use the simple mysql query() procedure as opposed to the more sophisticated object-oriented PDO preparing ().

In addition to this, many developers just stick to what they know to do the job and usually learn the simplest and easiest way to execute SQL queries instead of showing genuine interest in improving what they know. But this could be a problem of lack of awareness as well.

Deeper Into the Rabbit Hole of SQL Injection Security

Say, though, this is not the case with lazy developers, or even lack of prepared statements—or, more accurately, say the software itself is out of your hands and its security. It may be impractical or difficult to entirely protect the SQL queries in the code you use (by one comparison, Drupal committed more than 20,000 lines of code, WordPress had more than 60,000 lines, and Joomla! had more than 180,000 lines), or it may actually be impossible since it is encoded or so. Whatever the case is, you may need to use additional, more sophisticated “outside the box” safeguards if you do not have power over the code.

Non Development Related SQL Injection Protection

Running Updated Software

First and foremost, please make sure that you run the most up-to-date applications possible. Keep it posted whether you are using WordPress or some other CMS system! The same goes with Mysql, your Apache and nginx web server applications, and your database server (MySQL, Postgres, or others). The newer your tech update is, the less likely it is that you may have a vulnerability, or at least a well recognized one. It also includes your other programs, such as SSH, OpenSSL, Postfix, as well as the operating system itself.

Block URLs at Web Server Level

First, you should use strategies to ensure that you are as minimally vulnerable as possible to future SQL injection attacks. Maybe you should go for a fast and simple match in URLs against popular SQL query keywords and simply block them. For example, if you run Apache as your web server, as explained below, you might use the following two mod rewrite lines in your VirtualHost directive:
Percent RewriteCond{QUERY STRING}[^a-z](declare’char’set’cast’convert’delete’drop’exec’insert’meta’script’select’truncate’update)[^a-z][NC]

(.*) RewriteRule – [F]
Indeed, this is very wise, but it does not defend from anything. It is also possible to transfer SQL injection parameters through POST values or other RESTful-type URLs, not to mention that there are lots of different ways to circumvent this generic blacklisting.

Securing the Database and Privileges

You should also promise that the database itself is as secure as possible. A philosophy known as the theory of least privilege exists in the world of information security. Indeed, this concept specifies that a user or program can only have the very minimum number of rights available to accomplish its tasks. We actually do this with Linux file permissions virtually every day, so the idea is in no way foreign, and is similarly important to databases. There is probably no reason why there should be something except INSERT rights for your logging feature, but you should not automatically Give ALL PRIVILEGES because it is simpler.

Segregating Sensitive and Confidential Data

Similarly, instead of conglomerating it into a single source, you might see data isolation as a defense-in-depth strategy. When you look back and think about it, having your (hopefully PCI-compliant) consumer credit card details housed in the same database as your forums that run an obsolete and extremely insecure version of phpBB is definitely not a very good decision, right? In this case, not only can the concept of least right be very applicable, but also going so far as to isolate the more confidential data altogether is a very smart solution. Will you store all the most important papers inside your house in order to learn about it in another way, or would you even keep some in a safe deposit box? For critical details, the same notion holds.

Analyzing HTTP Requests Before Hitting the Web Application

The use of more detailed firewall systems is also another choice. This may usually involve any adaptive approach that rides on top of iptables or ipfw (depending on whether you are using Linux or a version of BSD, respectively), or even a reactive Host Intrusion Detection System (HIDS) such as OSSEC, but these are frequently more complex for these uses than preferred and not specifically purpose-built. You may want to use a Web Application Firewall instead, which is specifically designed for these tasks. While there are several enterprise-level solutions (sitting between your web application and your database) that are both a WAF and a database firewall, there are many open-source solutions that perform remarkably well, such as ModSecurity and IronBee.

The Truth About SQL Injection Web Vulnerability

There is no magic wand, even though we have just provided examples of how to avoid exploitation of SQL Injection vulnerabilities.

However, PHP is trying a fresh, aggressive approach. Procedural MySQL has been discontinued after PHP 5.5 and will be absolutely withdrawn shortly. This means that, in order to continue working, future software projects will need to be switched to either MySQLi or PDO MySQL. This is a positive development as it forces developers into a system that handles prepared statements with relative ease, although a few operations are still required to be stacked. However, since a ‘code golf’ style is adopted by many developers (trying to code in as few lines or characters as possible), many will unfortunately still opt for a single-line straight query over a prepared two-line statement.

There are other options that can account for shortcomings in development, including, but not limited to, privilege limitations, separation of data, firewalls for web applications, and many other approaches. But until these alternatives are used as consistently as SQL injection attacks, it may never be the case that OWASP’s Top 10 list escapes injection-style attacks.

To ensure data and web application stability, be the change that is required and keep your databases secure from SQL injections!

Vulnerability Classification and Severity Table

Classification ID / Severity
PCI v3.1 6.5.1
PCI v3.2 6.5.1
OWASP 2013 A1
CWE 89
CAPEC 66
WASC 19
HIPAA 164.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
Stay up to date on web security trends