Application security

SQL Injection: The Equal Opportunity Vulnerability

Introduction

In the first installment of this series, we discussed application security within the Software Development Process by demystifying the adoption of security controls within the development organization. We also took a deeper dive into identifying potential vulnerabilities based on threats to attack surfaces exposed to the application, a process known as threat modeling.

In this installment, we are going to take a technical dive into specific vulnerabilities that can be used to compromise the attack surfaces and discuss what is arguably one of the most prevalent attacks to applications and inflicts the most exposure of sensitive data: SQL Injection. Injection attacks attempt to infiltrate an application by injecting malicious content into the application through available input channels. Input channels can be in the form of form fields, uploads, 3^rd party API, data access channels, configuration files, input files: essentially, any point at which the application receives data for processing. In addition, this article will highlight a few automated tools that will also be beneficial at identifying SQL Injection flaws.

SQL Injection is defined by the Open Web Application Security Projection (OWASP) as "[consisting] of insertion or 'injection' of an SQL query via the input data from the client to the application...SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands." According to the Web Application Security Consortium, "SQL Injection is an attack technique used to exploit applications that construct SQL statements from user-supplied input. When successful, the attacker is able to change the logic of SQL statements executed against the database."

Notice an important similarity between these definitions – SQL Injection affects applications, not just web applications. This is an important detail that is lost far too often when referring to Injection flaws, in large part because the media and many experts focus primarily on web applications when speaking in the context of SQL Injection flaws. Web applications are externally facing, exposing them to the virtual world. However, internal threats can be even more dangerous, opening up the organization to pilferage of data from malware, viruses, employees, and other influences. These internal threats have access to more data than external attackers likely do not have access to, unless an external attacker combines a SQL Injection attack with secondary attacks that allow infiltration, escalation of privilege, and other gains.

Therefore, as a general rule, any application that receives input and uses that input to solicit information from a data repository through the construction of Structured Query Language (SQL) is potentially vulnerable to a SQL Injection attack, regardless of whether it is a web application desktop, client application or batch application, as well as whether the application is externally or internally facing. Unlike the majority of the other articles that discuss and demonstrate a SQL Injection flaw and attack that use a web application as the platform under attack, this article will demonstrate a successful attack using a desktop or client application. In addition, this article will demonstrate that multiple programming languages can fall victim to an SQL Injection vulnerability – again, it's not the language used, but how the language uses the supplied data.

SQL Injection How-To

The construction and understanding of a SQL statement is beyond the scope of this article: however, an excellent tutorial can be found here. The danger of a SQL Injection flaw is the simplicity of a SQL statement and the ability to, with just a few characters, potentially expose or compromise a significant amount of data. For a tutorial on SQL Injection, simply open a URL to a search engine and type in the phrase SQL Injection Example. There are many tutorials available that will help an organization perform specialty testing to determine if the application is protected from a SQL Injection attack.

However, before we discuss the tools that can be used to help in the identification of SQL Injection flaws, it is important to explore what SQL Injection flaws look like within code. We will focus on applications written in four different languages, i.e. C, Java, Ruby, and C#, all of which were developed on Ubuntu version 11, using Code:Blocks IDE, Net Beans IDE, Eclipse IDE (with Ruby plug-in), and MonoDevelop, for the respective languages. The database is MySql. All languages used the MySql API for interacting with the MySql Server.

C Code

Many developers are surprised to learn that many applications that perform key functions that require heavy processing loads, large volume transactions, and are light-weight written in C are vulnerable to SQL Injection attacks. These applications are written as either CGI, embedded, APIs, or back-end broker type applications. This type of processing is ideal for direct interaction with databases and is a sweat spot for attackers to target because of the unprotected SQL statements that are created as part of the data processing functions. In addition, many developers and managers are of the mindset that since the application sits two or more layers deep (with the exception of CGI) in the data flow stack that it is unlikely to fall prey or victim to a SQL Injection attack. While there may be some merits to this argument, these merits are only true if other protection mechanisms are put in place upstream. However, it does not fix the known issue that the application is ultimately still susceptible to a SQL Injection attack regardless of the number of mitigating controls in front of it.

So, what does a SQL Injection vulnerability look like within C code? Figure 1 is a snippet of code that demonstrates the dynamic construction of a SQL statement that retrieves a list of session records from the sessions table.

Figure 1: C code showing SQL Injection flaw. Line 6 is the SQL Statement

and lines 51 and 52 receive the input and concatenate

it to the SQL statement respectively.

Figure 2 demonstrates a successful SQL Injection attack based on the code in Figure 1 (in the unsafeRecordRetrieve):

Figure 2: SQL Injection input to the C program.

For the input the user entered 51-ASCII(2) OR 567=567. What this translates to is that MySql first executed the statement ASCII(2) by determining the ASCII value of 2, then subtracting that number from 51. MySql then appends the OR statement 567=567 and compares this statement to the statement for the value of 51-ASCII(2). Here is the catch – no matter what the value or conditional is prior to the OR statement, the OR statement will always return true: therefore, all records will be returned regardless of the value of the first condition. In the case of the example, no matter what the value of uid equals, the OR condition will result in a true statement telling MySql to return all records requested in the SQL statement.

With this simple statement, an attacker learns three important and useful techniques for pilfering data: the application is vulnerable, the database will execute database functions, and the input is not constrained to just numbers. This scenario is a worst case situation for any organization and application.

Java

Java has grown over the years to make its top debut as one of the mainstream languages that many organizations use to develop business applications and that the open source community uses to write applications available to the public free of charge. Java is also used in embedded and mobile devices in derivative forms, making it a universal and cross-platform language of choice. This also makes it more susceptible and vulnerable to attacks, and SQL Injection is no exception.

So, what does a SQL Injection vulnerability look like within Java code? Figure 3 is a snippet of code that demonstrates the dynamic construction of a SQL statement that retrieves a list of session records from the sessions table.

Figure 3: Java code showing SQL Injection flaw. Line 205 is the SQL Statement

with the input from the user.

Figure 4 demonstrates a successful SQL Injection attack based on the code in Figure 3 (in the getResultSet function):

Figure 4: SQL Injection input to the Java program.

As with the C program, the Java application also allows the user to input any string of characters and values, which includes the OR condition that will be appended to the SQL statement and executed by the MySql server, resulting in all of the records in the table being returned and displayed to the user. To demonstrate the effectiveness of the OR statement, the values were changed (from 567 to 3421), but the result is the same. Again, this outcome is due to the OR statement evaluating to TRUE as long as the two values equivalent.

Ruby

Ruby is an up-and-coming open source language that allows for a significant amount of abstraction, meaning that there are no declarative statements that bind to a data type for variables. While this simplifies the programming syntax, it presents challenges to validation and other security measures that are provided through a declarative language like C and Java. Ruby is, however, gaining in popularity in the open source community with more and more applications becoming available for use. Ruby allows for adoption of Java, C, and other programming languages with the concept of a Gem, making it a very diverse language that is powerful with very little code. This also makes it more susceptible and vulnerable to attacks.

So, what does a SQL Injection vulnerability look like within Ruby code? Figure 5 is a snippet of code that demonstrates the dynamic construction of a SQL statement that retrieves a list of session records from the sessions table.

Figure 5: Ruby code showing SQL Injection flaw. Line 13 is the SQL Statement

being used with the input from the user obtained in line 12.

Figure 6 demonstrates a successful SQL Injection attack based on the code in Figure 5 (in the initialize function):

Figure 6: SQL Injection input to the Ruby program.

As with the C and Java programs, the Ruby application also allows the user to input any string of characters and values, which includes the OR condition that will be appended to the SQL statement and executed by the MySql server, resulting in all of the records in the table being returned and displayed to the user. To demonstrate the effectiveness of the OR statement, the values were changed from numeric to characters (from 3421 to 'a'): however, the result is the same, due to the OR statement evaluating to TRUE as long as the two values equivalent.

C#

Much like Java, C# has grown over the years to make its top debut as one of the mainstream languages that many organizations use to develop business applications. Unlike Java, C# is a licensed language and is part of the Microsoft .NET framework. There is an open source project, MonoDevelop, that brings C# to the open source community via the MonoDevelop IDE.

So, what does a SQL Injection vulnerability look like within C# code? Figure 7 is a snippet of code that demonstrates the dynamic construction of a SQL statement that retrieves a list of session records from the sessions table.

Figure 7: C# code showing SQL Injection flaw. Line 40 is the SQL Statement

used with the input from the user obtained in line 42.

Figure 8 demonstrates a successful SQL Injection attack based on the code in Figure 7 (in the initialize function):

: Figure 8: SQL Injection input to the C# program.

As with the programs previously discussed, the C# application also allows the user to input any string of characters and values, which includes the OR condition that will be appended to the SQL statement. To again demonstrate the effectiveness of the OR statement, the values were changed from numeric to a word (from 'a' to 'hello'): however, the result is the same, due to the OR statement evaluating to TRUE as long as the two values are equivalent.

How to Protect Against SQL Injection

Even though four different languages were used to demonstrate a successful SQL Injection example, there is one common theme among them: taking the input directly from the user and adding it to the SQL statement without validation.

Figures 9 through 12 demonstrate how to take the user input and wrap the input into a parameterized SQL statement. The parameterized variable is then validated by MySql for the direct data type and properly encoding special characters, wrapping functions into strings and converting strings to numerical values if necessary.

Figure 9: C code for wrapping the user input into a parameterized SQL query statement.

Line 16 is the parameterized SQL statement, and lines 61 – 74 create the bind parameter

and set the value.

Figure 10: Java code for wrapping the user input into a parameterized SQL query statement.

Line 225 is the parameterized SQL statement, and lines 226 and 227 create the bind parameter

and set the value.

Figure 11: Ruby code for wrapping the user input into a parameterized SQL query statement.

Line 35 is the parameterized SQL statement, and line 36 creates the bind parameter

and set the value.

Figure 12: C# code for wrapping the user input into a parameterized SQL query statement.

Line 68 is the parameterized SQL statement, and lines 69 - 73 creates the bind parameter

and set the value.

Figures 13 through 16 show the output of the protected SQL statements.

Figure 13: C

application output showing protection from SQL Injection.

Figure 14: Java application output showing protection from SQL Injection.

Figure 15: Ruby application output showing protection from SQL Injection.

Figure 16: C# application output showing protection from SQL Injection.

    There are many tools available from the open source community as well as from commercial vendors that will perform static code analysis and dynamic scanning of applications. Static code analysis can be used for any application type as long as the specific tool supports the language of the application undergoing the scan. During a static code analysis scan, the tool will find particular patterns within the syntax that could be considered as vulnerabilities, warranting a closer look. Dynamic scanning tools are used primarily for web applications where a series of attacks are launched against the application. The attacks are then evaluated to determine whether the attack was successful or unsuccessful – if it is considered successful, a vulnerability is reported.

While the tools are a great asset to quickly scan applications, it is never advised to rely solely on the output provided by the tools. The tools look at the application and potential vulnerabilities as either a 1 or 0, meaning the context is not a factor when reporting vulnerabilities. This type of review can result in false positives which is why the most important asset – human eyes – should always be the final arbiter on whether a vulnerability identified is worth remediating or whether it can be deemed as a false positive.

To cite an example, many dynamic scanning tools will check for SQL Injection vulnerabilities. The tools will launch a series of statements similar to the ones discussed in this article to form fields that solicit input. If the tool receives a 200 OK response, the tool will consider this to be a successful attack. However, if the application has the defense mechanisms put into place to protect from SQL Injection vulnerabilities, then a 200 OK response would be expected. Only after a careful review will it be determined that the application is not vulnerable to SQL Injection. For a list of the tools available, click here.

Final Thoughts

SQL Injection is an equal opportunity vulnerability, impacting all languages that allow access to a data source that uses SQL as the mechanism to extract data, and exposing in web applications and client applications alike. The nature of SQL Injection is what makes it one of the most dangerous and effective vulnerabilities to applications.

We've presented one of the most effective ways of protecting applications. However, as with any security practice, an in-depth, multifaceted approach is the best approach to protecting applications. The following guidelines provide the ultimate protection against SQL Injection:

1. Input Validation – do not trust any data from any source. Validate the information for content, length, format, and other factors prior to use.
2. Parameterized statements – avoid dynamic SQL statements. Always bind data to parameters that clearly identify the data type of the bind value.
3. Business rule validation – always apply business validation to input. Business validations include length, type, and expected value.
4. Least privilege – only allow read only access to the data as a general rule, and other access as an exception. If a form within an application simply views the data, only call the database with a read-only database user. If adding or modifying data, call the database with a modify and add database user.
5. Logging – always log access to data, modification of data, and, if necessary, access to the data.
6. As a general rule, do not allow deletion - mark record for deletion and create a separate process to delete.
7. Threat modeling – always threat model an application to understand access points to the database, input points to the application, and what boundaries and layers are involved through the data flow of the application.
8. Error handling – do not throw detailed error messages to the screen for viewing by the user. The detailed information that is included in an error message is invaluable to an attacker providing valuable clues on how to modify the attack to allow the attack to execute without error.

ATTACHED: SOURCE CODE

The next installment of the series will discuss Cross-Site Scripting (XSS) and Cross-Site Request Forgery (CSRF).