What is a SQL Injection Attack?
SQL Injection Definition
SQL injection is an attack that illegally manipulates a database by injecting unintended Structured Query Language (SQL) statements into an application that has a relational database (RDBMS). There are several types of SQL injection depending on the method and purpose, and from the perspective of cyber attackers, they range from stealing information, falsifying data, and investigating vulnerabilities. Although it is an old attack, it is still causing a lot of damage today, so it is one of the attacks that corporate organizations should be particularly wary of.
Relational Databases and SQL
Before we get into the explanation of SQL injection, let's first explain relational databases and SQL. A relational database is a type of database that manages application data in a table format. It is used in many business applications because of the high reliability of transactions (each individual process).
SQL is a language (database language) for operating relational databases. Operations are broadly divided into three categories:
Data Definition Language (DDL) for configuring the database
Data Manipulation Language (DML) for reading and updating data
Data Control Language (DCL) for various controls such as authority
The specifications for SQL are stipulated by ISO. Therefore, if it is a relational database, in principle, it is possible to operate it in the same way even if the provider is different.
SQL Injection Examples
One day, a cyber attacker discovers an application and makes it the target of an attack. We assume that the cyber attacker knows that a legitimate user "Ken Sato" already exists on this web application. The cyber attacker attempts to illegally log in by posing as user "Ken Sato." In the login form, the cyber attacker enters the string shown in Figure 1. When viewed from the application's perspective, the SQL statement shown in Figure 2 is generated. How is this SQL statement interpreted in a relational database?
Figure 1: Example of inputs used by cyber attackers
Figure 2: SQL statement created from the input in Figure 1
When the relational database receives this SQL statement, it first searches for a row in the USERS table where the USER NAME is "Ken Sato". Since the user name "Ken Sato" already exists, the relational database moves on to the next search condition. This is where the problem begins.
According to the search condition, the relational database searches for a user whose user name is "Ken Sato" and whose password is "blank" or "'1' = '1'". Since passwords are required in many applications, let's say that no user with a "blank" password was found this time. But what about "'1' = '1'"? This is a formula that compares whether "1" and "1" are the same. Naturally, this result is always true. Therefore, the relational database recognizes that a user exists whose USER NAME is "Ken Sato" and whose PASSWORD is "blank" or "'1' = '1'". As a result, the database server responds with information associated with the username "Ken Sato" to the application server without checking the password for that username. The application server creates a login success screen based on that information and sends it as a response to the cyber attacker's browser.
This is the basic mechanism of SQL injection. We have given a web application login as an easy-to-understand example, but database operations using SQL statements are used in many application functions. Therefore, this attack can be successful not only on the login screen, but in various situations in the application.
Types of SQL Injection Attacks
There are several types of SQL injection depending on the purpose and method
Error-Based SQL Injection
This is a SQL injection technique used to explore application configurations and vulnerabilities. An error is generated by intentionally entering invalid input into the application, and the details of the targeted system are explored based on the error message. Although the possibility of directly falsifying or leaking data using this technique is low, cyber attackers may use the information obtained using this technique to launch attacks targeting vulnerabilities or other SQL injection attacks described below.
UNION Injection
This is an SQL injection technique that uses the UNION operator, a type of SQL, to reference arbitrary data. The UNION operator is an operator that combines the results of multiple SELECT statements. If a cyber attacker adds a new SELECT statement beginning with the UNION operator to a SELECT statement issued by an application, it becomes possible for the application to obtain data that is not intended by the application. If the attack is successful, the cyber attacker can obtain arbitrary data at the database table level. For this reason, it is a method that can cause particularly great damage among SQL injections.
Blind SQL Injection
This is a SQL injection technique that sends an SQL statement to an application and explores the structure of the application by observing differences in behavior rather than the direct results. As with error-based SQL injection, the possibility of directly falsifying or leaking data using this technique is low, but cyber attackers may use the information obtained using this technique to launch attacks targeting vulnerabilities or other SQL injection attacks.
Second Order SQL Injection
This is a SQL injection technique in which a cyber attacker sends a crafted SQL statement to an application that is ineffective at the time of execution and then executes it later. Because this technique is executed in an environment where direct user access is not expected, in the worst-case scenario, it could lead to database-level intrusions, such as changes to database settings and permissions.
How to prevent SQL Injection Attacks
Today's applications are composed of various elements. Therefore, appropriate measures for each element, in other words, multi-layered defense, are necessary.
These are the main measures to prevent damage caused by SQL injection.
Database-level measures
One countermeasure at the database level is to optimize user privileges on the database. As mentioned above, relational database operations are broadly divided into three categories: Data Definition Language (DDL), which configures the database; Data Manipulation Language (DML), which reads and updates data; and Data Control Language (DCL), which handles various controls such as privileges. However, in many cases, DML such as SELECT clauses are what most applications normally use. By restricting privileges for other operations, it is possible to prevent unintended data deletion and setting changes.
Application-level measures
There are a variety of application-level countermeasures available.
Using placeholders
It is possible to prevent SQL injection by creating SQL statements using placeholders. Placeholders mechanically assign input values to SQL statements prepared in advance by the application, and even if invalid values are provided as input to the application, they are invalid values, and the creation of the final SQL statement is halted
Proper escaping of input values
Symbols and character strings that have special meanings in SQL statements are escaped and treated as normal character strings to prevent unintended relational database operations. Examples of characters that can be escaped include "` (single quote)", "; (semicolon/meaning: the symbol in question is considered the end of the SQL statement)", "-- (two consecutive hyphens/meaning: the symbol after the symbol is treated as a comment)", and "UNION (UNION clause/meaning: combines the results of two or more SELECT statements)". In addition, in the case of numbers such as "1", it is necessary to explicitly define whether they are treated as "numbers" or "characters" in the database and convert them appropriately.
Hide errors
Error messages displayed by applications can provide a lot of information to cyber attackers. As described in "Error-Based SQL Injection", attackers can use these messages to launch further attacks. When developing applications, it is important not to directly display error messages that could lead to an understanding of the internal environment of the system, not just the database.
Applying patch programs to package systems
If you are using a package system, we recommend that you apply the patch officially provided by the vendor as soon as possible. This will help protect your system from various vulnerabilities, including SQL injection.
Network-level measures
Measures at the network level include the use of IPS (Intrusion Prevention System) and WAF (Web Application Firewall). IPS is a solution that monitors the network and detects/blocks malicious communications. WAF is a solution that protects web applications, and by inspecting web communications, it detects/blocks attacks that target vulnerabilities in web applications.
Measures at the overall operating environment level
After implementing the above-mentioned measures, it is possible to objectively evaluate the effectiveness of the measures and any deficiencies by conducting external penetration tests and vulnerability assessments.
Difference with Cross-site Scripting
While both vulnerabilities can be caused by malicious code or data sent by website/app users and administrators, they differ in terms of impact. CSS/XSS typically causes disruptions on the client or visitor side and can be used to hijack sessions, deface websites, download malicious content, and redirect URLs. On the other hand, injections severely affect the server side and can lead to data loss and other consequences.
Conclusion
Although SQL injection is an old attack, there are still many confirmed cases of it causing great damage in recent years. Therefore, it is still an attack that organizations should be wary of. If a technique such as UNION injection is used and the attack is successful, it may lead to a large-scale information leak. However, by taking appropriate measures, it is possible to prevent such damage before it occurs. As a security measure for corporate organizations, in addition to the measures from the perspective of defense in depth mentioned above, we recommend that safety assessments are regularly carried out, such as external penetration tests and vulnerability diagnosis.