Exploring SQL injection: What it is in depth

Did you know that the cause of 80% of hacking incidents is stolen or reused login information? Passwords, credit card numbers, and other sensitive data can be stolen with a successful SQL injection attack. In 2022, SQL injection accounted for 33% of global critical web application vulnerabilities.

SQL injection attacks are a common issue that SOCs face, and they're constantly on the hunt for ways to remediate them. Creating a secure code would prevent the introduction of a harmful code into an application. A lack of strict separation between user-provided (or external) input and program instructions (i.e., code) is the root cause of injection attacks. Therefore, a data snippet can easily be injected with a malicious code. Due to the absence of this separation, an application may run a malicious code created by an attacker.

In this blog, we will cover:

• What is SQL injection?
• Damages caused by SQL injection
• The workflow of SQL injection attack
• Types of SQL injection
• Best practices to prevent SQL injection

What is SQL injection?

SQL injection (SQLi) is a popular attack method that uses malicious SQL code to manipulate backend databases and access data that was not meant to be displayed. Various information, including private customer information, user lists, or sensitive corporate data, may be included in this data.

Figure 1: SQL Injection

Damages caused by SQL Injection

By successfully carrying out a SQLi, attackers can:

• Read, edit, and delete confidential or private files on the server, including:
  • Emails
  • Files containing passwords
  • SSL certificates
  • Databases with third-party data, which may contain sensitive information such as credit card numbers, addresses, names, and telephone numbers
  • Financial information such as invoices, payroll, and agreements
  • Private images or videos
• Attack or access other computers and servers internally
• Deliver malware to users
• Create new users, and monitor traffic

The workflow of a SQL injection attack

A SQL injection attack's objective is to change or steal data from a database that the application communicates with. These control characters provide access to or retrieve data components from a backend database server when used with standard SQL commands (such as SELECT, FROM, and DELETE).

Here is a representation of a typical SQL injection attack:

1. Attackers identify input fields on a web application that communicates with a database. Text boxes, search bars, login forms, and other user input devices that are used in database queries can all be used as these input fields.

2. The attackers must comprehend the structure of the SQL queries that are being utilized by the application in order to carry out a SQL injection. To collect this information, they may examine the HTML source code, intercept network traffic, or carry out other types of reconnaissance.

3. In the input field, the attacker inserts the malicious SQL code. This code's purpose is to alter the SQL query that the application is running. Typically, the injected code is included in the input as a parameter, such as a username or search term.

4. Attackers frequently employ SQL injection to bypass authentication safeguards. For instance, the attacker might be able to log in without using a legitimate username and password if the inserted code causes the query to always return true (for example, "1"="1").

5. The attackers will extract private information from a database. They can retrieve data they shouldn't have access to by inserting a SQL code that modifies the query's functionality. For instance, they can retrieve usernames, passwords, credit card numbers, or other private information.

6. SQL injection can be used to edit or delete data in the database, in addition to extracting it, depending on the permissions of the database user account used by the application.

7. Attackers often purposefully insert SQL code that results in errors. These errors could provide useful insights into the design of the database or aid attackers in improving their injection methods.

8. Attackers can conduct attacks using strategies like blind SQL injection in situations where error messages are not returned to them. This entails injecting code that compels the program to behave differently depending on whether certain criteria are true or untrue. Based on these conditional actions, the attacker can then draw conclusions about the target.

As a result, SQL injection attacks may result in serious consequences like data breaches, loss of data, and unauthorized access.

Figure 2: SQL Injection attack working flowchart

Types of SQLi

There are 3 types of SQL injection attacks:

• In-Band SQLi
• Blind SQLi
• Out-of band SQLi

Figure 3: SQL Injection attack types

1. In-band SQLi

In-band SQL injection, sometimes referred to as Classic SQL injection or Error-based SQL injection, is one of the more prevalent kinds of SQL injection attacks. It takes place when a hacker accesses a database's data via the same channel that was utilized to begin the attack. In other words, the attacker can instantly observe the results of the SQL query they inserted through the application's responses or error messages.

Here's how in-band SQL injection typically works:

• User input form: The attacker locates a web application that accepts user input and utilizes it in the form of SQL queries without sufficient validation and sanitization.
• Malicious SQL Code Injection: The attacker inserts malicious SQL code into the parameters or input fields that communicate with the database. This code is intended to manipulate the SQL query that is being executed.
• Error-Based Exploitation: The attacker can extract information from the database indirectly by injecting SQL code that triggers errors. For example, they may use an injection like '' 'OR 1=1 --" to make the query always evaluate to true, revealing data.
• Data Extraction: The attacker might extract confidential data from the database by using error messages or application responses. This might contain details on database structure, usernames and passwords, and other information.

Here's an example of an in-band SQL injection:

1. Suppose a web application has a login page that takes a username and password and constructs an SQL query like this:

   SELECT * FROM users WHERE username = '<input_username>' AND password = '<input_password>'

2. In order to take advantage of an in-band SQL injection vulnerability, an attacker might use the following username:

   ' OR '1'='1' --

3. In this case, the SQL query would become:

   SELECT * FROM users WHERE username = ' ' OR '1'='1' --' AND password = '<input_password>'

4. The rest of the query is commented out using double hyphens (' -- '), ensuring that the injected condition (' '1'='1' ') is always true.

Since the query always evaluates to true, the attacker can get in without knowing a legitimate username or password.

There are two primary categories of in-band SQL injection:

Error-based SQLi: Here, the result returned to the attacker is a database error string.

Let us consider an example:

1. We have a query as mentioned below:

   SELECT * FROM users WHERE user-id = '<input_userid>'

2. The attacker would provide the following value as the input:

   1'

3. The result of the query becomes,

   SELECT * FROM users WHERE user-id = ' 1' '

The database reports an error because of the doubled single quotation at the end of the query. The attacker might receive a similar message to the one below, if the web server is set up to show faults on screen:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax.

As a result, the attacker can concentrate on MySQL-specific assaults since the error message clearly indicates that the program uses a MySQL database.

Union-based SQLi: Here, the Union SQL clause is used by the attacker to get a result that blends valid data with sensitive data.

Let us consider an example:

1. We have a query as mentioned below:

   SELECT * FROM users WHERE user-id = '<input_userid>'

2. The attacker would provide the following value as the input:

   1=1' UNION SELECT name, dcode--'

3. The result of the query becomes,

   SELECT * FROM users WHERE user-id = ' '1'='1' ' UNION SELECT name, dcode--' '

As a result, the attacker would gain the information related to the name and dcode from the database.

2. Blind SQL Injection

When in-band approaches cannot be utilized because of security measures or when the attacker wishes to be concealed, blind SQL injection is frequently used. In a blind SQL injection attack, the results of the injected SQL query are not immediately visible to the attacker in the application's answers or error messages. Instead, the attacker uses the application's behavior to detect whether the SQL query was injected correctly or incorrectly, and then they take data from the database based on these true/false criteria.

Depending on how the application and database server behave, the attacker might be able to:

• Verify if any other SQL injections are feasible
• Learn more about the database's structure
• Get data from the database

There are two primary categories of a blind SQL injection:

Boolean-based Blind SQL Injection: The attacker often employs conditional expressions (e.g., IF, CASE, AND, OR) to design SQL queries that indirectly retrieve information from the database. In this type of blind SQL injection, the attacker injects SQL code that depends on the application's response to true/false conditions.

Let us consider an example of a hospital's webpage. This displays the details about the patients once they enter the required credentials.

1. The following link displays the details about patient number 22, which are retrieved from the database.

   http://www.sthospitals.original/pid.php?id=22

2. The SQL statement used for this request would be:

   SELECT code, case, ward, paymentdetails FROM patient_table WHERE id =22

3. The attacker may manipulate the request by altering the URL to:

   http://www.sthospitals.original/pid.php?id=22 and 1=2

   (Note: '' AND" operator will consider the results of both the statements. Even if one of the statements is false, the result will be taken as FALSE)

4. The SQL statement changes to:

   SELECT code, case, ward, paymentdetails FROM patient_table WHERE id =22 and 1=2

   (Note: 1=2 is not true, thus the result of this condition will always be taken as FALSE)

   This will cause the query to return FALSE and no details will be displayed.

5. The attacker then proceeds to change the request to:

   http://www.sthospitals.original/pid.php?id=22 and 1=1

6. The SQL statement changes to:

   SELECT code, case, ward, paymentdetails FROM patient_table WHERE id =22 and 1=1

   (Note: 1=1 is true, thus the result of this condition will always be taken as TRUE)

   This returns TRUE, and the details about the patient with ID as 22 will be displayed. This is a clear indication that the page is vulnerable.

Time-based Blind SQL Injection: In this case, the attacker will inject SQL code that causes the database to perform a time-intensive operation. By measuring the time it takes for a response to arrive, the attacker can infer whether the injected condition is TRUE or FALSE.

Based on the previous example, first, the attacker would find out the actual response time of the web server. Then, they would issue the following request with a "sleep" operation:

http://www.sthospitals.original/pid.php?id=22 and if(1=1, sleep(10))

(Note: SLEEP pauses the MySQL process for a given duration)

The web application is identified to be vulnerable if the response is delayed by 10 seconds.

3. Out-of-band SQL Injection

In out-of-band SQL injection attacks, the attacker uses the vulnerable application to trigger actions that send data to a location they control. This data transfer can happen through various means such as DNS requests, HTTP requests, or other network protocols.

Here is an example of out-of-band SQL injection attacks:

DNS Exfiltration: In this instance, the attacker inserts malicious SQL code that, when the application executes it, causes the database server to make DNS requests to a domain and causes the attacker to gain control of the domain. These DNS queries encrypt the data stolen from the database and share information with the attacker to recreate the database.

1. Let us consider scenario where without sufficient validation or sanitation, an online application that uses user input directly in SQL queries is vulnerable to SQL injection.

   user_input = get_user_input()

   query="SELECT*FROM database WHERE name = "'+user_input+'";"

   execute_query(query)

2. The attacker tries transmitting data out of the target network over a separate communication channel rather than attempting to directly retrieve data through the application's response. In this case, the hacker steals data through DNS requests.

   ' UNION SELECT DNS_QUERY(LOGS)FROM DUAL--

3. The UNION operator combines the original query with the DNS query of the domain they are trying to control.

As a result, the attacker will get the data related to the logs that is available in the particular domain.

The attacker can now inspect their DNS server logs to extract the data that was exfiltrated.

Best practices to prevent SQLi:

• Use parameterized queries (prepared statements): Use the parameterized queries offered by your programming language or database framework. Guarantee that user input is handled as data and not as SQL code that can be executed. This ensures that it isolates the query structure from the actual data.
  • Example: cursor.execute("SELECT * FROM users WHERE username = ?", (user_input,)) - In this query, "?" serves as a placeholder for data values that will be supplied later.
  • Avoid using Dynamic SQL.
• Input validation and sanitization: Use stringent input validation and sanitization procedures to filter out or clean information that doesn't follow the expectations of your application.
• Escaping user input: If you cannot use prepared statements, escape user input before incorporating it into SQL queries. Use the appropriate escaping functions provided by your programming language or framework.
  • This can be done using backslashes, quotes, or other symbols that can indicate that the input is a string or a literal value, not a code or a command.
  • Ex: "SELECT * FROM users WHERE username = '$user_input'";
• Least privilege principle: Restrict the privilege access of the database to only the required tables and operations.
• Web Application Firewalls (WAFs): WAFs can inspect the incoming traffic to identify any malicious patterns.
• Error handling and logging: Avoid exposing detailed error messages to the users as these can provide the attackers with the details of the database structure.
• Regular patching and updates: Keep your web server, database management system, and application frameworks updated with the most recent security updates to reduce risks.
• Security testing and code reviews: Conduct regular security testing, including vulnerability assessments and penetration testing, to identify and address SQL injection vulnerabilities. Additionally, perform code reviews to ensure that secure coding practices are followed.
• Whitelisting: Instead of blocklisting known harmful characters or patterns, consider using whitelisting, which lets you designate permitted input patterns.
• Use an Object-Relational Mapping (ORM) framework: Use an ORM framework that automatically manages parameterized queries and data sanitization while abstracting database interactions.
• Server hardening: Implement server hardening practices to reduce the attack surface of your application's infrastructure.

How can a SIEM solution aid in tackling such attacks?

We can clearly understand that to detect and mitigate a SQLi attack, security analysts need a comprehensive network security tool that analyzes and correlates log data from your web servers and databases. With the help of a SIEM tool like ManageEngine Log360 you can,

• Audit data manipulation language and data definition language activity.
• Track database server account changes.
• Monitor database server activity.
• Track web server usage, errors, and other important events of interest.
• Detect and be alerted to suspicious activities and security attacks, such as an SQL injection or cross-site scripting, that target these applications.

If you want to protect your organization from such attacks, sign up for a personalized demo of ManageEngine Log360, a unified SIEM solution with data security and cloud security capabilities.