Common Web Application Vulnerabilities - Part 5

By Joseph Belans ·

In this series of posts, my colleagues and I will dig into some specific, common web application vulnerabilities we observe regularly while performing network and application pentests. The intention of this series is to further expand upon a lot of the great information that already exists on the topic while preemptively addressing common questions we receive from our customers.

Part 5: SQLi - Overview and Examples

SQLi, or Structured Query Language injection, is a widely known injection technique used to attack the underlying database of a web application. From a historical standpoint, SQLi is commonly referred as a technique to access a structured database; however, injection attacks are also inherent in the more recent NoSQL technologies (like MongoDB, Elasticsearch, CouchDB, etc.). Therefore, the intention of this post is to provide an overview of what SQLi is, the various types and how it pertains to structured and unstructured databases. We’ll also go over examples of how the attacks are performed and some recommendations on how to defend against SQLi attacks.

Overview

OWASP (owasp.org) defines SQLi as follows:

An SQL injection attack consists of insertion or "injection" of an 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
  • Issue commands to the operating system in some cases.

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. There are various types of SQLi attacks:

  • First Order Attacks - The injection attack has an immediate response whether it be returning data from the database, altering data or executing a command.
  • Second Order Attacks - SQL data is entered into the database and ultimately executed during a future request (for example, when an administrator is logged into the administrative console).
  • Blind SQLi - The attacker asks the database true/false questions to infer information regarding the data, the database or the underlying operating system.

Let’s go over each individually. First order attacks are what most people think about when talking about SQLi. A common example involves a vulnerable web form that returns queried data to the user, like a search form. An attacker would enter a single tick (‘) in the search field, which is passed directly to the database via a query constructed by the web application.

As a result of the addition of the single tick, the SQL query will be invalid and cause an error in the application. The web server or the application would recognize this and generate an error page. The attacker would then attempt to modify the query by inserting a valid SQL statement, typically via a UNION statement, to return additional information from the database. If successful, usernames, passwords and other sensitive data could be included in the results.

Second order attacks are a little more complicated and require additional actions for the attack to complete. For example, let’s say an attacker was able to update a piece of information contained within the database, and the update included valid SQL code. The application properly escapes the data prior to storing it in the database, which prevents SQLi from occurring within the initial query. However, this data is utilized by another SQL query that does not properly sanitize the data and does not use parameterized values. As a result, when the SQL data is inserted into the second SQL query, the code is executed by the database.

Lastly, blind SQL injection attacks are associated with attack vectors where the web application does not return the query results to the user; however, may return a true/false response based on the outcome of the request (for example, a user login – valid or invalid user) or a static invalid username or password response. The attacker could use the username field to submit data directly to the database and utilize the responses to infer information from the database as follows:

  • User exists -> The query was valid, and the attacker verifies the answer to the query to be true.
  • User does not exist -> The query was invalid, and the attacker validates the answer to the query is false.
  • Static answer -> The attacker inserts a time-wait into the query.
    • Immediate response -> The answer to the query is false.
    • Delay in response -> The answer to the query is true.

The attacker could attempt to extract data from the database using the true/false scenarios. Some examples will be provided later in this document.

Structured vs. Unstructured

SQLi is commonly associated with relational databases (structured); however, the same rules apply to NoSQL (unstructured) databases. More importantly, many developers believe that NoSQL databases are not vulnerable to SQLi. This could not be further from the truth.

A vulnerable web application that utilizes a structured database and generates in-line queries (vs. using parameterized queries) is susceptible to SQLi. The injection point is where the user supplied data in inserted into the query. If the data is not properly sanitized, an attacker could insert data that would be interpreted (or executed) by the database system. As a result, the attacker could ultimately modify the original query, execute additional queries or possibly execute commands on the underlying operating system.

Applications that utilize unstructured databases are susceptible to the above as well, but in a different manor. A dictionary is generated and sent to the database to query for a set of results. If the user data is not properly sanitized, an attacker could modify the original request in multiple ways:

  • Inject data into the $where clause, which could alter the results of the request or execute additional commands.
  • Inject operators into the request, which could alter the original meaning of the request.
  • Inject JSON/javascript, which could be executed within the database.

Examples

First Order Attack

The following example describes one of the most fundamental SQLi attacks. To demonstrate this, and the following structured database injection attacks, a vulnerable application was developed using PHP and MySQL. The application provides a typical login form where the user will pass the username and password to login to the application. The application will generate the queries using in-line strings vs. parameterized values. As a result, any data that is submitted via the form will be injected directly into the query and sent to the database.

The backend query that searches for the provided username and password is as follows:

SELECT user, password FROM users WHERE user=’’ AND password=’

The following screenshots show the outcome of an unsuccessful and successful login:

Figures 1 & 2: Query with invalid username and result with invalid login

 

Figures 3 & 4: Query with valid login and result with valid login

 

The following request contains a standard SQLi string, which will always return true. The string ‘ or 1=1;# would cause the application to generate the following WHERE clause:

WHERE user=’’ or 1=1;#’ AND password=’test’

The first single quote closes the search of the user field and the 1=1 will always be interpreted as a true statement. Therefore, the string will be interpreted as “give me all records where the user is an empty string or true,” which will always be true. As a result, all rows will be returned from the table.

Figures 5 & 6: SQLi query and result

 

Second Order Attack

Second order attacks utilize data previously stored in the database and require multiple actions to complete the attack. Our vulnerable application has been modified to include an updated profile page. The information provided by the user will be properly escaped when recorded in the database. An example update is provided below:


Figure 7: Edit profile

 


Figure 8: Result of update

 

As you can see, the quotes contained within the group field have been properly escaped so that a valid SQL query is generated, and the data entered in the group field is correctly stored in the database. The group field is not vulnerable to SQL injection since all data is properly escaped. The following screenshot shows the state of the database after the update:


Figure 9: Current state of database

 

A view user page has also been created, which displays the user’s profile and associated users. The application identifies associated users generating a second SQL query that uses the data stored in the grp field. The grp data is not escaped prior to being inserted into the second query. As a result, when the user1 profile is requested, the database executes the malicious code and changes the admin password. The view profile page and result of the second SQL query are provided below:


Figure 10: Result of view page

 


Figure 11: Result of second order injection

 

As you can see, the admin password has been successfully changed. The password change is possible due to the fact that the second query does not properly escape the data contained within the grp field prior to generating the second query.

Blind SQL

Continuing with our vulnerable application, a slight modification has been made which prevents the results of the query from being displayed to the user. As a result, the application returns one of three results:

  • “Valid login” when a valid username/password combination is provided
  • “Invalid login” when an invalid username/password combination is provided
  • “error” when an invalid query string is generated

Since no results are provided to the requesting user, an attacker would need to use the true/false scenarios to identify data within the database. The following are examples of true/false scenarios:

Figures 12 & 13: True SQLi request and response

 

Figures 14 & 15: False SQLi response and request

 

The attacker could send multiple queries to the database and use the true/false responses to brute-force the data in the database. For example, the following request will test if the first letter of the password is “a.”

 

Figures 16 & 17: SPLi to check if first letter of password is 'a'
and the SQLi response stating it is not an "a"

 

The attacker receives the “Invalid login” response, which indicates that this is incorrect. Additional requests would be generated for every letter of the alphabet until the letter “p” is reached.

 

Figures 18 & 19: SQLi to check if the first letter of password is 'p'
and the response stating it is a 'p'

 

The “Valid login” response is received, which indicates that the first letter of the password is “p.” The same logic will be applied to each position within the password string. The next example shows attempts to find the third character in the password string.

 

Figures 20 & 21: Continue iterating through passwords -
Invalid request and response
stating it is not 'paa'

 

Figures 22 & 23: Continue iterating through passwords -
Valid request and response stating it's 'pas'

 

Each time a letter is discovered, a standard login request is made to determine if the full string has been identified. This process would be repeated until each character position is identified and a successful login is achieved.

Figures 24 & 25: Login request and successful login

 

Unstructured

The above vulnerable application has been modified to use the MongoDB database to show an example of how NoSQL databases are also vulnerable to injection attacks. Similar to our first example, the login form will be used to inject code via the username field. By submitting a username and password, we can see the request that is generated to the server:

Figures 26 & 27: Login request and invalid login

 

One way to inject code into an application that uses PHP and MongoDB is to insert objects into the original query. If we append [$ne] to the form fields and set each field to 1, we get the following request/response.


Figure 28: SQLi return all results

 

As a result of the request, the [$ne] operators are inserted into the query dictionary and generate the following MongoDB query:

 

$query = {
            ‘user’: {‘$ne’:1},

            ‘password’: {‘$ne’: 1}
}

The query would be interpreted by the database as:

“Find all rows where the user is not-equal to 1 and the password is not equal to 1. The 1 in this case could have been random string that would not be expected to be contained within the database and would ultimately result in a query that is always true.”

Recommendations

The most effective way to prevent structured SQLi is to utilize parameterized queries, which prevent client data from altering the original query structure. Regardless of the characters entered, all data will be interpreted as text and submitted to the database as a value and not a portion of the query logic. User input validation should always be included as a sanitization step regardless of whether or not parameterized values are used. Input validation is extremely important if parameterized values are not used. All input variables should be validated to ensure the respective values are consistent with the expected data. Under no circumstances should user data be inserted directly into a SQL query.

With regards to unstructured databases, the mechanism for generating safe queries is very dependent on the NoSQL database in use and the programming language utilized by the web application. Because each is different, you should reference the manual for each to determine the proper way to generate safe queries.

 

Additional Posts