SQL Injection: Types, Examples & Prevention Cheat Sheet

What Are SQL Queries? 

SQL queries are commands used to interact with databases, allowing data retrieval, updates, and administrative tasks. Structured query language (SQL) serves as the standard language for database management. Users construct SQL queries to perform functions like SELECT for data retrieval, UPDATE to modify records, and DELETE to remove data.

SQL queries also include aspects like JOINs to connect data across tables and WHERE clauses to filter results. A well-crafted query interacts correctly with the database, ensuring data integrity and performance. Understanding SQL's structure and capabilities is crucial for database management and protection against vulnerabilities like SQL injection.

What Is the Impact of a Successful SQL Injection Attack? 

A successful SQL injection attack can result in unauthorized access to sensitive data. Attackers might retrieve personal information, financial data, or business secrets, leading to data breaches and privacy violations. Such incidents can incur regulatory penalties and damage the organization's reputation.

Beyond data retrieval, SQL injection can alter database content, impacting data integrity. Attackers might delete or modify records, disrupt services, or execute commands leading to information loss or corruption. Financial costs due to rectification efforts, legal liabilities, and business disruption also add to the impact risks.

Tzvika Shneider

CEO, Pynt

Tzvika Shneider is a 20-year software Security industry leader with a robust background in product and software management.

Tips from the expert

• Use error handling for SQL exceptions: Ensure the application does not expose database error details to end users. Custom error pages and proper logging mechanisms should handle any database errors to prevent information leakage that could aid attackers.
• Limit query execution times: Enforce a maximum execution time for SQL queries. This can help mitigate time-based blind SQL injections, where attackers use delays to infer database information.
• Enforce strong input encoding and escaping: Beyond parameterized queries, ensure all user inputs are properly encoded and escaped, especially when dealing with dynamic SQL. Special characters like quotes or semicolons should be neutralized before interacting with the database.
• Integrate runtime application self-protection (RASP):Use RASP solutions to detect and prevent SQL injection attacks in real-time. Unlike WAFs, RASP works inside the application, analyzing its behavior and stopping malicious queries before they reach the database.
• Limit stored procedure privileges: Even when using stored procedures, ensure that the database account used to execute these procedures has minimal privileges, such as read-only for SELECT operations, preventing attackers from abusing higher-level permissions.

Types of SQL Injections 

There are several techniques that can be used to implement an SQL injection attack.

In-Band SQLi

In-band SQLi is the most basic type of SQL injection, where attackers use the same communication channel to both inject malicious SQL and receive results. This method is typically easier to exploit and offers quicker feedback to attackers, highlighting vulnerable applications.

In-band SQLi involves two primary techniques: error-based and union-based. Error-based injection leverages database error messages to gain insight into database structure. Union-based combines results from multiple tables using the UNION SQL operator, enabling data extraction and unauthorized access.

Inferential (Blind) SQLi

Inferential SQLi, also known as blind SQL injection, involves inferring data based on database behavior. Unlike in-band SQLi, the attacker gets no direct data from injections. Instead, they observe responses or behaviors to deduce information, making this method less detectable.

Blind SQLi includes two techniques: boolean-based and time-based. Boolean-based relies on true/false SQL queries to infer what data might exist. Time-based blind SQLi uses SQL statements that delay response for certain conditions, indicating information without direct visibility.

Out-of-Band SQLi

Out-of-band SQLi relies on alternative channels to execute SQL commands and receive data, making it less common but useful when in-band and inferential SQLi are impractical. It often depends on server configuration to support HTTP requests or DNS for a communication channel.

This type of SQL injection is useful when an attacker's access is limited by restrictive firewall settings or the web app doesn't return responses, which confines other SQLi methods. Though less frequent, out-of-band attacks can exploit configuration anomalies to extract data without immediate detection.

Examples of SQL Injection Attacks 

Example 1: Bypassing Authentication

For example, consider a vulnerable login form that directly integrates user inputs into an SQL query without sanitization. The query might look like this:

String username = request.getParameter("username");

String password = request.getParameter("password");

String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

Statement stmt = connection.createStatement();

ResultSet results = stmt.executeQuery(query);
‍

An attacker might enter the following instead of the username:

' OR 1=1; ––
‍

This would translate into the following code:

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

The OR 1=1 condition is always true, so the query returns all rows in the users table, bypassing authentication. This method allows attackers to gain access without knowing valid credentials.

Example 2: SQL Injection Leading to Data Deletion

A more severe attack can occur when malicious SQL statements modify or delete data. Suppose the attacker provides the following input for ItemName.Text:

name'); DELETE FROM items; --
‍‍

This input alters the query to:

SELECT * FROM items WHERE owner = 'malactor' AND itemname = 'name'; DELETE FROM items; --';

The database executes two separate commands: the first retrieves items, and the second deletes all records from the items table. The -- comments out the rest of the SQL statement, avoiding syntax errors.

To prevent such attacks, it's crucial to use parameterized queries or stored procedures that do not allow arbitrary execution of SQL code, such as:

SELECT * FROM items WHERE owner = @owner AND itemname = @itemname;

SQL Injection Prevention Cheat Sheet 

Here are some of the ways to prevent SQLi attacks.

Prepared Statements (with Parameterized Queries)

Prepared statements, also known as parameterized queries, prevent SQL injection by ensuring user input is treated strictly as data, not executable code. This method works by defining the SQL query first, leaving placeholders (i.e., ?) for user inputs. These inputs are then securely passed to the query, preventing any malicious SQL commands from being executed.

For example, in Java, a prepared statement looks like this:

String custname = request.getParameter("customerName");

String query = "SELECT account_balance FROM user_data WHERE user_name = ?";

PreparedStatement pstmt = connection.prepareStatement(query);

pstmt.setString(1, custname);

ResultSet results = pstmt.executeQuery();

In this case, even if the user enters malicious SQL, it will be treated as data, preventing SQL injection. Additionally, it is important to sanitize the input variable. In the above case, custname should be sanitized to remove special characters, spaces, etc.

Similarly, in C# a parameterized statement looks like this:

string query = "SELECT account_balance FROM user_data WHERE user_name = @username";

SqlCommand command = new SqlCommand(query, connection);

command.Parameters.Add(new SqlParameter("@username", customerName.Text));

SqlDataReader reader = command.ExecuteReader();

By binding parameters to the query, attackers cannot alter the SQL logic.

Stored Procedures

Stored procedures are SQL scripts stored in the database that can be executed with input parameters. When implemented securely, stored procedures protect against SQL injection by preventing direct interaction with the SQL statements themselves. However, care must be taken to avoid dynamically constructing queries inside the stored procedure.

Here's an example of a safe stored procedure call in Java:

CallableStatement cs = connection.prepareCall("{call sp_getAccountBalance(?)}");

cs.setString(1, customerName);

ResultSet results = cs.executeQuery();

In this case, the stored procedure sp_getAccountBalance executes a predefined query in the database, with user inputs treated as parameters.

In VB.NET:

Dim command As SqlCommand = new SqlCommand("sp_getAccountBalance", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text))

Dim reader As SqlDataReader = command.ExecuteReader()

Stored procedures should be used carefully, ensuring no dynamic SQL construction inside them.

Allow-List Input Validation

Input validation ensures that only expected and safe values are allowed in the SQL query. This is especially useful when user inputs determine dynamic parts of a query, such as table names, column names, or sort order. Using a predefined allow-list of acceptable inputs prevents unexpected or dangerous values from being used.

For example, if an input is expected to choose a table name, validate it against an allow-list:

String tableName;

switch (input) {

  case "fooTable":

    tableName = "fooTable";

    break;

‍

  case "barTable":

    tableName = "barTable";

    break;

‍

  default:

    throw new InputValidationException("Invalid table name");

}

By validating inputs against predefined values, you can ensure that unsafe SQL commands are not injected into the query. Avoid dynamic SQL generation whenever possible, and always validate inputs if it cannot be avoided.

Best Practices to Mitigate and Prevent SQL Injection Attacks 

Organizations can implement the following best practices to prepare for attacks like SQLi.

Use Least Privilege for Database Accounts

The least privilege principle limits database accounts to essential permissions, reducing exposure to attacks. By restricting accounts to necessary actions like data retrieval without modification permissions, potential vulnerabilities are minimized.

This strategy ensures that even if an attacker gains access, their ability to manipulate the database is curtailed. Regular reviews and updates of privilege settings ensure ongoing security, adapting to changing application needs and threat landscapes.

Use ORM (Object-Relational Mapping) Frameworks

ORM frameworks translate database interactions into programming languages, simplifying SQL query construction. By abstracting SQL command creation, ORM helps prevent direct SQL execution, reducing the likelihood of injections.

These frameworks encourage secure patterns, handling SQL queries with parameterization automatically. Developers benefit from streamlined syntax and reduced manual SQL manipulation, enabling safer database interactions without compromising on functionality.

Use Web Application Firewalls

Web application firewalls (WAFs) monitor and block malicious traffic targeting web applications, providing a frontline defense against SQLi. WAF solutions identify and filter malicious patterns, responding to potential threats in real time.

Effective WAF deployment offers immediate protection against known SQLi attempts, adds another security layer to web applications, and analyzes incoming traffic. Regular updates ensure evolving threats are counteracted, hardening the defense perimeter alongside internal security measures.

Use Database Permissions and Segregation

Permissions and segregation ensure database operations remain within secure, defined boundaries. By segregating databases based on access levels or application functions, misuse risks are reduced. Proper permissions management ensures only authorized users perform sensitive tasks, narrowing manipulation risks.

Segregated environments enhance malicious activity isolation. If one segment is compromised, others remain unaffected. Updated permissions and routine audits maintain an ongoing shield, ensuring that security aligns with changing data interaction needs.

Train and Maintain Awareness

Training programs and awareness initiatives equip teams with knowledge to identify and respond to SQLi threats. By understanding potential risks and signs of attacks, personnel serve as proactive participants in security strategies.

Continuing education ensures staff remain informed on current threats and prevention tactics. This culture of awareness strengthens detection capabilities and incident response readiness, improving the organizations overall defense against SQL injection and similar vulnerabilities.

‍Related content: Read our guide to security misconfiguration

Application Security Testing for APIs with Pynt

Pynt focuses on API security, the main attack vector in modern applications. Pynt’s solution aligns with application security best practices by offering automated API discovery and testing, which are critical for identifying vulnerabilities early in the development cycle. It emphasizes continuous monitoring and rigorous testing across all stages, from development to production, ensuring comprehensive API security. Pynt's approach integrates seamlessly with CI/CD pipelines, supporting the 'shift-left' methodology. This ensures that API security is not just an afterthought but a fundamental aspect of the development process, enhancing overall application security.

Learn with Pynt about prioritizing API security in your AST strategy to protect against emerging threats and vulnerabilities, like SQL injection.

‍