What exactly is SQL injection and how can I protect myself against it? What exactly happens during such an attack?

SQL injection (SQLi) is one of the most common attacks on databases via web applications. We will now look at how these attacks are structured and what you can do to protect yourself against them.

  1. What are the requirements necessary for such an attack?
  2. Types of SQL Injection
    1. Blind SQL injection:
    2. Out-of-band injection
  3. An Example:
  4. How to prevent SQL injection attacks
  5. Conclusion:

Relational database management systems (RDBMS) such as Oracle, MySQL, PostgreSQL and Microsoft SQL Server are among the most popular in the market according to the current DB Engines ranking. Since they are considered reliable and avoid inconsistencies in the data sets, they have been used as an established database standard in most companies for decades. However, variants of this attack technique can also be used on No-SQL systems. Some No-SQL systems also support SQL queries and are therefore vulnerable to such attacks.

https://youtu.be/4wLrMHwPEdw

To query and edit the data in an RDMS, the database language Structured Query Language (SQL) is used. User input is also processed. When new data needs to be entered or when the existing data needs to be searched. Here, the required parameters are filled with the user’s input to arrive at a final SQL string. In general, the following process then takes place: A user enters the data into a form that is to be used to specify the search. These input values are then passed to the server and added to an existing SQL fragment. The resulting SQL string is subsequently sent to the RDBMS. The response returned by the RDBMS is then processed on the server and sent to the client that initiated the request in the desired form.

In this way, the data stored in the database is vulnerable to so-called SQL injection (SQLi), which injects arbitrary code into queries. This makes it possible to read or change information without permission. In the worst case, the intruders gain complete control over the entire RDBMS.

What are the requirements necessary for such an attack?

Successful exploitation of a SQL injection requires:

  • a SQL database that is being attacked, for example, MySQL, Microsoft SQL Server, Db2
  • an application program into which attackers can enter data, for example, a login mask, a product search in an online shop or a contact form on the web
  • that the application program passes this data on to the database
  • a programming error in the application program when passing on the data

The programming error is that the application program does not pass the input data to the database as pure data but instead creates the database query from this data. This allows an attacker to attempt to control parts of the database query precisely.

In the database query language SQL, some characters have special meaning, in particular:

  • The apostrophe to enclose text data
  • The quotation mark to enclose text data (some databases only)
  • The backslash to avoid interpreting the following character as a special character
  • The semicolon to separate one database command from the following database command
  • A double hyphen to ignore the rest of the line (comment)

Document-oriented NoSQL databases can also be affected by this security issue:

  • The curly and square brackets that enclose a JSON object or array

An attacker uses explicitly these characters listed here to carry out the attack.

Types of SQL Injection

No matter what type of SQL injection it is, the attacker injects arbitrary SQL code into the database query of a web application. There are different approaches here.

The simplest form of attack occurs via user input. Web applications typically accept information through a form, such as text input fields, that are not subject to fixed formatting. Fixed formatting, on the other hand, is an ISBN number that can be easily checked to ensure that it is in the correct form. The front end then forwards the input to the database in the backend for processing. If the web application does not sanitize the input, there is the possibility of deleting, copying, or modifying database content via injected SQL input.

Attackers can also modify cookies to infect the web application query. Cookies store client-state information on the local hard drive. Typically, web applications load cookies to process this information. A malicious user or malware can modify it to inject SQL commands into the backend database. The same is possible via server variables such as HTTP headers. Fake headers containing arbitrary SQL can inject this code into the database if the web application does not sanitize this input.

Blind SQL injection:

The blind SQL injection type includes attacks on web applications that do not actively defend against SQLi but do not visibly display the injection results. Instead, they either show no obvious answer or, for example, show general error messages that the syntax of the SQL query is incorrect. In this case, the page provides no data but looks slightly different depending on the results of a logical statement inserted into the legitimate SQL.

In this method, the information is not identified directly but through a series of true-or-false queries extracted from the database. This method is considered very time-consuming. However, various tools can automate the attack once the vulnerability and the desired information are found.

Out-of-band injection

This attack is slightly more complex and can be used by attackers if they cannot achieve their goal via a single direct query-response attack. Typically, an attacker issues SQL statements that, when received in the database, cause the database system to connect to an external server that the attacker controls. This allows the attacker to retrieve data or control the behaviour of the database.

A second-order injection is a type of out-of-band injection attack. In this case, the attacker provides an SQL injection that is saved and executed by a separate database system behaviour. When the secondary system behaviour occurs (a temporary job or an action triggered by a typical administrator or user use of the database), and the attacker’s SQL injection is executed, “contact” occurs with a system the attacker controls.

An Example:

An example of a SQL query that searches a system for items from a specific category is:

SELECT ID, title, lyrics FROM songs WHERE category = ‘Searched Category’;

This query consists of the following components:The capitalized words are keywords in SQL and have a fixed meaning that cannot be changed. The lowercase words are the names of tables, rows or columns in the database. Text in single quotes is full text. The text “Searched Category” is just a placeholder; The category you are looking for must be entered at this point in the database query. If the category you seek does not contain any of the special characters mentioned above, it could be inserted directly into the example query.

However, if the desired category is, for example, Rock_’n’_Roll and therefore contains an apostrophe, naively replacing the wildcard results in this SQL query:

SELECT id, title, lyrics FROM songs WHERE category = ‘Rock ’n’ Roll’;

What is striking here is the double meaning of the apostrophe: on the one hand as a separator and on the other as a literal symbol. This double meaning is easy for people to recognize and therefore put into the correct context. However, from the database’s perspective, this query cannot be interpreted meaningfully because the query is interpreted as category = “Rock”, followed by a word n and the further text “Roll” in apostrophes. The database cannot do anything with the word n and returns an error for this query.

In an SQL injection, the attacker changes the database query by selectively selecting the input data still to have a valid structure but a different meaning. For example, the seemingly nonsensical search term asdfasdf’ OR id < 100 OR category = ‘sdfgsdfg leads to this database query:

SELECT ID, title, lyrics FROM songs WHERE Category = ‘asdfasdf’ OR id < 100 OR Category = ‘sdfgsdfg’;

This query is completely correct from a database perspective; it contains 3 individual conditions, at least one of which must be true. The only purpose of the first and third conditions in this example is to ensure that the SQL query remains well-formed and categories of that name are unlikely actually to exist in the database. This leaves id < 100 as the only relevant condition. By cleverly choosing the search term, the original SQL query, which only returns articles from a certain category, has become a structurally different SQL query, which now searches for the articles based on their ID, regardless of the category.

This modified SQL query may allow attackers to view data from the database that is not intended for them. Depending on the case, the attacker can use additional targeted search terms to change or delete data in the database.

The programming error with an SQL injection is to copy the search term 1:1 into the database query without considering the special meaning of the apostrophe and similar characters.

How to prevent SQL injection attacks

The following suggestions can help prevent a successful SQL injection attack:

  • Do not use dynamic SQL : Avoid including user input directly in SQL statements. Preferably choose prepared statements and parametric queries, which are much more secure. Stored procedures are also typically more secure than dynamic SQL.

  • Clean up user input: Mask all characters that should be masked. Check whether the data type entered matches the expected data type.

  • Do not leave confidential data in plain text: Encrypt all personal/sensitive data stored in the database. Secure the encrypted hash passwords using the “salting” method. This adds another layer of protection in case the attacker successfully obtains sensitive data.

  • Further, restrict database access and permissions: Keep the capabilities of individual database users to an absolute minimum. This limits what an attacker can actually do if they gain access.

  • Avoid showing database errors directly to users: Attackers can use these error messages to obtain information about the database.

  • Use a Web Application Firewall (WAF) for web applications with database access: It protects web-facing applications. It can also help identify SQL injection attempts. Depending on your setup, it can also help prevent SQL injection attempts from reaching the application (and, therefore, the database).

  • Use a web application security testing solution to test web apps that interact with databases routinely: This makes catching new bugs or regressions that might allow SQL injection easier.

  • Update your databases to the latest available patch: This prevents attackers from exploiting known vulnerabilities or bugs from older versions.

Conclusion:

SQL injection is a popular attack method for attackers. Take proper precautions, such as: B. Data encryption, protecting and testing the web applications and updating them to the latest patches. Security is an ongoing process and precautions can be taken at every stage of development.

Happy coding

Sven