SQL Injection 101: Types, Examples, and Prevention

How SQL Injection Attacks Work

SQL injection attacks exploit vulnerabilities in the way applications construct SQL queries. Here's a detailed breakdown of the stages involved:

1. Identifying vulnerable inputs: Attackers first probe the application to locate input fields that interact with the database. These include login forms, search boxes, comment sections, or URL query strings. They test these inputs by entering special characters (e.g., ', ", or ;) and observing the application's behavior. If an error message reveals database details or indicates improper handling of inputs, the attacker confirms a potential SQL injection vulnerability.
2. Crafting malicious queries: Once a vulnerable input is identified, the attacker crafts SQL payloads to exploit it. For example, in a login form, submitting ' OR '1'='1 can manipulate an SQL query to bypass authentication checks by always evaluating the condition as true. The complexity of these payloads varies based on the attacker’s goal, ranging from extracting data to altering database structures. Knowledge of the database type (e.g., MySQL, SQL Server) helps tailor the queries for maximum impact.
3. Exploitation: The attacker injects the malicious payload into the vulnerable input field, which the application processes without proper sanitization. This allows the SQL code to execute directly on the database. Depending on the input point and query type, this stage may provide access to confidential information, allow modifications to existing data, or even delete entire tables. Attackers may also exploit this stage to identify administrative accounts or obtain hashed passwords for cracking.
4. Extracting data or escalating access: After gaining access, attackers often enumerate database tables, columns, and data types using SQL commands like SELECT or system-specific metadata queries. For example, they might use techniques like UNION SELECT to combine results from multiple tables or extract information like user credentials. If privileged access is obtained, attackers may escalate their control by creating backdoors, altering security settings, or executing system-level commands via database functions.
5. Post-exploitation activities: The final stage depends on the attacker’s objectives. They might exfiltrate sensitive data such as personal information, financial records, or intellectual property. Alternatively, they could manipulate or delete data to disrupt services, deface applications, or cause reputational damage. Advanced attackers may establish persistent access by injecting malicious scripts into the database or leveraging compromised credentials.

Related content: Read our guide to Vulnerability Assessment.

Types of SQL Injection Attacks

In-Band SQLi

In-band SQL injection, the most common type of SQLi, involves a direct feedback loop to the attacker. It includes two main techniques: error-based SQLi and union-based SQLi. Error-based SQLi leverages database error responses to gather details about the database structure, aiding in constructing additional malicious queries. This exposure occurs when applications fail to properly handle and hide database errors.

Union-based SQLi exploits the UNION SQL operator to combine results from two or more SELECT statements, providing the attacker with data from multiple tables. This method requires knowledge of the database schema to ensure correct payload construction. In-band SQLi's direct nature makes it easily detectable, although it is dangerous if not accurately addressed.

Inferential (Blind) SQLi

Blind SQL injection, or inferential SQL injection, involves no visible database output, forcing attackers to deduce information via application behavior changes. Attackers employ Boolean-based and time-based techniques to infer data. Boolean-based blind SQLi uses conditional SQL statements that alter application responses without providing data directly, making detection more complex and time-consuming.

Time-based blind SQLi manipulates SQL queries to generate time delays if a condition is true, indirectly confirming data presence. By controlling application wait times, attackers unveil sensitive information without explicit error messages. Combating blind SQLi requires input validation and implementing parameterized queries, which minimize the risk of indirect data exposure derived from untrusted inputs.

Out-of-Band SQLi

Out-of-band SQL injection is less common due to its network requirements but can be effective when in-band and inferential techniques are impractical. It utilizes different communication protocols, such as HTTP and DNS, to extract data from the server. This allows attackers to perform actions indirectly, using the database to communicate results.

The success of out-of-band SQLi typically depends on features enabled in the database, like sending HTTP requests or interacting with networked services. Given its complexity and requirement for environmental vulnerabilities, it's often used in tightly secured networks. Defensive measures include disabling unnecessary features and ensuring access control.

Second-Order SQLi

Second-order SQL injection occurs when payloads are injected and stored but executed later. Attackers inject SQL code in initial interactions, affecting queries at a different processing phase. Unlike traditional SQLi, it requires understanding of how data flows through the application and where it might trigger SQL execution.

In second-order SQLi, payloads often remain latent until a given action causes them to execute, targeting internal system vulnerabilities. Preventing this requires thorough sanitation and validation of inputs at every stage, not just initial entry points. Developers must scrutinize code paths and data handling processes to identify potential second-order vulnerabilities.

Common Examples of SQL Injection Attacks

Retrieving Hidden Data

SQL Injection is often used to retrieve hidden data from a database. For example, an attacker might exploit a vulnerable search feature to bypass normal data access restrictions. Suppose a query designed to fetch results for a keyword looks like this:

SELECT * FROM products WHERE name = 'Laptop';

If the application fails to sanitize input, an attacker can manipulate the input to include a broader condition, such as:

' OR 1=1 -- ‘

The resulting query becomes:

SELECT * FROM products WHERE name = '' OR 1=1 -- ';

This forces the query to always evaluate as true, retrieving all rows from the products table instead of a subset. Such attacks expose sensitive or restricted data, including internal product listings, unpublished prices, or confidential user records.

Subverting Application Logic

Another common attack scenario involves manipulating SQL queries to subvert application logic. For example , consider a login system using the following query:

SELECT * FROM users WHERE username = 'alice_j' AND password = 'password123';

By entering a payload for both username and password such as:

' OR '1'='1

The query becomes:

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

The condition '1'='1' always evaluates as true, allowing the attacker to bypass authentication entirely. This grants unauthorized access to restricted areas of the application, potentially leading to data breaches or system compromise.

UNION-based Attacks

UNION-based SQL Injection leverages the UNION operator to combine results from multiple queries, enabling attackers to extract data from unrelated tables. For example, a vulnerable query might look like:

SELECT name, price FROM products WHERE category = 'Electronics';

An attacker could modify the input to:

' UNION SELECT username, password FROM users –

Resulting in:

SELECT name, price FROM products WHERE category = '' UNION SELECT 
username, password FROM users --';

This combines the original query’s results with data from the users table, potentially exposing sensitive user credentials. Successful UNION-based attacks require knowledge of the database schema, including the number and data types of columns in the original query. Attackers often use trial-and-error methods to determine these details, escalating the risk if input validation is insufficient.

5 Best Practices for Preventing SQL Injection Attacks

Here are some of the ways that organizations can best protect themselves against SQL injection.

1. Input Validation

Input validation helps ensure data integrity before it enters SQL queries. This involves checking inputs against defined criteria, rejecting any that deviate from expected formats or contain harmful characters. Proper validation prevents malicious payloads from influencing query structures and initiating unauthorized database interactions.

A layered input validation strategy includes both client-side and server-side checks. Server-side validation remains essential, as client-side alone is insufficient against determined attackers who bypass browser-based controls. By enforcing strict validation rules consistently, applications bolster resilience against SQL injection attempts.

2. Parameterized Queries

Parameterized queries, or prepared statements, aid in preventing SQL injection by treating user inputs as data rather than code. This technique distinguishes executable code from input variables, preventing attackers from altering SQL query logic. By defining static, parameterized SQL queries, applications protect against SQL injection vectors with ensured input separation.

The use of parameterized queries is essential in all database interactions, mitigating risks associated with dynamic SQL commands. By obligatorily implementing parameterization across application platforms, developers remove query execution vulnerabilities.

3. Stored Procedures

Stored procedures encapsulate SQL code execution within the database, offering a layer of abstraction and security from direct user influence. They enable predefined operations, limiting the potential vectors for SQL injection attacks by controlling what database actions are permissible. Given their encapsulated nature, stored procedures can implement strict access and data validation rules.

By relegating complex query logic to database-controlled stored procedures, applications reduce the likelihood of SQL injection. This ensures query execution consistency, reliability, and security, as only legitimate operations are permissible. To maximize protection, stored procedures should be accompanied by parameterized inputs and rigorous access controls.

4. Escaping Inputs

Escaping inputs is a precautionary measure to neutralize potentially harmful characters by ensuring they are treated as literal strings, rather than executable commands. This practice modifies data passed into SQL queries, preserving query logic from interference by special characters like quotes or semicolons.

Escaping inputs prevents attackers from crafting inputs that alter intended database operations. While effective, escaping should be used alongside parameterized queries and other defenses for comprehensive protection. By consistently escaping input data in combination with secure coding practices, organizations mitigate SQL injection risks.

5. Least Privilege Principle

Adhering to the least privilege principle involves granting database access rights strictly necessary for operations, diminishing potential damage from SQL injection. By limiting user permissions, even successful SQLi attacks encounter enforced operational boundaries, unable to access or manipulate extensive data sets.

Implementing least privilege requires careful analysis of application roles and access needs, ensuring credential configurations align precisely with functional requirements. This practice, coupled with rigorous access audits and monitoring, protects applications by maintaining minimal exposure to vulnerabilities.

Web Application Security with CyCognito

CyCognito identifies web application security risks through scalable, continuous, and comprehensive active testing that ensures a fortified security posture for all external assets​​.

The CyCognito platform helps secure web applications by:

• Using payload-based active tests to provide complete visibility into any vulnerability, weakness, or risk in your attack surface.
• Going beyond traditional passive scanning methods and targeting vulnerabilities invisible to traditional port scanners​​.
• Employing dynamic application security testing (DAST) to effectively identify critical web application issues, including those listed in the OWASP Top 10 and web security testing guides​​.
• Eliminating gaps in testing coverage, uncovering risks, and reducing complexity and costs. Offering comprehensive visibility into any risks present in the attack surface, extending beyond the limitations of software-version based detection tools​​.
• Continuously testing all exposed assets and ensuring that security vulnerabilities are discovered quickly across the entire attack surface.
• Assessing complex issues like exposed web applications, default logins, vulnerable shared libraries, exposed sensitive data, and misconfigured cloud environments that can’t be evaluated by passive scanning​​.

CyCognito makes managing web application security simple by identifying and testing these assets automatically, continuously, and at scale using CyCognito’s enterprise-grade testing infrastructure.

Learn more about CyCognito Active Security Testing.