Blog

Did you know?

"SQL injection" is a term just about everyone associates with computer hacking, whether it be in the news, TV shows or movies, anyone that works in or around computer security likely knows that an SQL injection vulnerability can have devastating impacts while not fully understanding it. In today's blog, we discuss exactly what SQL injection is... alongside its common ramifications.

Let's get started:

Firstly, What is SQL Injection?

Databases are used to store data such as web application login credentials and e-commerce inventory. The data is interacted with using SQL queries and is most commonly used in web applications to validate and authenticate a login request or perform a product search. Keywords and characters are used to form SQL queries in order to retrieve or modify data in a database.

In an SQL injection attack, an adversary can insert combinations of keywords or characters (such as a single quote, double quote, asterisks, semicolon or round brackets) to manipulate the queries used by an application to retrieve or modify information in unintended ways.

It is a common issue and has held the #1 spot in the OWASP Top 10 web application vulnerabilities across several revisions. It is easy to detect and exploit in many cases, however, compensating controls and mitigations can make it more difficult to exploit.

The Impact and Risk of SQL Injection

With no mitigating controls, SQL injection can leave the application at a high risk of compromise resulting in an impact on the confidentiality, and integrity of data as well as authentication and authorization aspects of the application. An adversary can steal sensitive information stored in databases used by vulnerable programs or applications such as user credentials, trade secrets, or transaction records.

SQL injection vulnerabilities should never be left open; they must be fixed in all circumstances. If the authentication or authorization aspects of an application is affected an attacker may be able login as any other user, such as an administrator which elevates their privileges.

Technical Trenches

This section is geared towards application developers or system administrators who are seeking to understand why SQL injection vulnerabilities exist, how they work, and how to properly mitigate them. For those not looking to get deep in technical details, you can skip to the Remediation section.

Types of SQL Injection

There are many different ways to exploit SQL injection each requiring a different level of expertise and knowledge ranging from beginner to expert. The three common types of SQL injection are Union-based, Error-based, and Blind SQL injection.

• Union-based SQL Injection involves using the union statement keyword to combine queries together to retrieve information from a database. This type of injection typically can only retrieve information and cannot be used to modify, delete or insert additional data

• Error-Based SQL Injection is typically easy to exploit as it returns verbose error information when a SQL query fails such as when the syntax is incorrect. An attacker can learn the exact query being executed by the SQL server and use this information to craft malicious requests. Depending on database configurations this may be able to be used to insert or modify data in addition to retrieval

• Blind SQL Injection is typically harder to exploit as there is no information returned directly to the attacker, but it is still exploitable and can sometimes be just as easy to exploit as union-based and error-based attacks thanks to tools such as SQLMap. This type of vulnerability requires an attacker to use out-of-band techniques such as Boolean-based or time-based queries in order to extract information from the database in a series of complex, logical statements

SQL Attack Scenario

Let’s walk through a fictitious example of a grocery store search feature that is vulnerable to SQL injection, that mimics real-world scenarios that Packetlabs has encountered during engagements.

The home page of the web application features a search bar, regular users would enter in products such as apples, oranges, milk, or chocolate and be returned a list of products that match their search criteria.

To discover an SQL injection vulnerability, an attacker would enter key characters that affect the structure and syntax of a SQL query such as a single quote, double quote, asterisks, semicolon or round brackets systematically and observe the differences in the response from the web application (looking for interesting behaviour such as error messages). Once a vulnerability is identified, the attacker would try to determine details about the application and database that can aid in their attack. Details such as:

• the database type (MySQL, MSSQL, etc)

• back-end languages used (PHP, Python, Ruby, etc)

• the structure or exact syntax of the query.

Once this information is obtained they can begin crafting specific queries to extract information.

The narrative below describes a scenario where an attacker has identified an error-based SQL injection vulnerability and uses the vulnerability to retrieve the entire contents of the products database.

SELECT PRODUCT_ID PRODUCT_NAME FROM Products WHERE PRODUCT_NAME LIKE ‘Apples’;

The SQL query is the backend code used to search the database to return search results to a user that enters the word Apples into the search bar. The key portions relevant to the SQL injection attack are WHERE PRODUCT_NAME LIKE ‘Apples’; this tells the database to find ALL products that contain the string ‘Apples’ in the name.

The screenshot below shows the search bar of the web application and a malicious statement that will exploit the SQL injection vulnerability. The meaning of this query is explained below.

SQL Injection Illustration

The injected string is contained in the search URL parameter. Note the plus (+) characters were added by the browser in place of the space character. This is not of concern for this article.

SQL Injection HTTP Request

The resulting query that is executed is as follows:

SELECT PRODUCT_ID PRODUCT_NAME FROM Products WHERE PRODUCT_NAME LIKE ‘’ OR 1=1; # ‘;

The red highlighted characters are the malicious string and the altered SQL query the attacker has crafted. The single quote will escape out of the ‘Apples’ portion of the original query and affect the structure of the query. By injecting the logical expression ‘or 1=1’, the database will now return all product names as the attacker has effectively said find all product names that exist in the product table. The green characters have been commented out by the pound sign (#) the attacker injected; this prevents the SQL software from returning a syntax error.

Remediation

SQL injection has been around for a long time and, as such, there are multiple ways to patch these vulnerabilities. In short, any data that comes from a third-party source such as user input should not be trusted and assumed to be malicious. From a technical standpoint, user input validation can be done through the following techniques.

• Client-side AND most importantly server-side validation through the use of a whitelist

• Use Prepared SQL Statements with parameterized queries

• Use stored SQL procedures

• Escape (a type of encoding) all user-supplied input.

Organizations should look into implementing a Systems Development Life Cycle (SDLC) policy that adopts secure coding practices while ensuring static and dynamic code analysis is regularly performed. A final penetration test should also be considered prior to going live to ensure all issues have been addressed.

When sanitizing and encoding user input ensure a whitelist is used, opposed to a black list. A blacklist requires discovering every single character that can be malicious and will require retuning the blacklist as other security issues are identified and even as new exploits and exploit techniques are discovered. Ensure all whitelisting and sanitization is performed on the server-side, in addition to any client-side validation because the client-side code is controllable by the user it can be disabled, removed or bypassed and is ineffective.

For in-depth examples and specific implementations of each of the suggested remediation techniques we suggest review the following resource OWASP SQL Injection Cheat Sheet.

Conclusion

The Packetlabs team is composed of highly trained and experienced ethical hackers that focus and excel at detection and exploiting advanced vulnerabilities that are often overlooked and go undetected. Our team members have some of the highest regarded training when it comes to penetration testing including the Offensive Security Certified Professional (OSCP), Offensive Security Certified Expert (OSCE), GIAC Web Application Penetration Tester (GWAPT), and GIAC Exploit Researcher and Advanced Penetration Tester (GXPN) certifications.

Contact us to learn more about how we can help.