What are SQL Injections?

SQL Injection is a type of attack that can be very harmful for a database. It allows attackers to insert unexpected SQL code through user inputs with the intention of accessing and altering data from a database. SQL Injection (SQLi) is one of the most common vulnerabilities application systems have, in some cases making the database completely available to the attackers.  The reason such attacks are seen so often is because of insufficient validation of user inputs.

In this extended abstract, I will present various types of SQL Injection with small query examples, depict defensive code practices and briefly describe detection and prevention techniques.

SQL Injection Attacks

An SQL Injection attack happens when inserting malicious SQL code into an already existing query. SQL Injection attacks can be categorized based on injection mechanisms or attack intent.

Injection mechanisms exploit an application’s vulnerability through different inputs. First-order injection consists of injecting the unwanted SQL command through user inputs (in form submission sent via GET & POST requests), cookies (alter the cookie’s content) or server variables (attacks can be introduced directly into headers or environment variables). Second-order injection introduces data that does not launch the attack when first enters the database, but waits for when that code is being used in another, key part of the call.

There are many reasons for why one would tamper with a database – attack intents – such as identifying injectable parameters, database finger-printing (uncovers the database’s type and version, allowing for database-specific attacks), determining the database schema (retrieves information about tables and column data types), extracting or performing create/read/update/delete operations on data, denying the service (either shuts down the database, or locks or drops tables), privilege escalation (focuses on exploiting the database user privileges), bypassing authentication and executing remote commands (stored procedures or functions).

In continuation, the SQL Injection attack types will be presented in a table, together with the associated intents, short descriptions and, in some cases, query examples. The malicious queries are represented underlined.

Type Intent(s) Description Example
Tautologies Bypassing authentication

Extracting data

Identifying injectable parameters

Inject conditional statements that always evaluate to true, usually in WHERE conditions, resulting in extracting all rows.

Attacks should also take into consideration implementation code, best cases being when the code performs an action with the returned data.

SELECT * FROM users WHERE username = “” or 1=1 — AND password = “”
Illegal/Logically Incorrect Queries Injectable parameters

Database finger-printing

Extracting data

This is usually a preliminary step of a bigger attack, that aims to gather information by making the system to error. It relies on the – previously helpful – overly descriptive error messages to discover database-related information as well as identifying vulnerable parameters.
Union Queries Bypassing authentication

Extracting data

Use the UNION operator to change the returned data set; following the operator, the attacker has freedom to do anything with the second query. SELECT * FROM users WHERE login = “” UNION SELECT phone_no from personal_information WHERE user_id = 1234 – AND pass = “”
Piggy-Back Queries Extracting data

CRUD operations

Denying service

Executing remote commands

Attempt to inject additional queries into the original one (piggy-back on the original query). Can be very harmful, as anything can be inserted as subsequent queries. SELECT * FROM users WHERE login = “”; DROP TABLE users –” AND pass = “”
Stored Procedures Privilege escalation

Denying service

Executing remote commands

Try to execute already existing stored procedures to determine the back-end database. It is essentially a piggy-back attack using stored procedures.
Inference Injectable parameters

Extracting data

Determining the database schema

This type of attack relies on the absence of error messages. It uses the answer of a true/false statement to note in which case the application behaves as intended by the developer and in which it does not. There are two techniques for inference attacks.
·       Blind injection Observes whether the page behaves normally, or changes significantly based on a true/false question.
·       Timing attacks Observes time delays in the response of the database, using the WAITFOR keyword SELECT * FROM users WHERE login = “” or 1=1 WAITFOR DELAY 00:00:10 – AND pass = “”

Defense, Detection and Prevention

It is of utmost importance to have a system that is able to defend itself from the different types of attacks. Subsequently, detecting and preventing such attacks is equally crucial, as knowing what to expect leads to increased defenses and better preventing mechanisms.

The main cause of SQLIAs is insufficient user input validation. This happens because implementing system security is a human-based task, meaning it is prone to errors (fatigue, lack of experience etc.). Updating and patching of servers regularly highly contributes to ensuring security, but it is dependent on having well written and tested code that prohibits injection. However, in large systems, ensuring security in legacy code is tedious and requires many resources, as it can be quite extensive of a task too.

Some of the best code practices that eliminate SQLi vulnerabilities include various ways of input validation methods. Input type checking encourages implementing even the simplest checks, like not accepting strings in number fields. Input encoding makes sure that any meta-characters are specially encoded and interpreted by the database correctly. The developers can create validation routines that check for good input, rather than bad. Because the accepted cases are already known, positive pattern matching is a very safe form of defense. Lastly, identifying all input sources also layers on system security.

Researches have encompassed a collection of techniques and tools to detect and prevent SQL Injection vulnerabilities and attacks. Not using dynamic queries, integrating input validation or use of parameterized database queries are some common prevention techniques, but there are also some more, automated ones, that could be used to greater increase security. WAVES is a black-box testing technique that uses a web crawler to find injection vulnerable points, which it then attacks back, using machine learning to improve its attacks. JDBC-Checker, which was not initially built to detect SQLIAs, can “be used to prevent attacks that take advantage of type mismatches in dynamically-generated query strings” [3]. The two the research papers used as base to write this paper enumerate many more detection and prevention techniques.

Conclusion

Given that there are many types of SQL Injection Attacks, it takes considerable effort to implement and maintain high system and database security. Unfortunately, the lack of it leads to identity theft, loss of confidential information or fraud, having disastrous repercussions both for the system stakeholders and clients. In the past, as much as 3 billion user accounts were accessed from Yahoo! databases, putting into perspective the severity of SQLIAs.

To sum up, having good quality and highly tested coded, more importantly input validation, is the only approach one should have when it comes to minimizing the risk of SQL Injection attacks and vulnerabilities.

References
[1] W. G. J. Halfond, J. Viegas, A. Orso, “A Classification of SQL Injection Attacks and Countermeasures”.
[2] “Beyond Security,” [Online]. Available: https://www.beyondsecurity.com/about-sql-injection.html.
[3] A. Tajpour, S. Ibrahim, M. Masrom, “SQL Injection Detection and Prevention Techniques”.
[4] T. Armerding, “CSO,” 2018 January 26. [Online]. Available: https://www.csoonline.com/article/2130877/data-breach/the-biggest-data-breaches-of-the-21st-century.html.