SQL Injection Cheat Sheet – Preventions & Vulnerabilities

In today’s digital age, web applications play a crucial role in our daily lives. From online banking to e-commerce, these applications store sensitive user data, making them prime targets for malicious hackers. One such common and dangerous attack is SQL injection (SQLi), which exploits vulnerabilities in web applications to gain unauthorized access to databases. 
In this blog, we will unlock the SQL injection cheat sheet, shedding light on SQL injection attacks, their impact, and the best practices to safeguard your applications from such threats.

What is SQL Injection?

SQL injection is a type of security vulnerability that occurs when an attacker manipulates an application’s input to execute malicious queries of SQL. Typically, web applications use user inputs to interact with databases. 

When these inputs are inadequately validated, attackers can insert malicious SQL code, leading to unauthorized data exposure and data manipulation. You can learn more about SQL vulnerabilities by taking an ethical hacking course.

Impact of SQL Injection Attacks

The consequences of a successful SQL injection attack can be severe, affecting both the application’s owner and its users. Some potential damages include:

• Unauthorized access to sensitive information, such as usernames, passwords, or personal data.
• Tampering with the database, altering data, or deleting valuable records.
• Complete compromise of the application, leading to administrative control by the attacker.
• Financial losses, such as customer data and transactions, may be compromised.
• Reputational damage, eroding customer trust and brand loyalty.

Cheat Sheet for Prevention Techniques

To secure your web applications against SQL injection attacks, follow these essential prevention techniques.

Input Validation

Input validation is an important practice in web development and database management to enhance security and prevent vulnerabilities like SQL injection attacks. 

Here are some of its types.

• Data Format Validation – Check if the input data matches the expected format, such as email addresses, phone numbers, dates, or numeric values. Regular expressions or specific validation functions can be used for this purpose.

• Data Range Validation – Ensure that numeric inputs are within acceptable ranges or that text inputs do not exceed the maximum allowed lengths.

• Data Type Validation – Validate that the input data matches the expected data type, such as strings, integers, or booleans.

• Data Sanitization – Remove or escape any potentially harmful characters from the input data.

• Server-Side Validation – Although client-side validation can enhance user experience, it’s essential to perform validation on the server side as well. Client-side validation can be bypassed, so server-side validation is a necessary prerequisite.

• Error Handling – Implement appropriate error handling mechanisms to provide meaningful error messages to users when their input is incorrect.

For example,

import re

def validate_email(email) -
    # Regular expression pattern for email validation
    email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    
    if not re.match(email_pattern, email) -
        return False
    return True

def register_user(name, email) -
    # Server-side validation
    if not name -
        return "Name is required."
    
    if not email -
        return "Email is required."
    
    if not validate_email(email) -
        return "Invalid email format."
    
    # Save the user's information to the database or perform further processing
    # ...
    
    return "User registered successfully."

# Example usage -
user_name = "John Doe"
user_email = "john.doe@example.com"

validation_result = register_user(user_name, user_email)
print(validation_result)

In this example, we have a function called “validate_email” that uses a regular expression pattern to validate the email address. The “register_user” function takes the user’s name and email as inputs and performs server-side validation. It checks whether the name and email are provided and whether the email matches the expected format using the validate_email function.

Parameterized Queries (Prepared Statements)

Parameterized queries, also known as prepared statements, are a way to execute SQL queries while keeping user data separate from the actual SQL code. This technique prevents SQL injection attacks, where attackers manipulate user inputs to inject malicious SQL code into the query. 

Here’s how prepared statements work.

• Query Template – Instead of directly embedding user inputs into the SQL query, a parameterized query uses placeholders (often represented by ‘?’ symbols) for dynamic values in the SQL code.

• Binding Parameters – Before executing the query, the application binds the actual values to the placeholders. The database engine automatically handles the proper escaping and quoting of the data, ensuring it’s treated as data and not executable code.

• Reusability and Performance – Prepared statements can be compiled and cached by the database server, leading to improved performance for repeated queries. They also promote code reusability as you can reuse the same query template with different parameter values.

For example,

import sqlite3
  
# User input
user_input = "JohnDoe'; DROP TABLE users; --"

# Using a parameterized query (prepared statement)
conn = sqlite3.connect("example.db")
cursor = conn.cursor()
query = "SELECT  FROM users WHERE username = ?"
cursor.execute(query, (user_input,))
result = cursor.fetchall()

In this example, the ‘user_input’ is safely inserted into the query using a parameterized query, mitigating any SQL injection risk.

Stored Procedures

Stored procedures are pre-compiled SQL statements stored in the database. Utilizing stored procedures can minimize the risk of SQL injection by limiting direct access to the underlying tables. This has several advantages, including:

• Improved Performance – Since stored procedures are pre-compiled and stored in a compiled form within the database, they can offer better performance compared to executing the same SQL statements repeatedly from an application. This is because the database engine can optimize the execution plan for the stored procedure once, and subsequent calls can reuse that plan.
• Reduced Network Traffic – When using stored procedures, only the procedure call needs to be sent over the network from the application to the database, rather than the full SQL statements. This can lead to reduced network traffic, especially when dealing with large or complex queries.
• Modularity and Reusability – Stored procedures promote modular design by allowing developers to encapsulate specific functionality within a single unit. This makes it easier to maintain and update the database logic, as changes can be made in one central location, and all the applications using the stored procedure will automatically benefit from the updates.
• Enhanced Security – As mentioned in the initial information, stored procedures can help minimize the risk of SQL injection attacks. By using stored procedures, developers can restrict direct access to the underlying database tables and grant appropriate permissions only to the procedures themselves. 
• Abstraction of Complexity – Complex database operations can be hidden behind a simple interface provided by the stored procedures. This abstraction allows developers to focus on the application logic without worrying about the intricacies of the database implementation.
• Transaction Management – Stored procedures can be used to define transaction boundaries, ensuring that multiple SQL statements are executed as a single unit of work. This helps maintain data consistency and integrity in the database.

For example,

-- Creating the Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Quantity INT,
    Price DECIMAL(10, 2)
);

-- Creating the AuditLog table
CREATE TABLE AuditLog (
    LogID INT PRIMARY KEY,
    LogDate DATETIME,
    Action VARCHAR(100),
    ProductID INT
);

-- Creating a stored procedure to update the product quantity and log the action
CREATE PROCEDURE sp_UpdateProductQuantity
    @ProductID INT,
    @NewQuantity INT
AS
BEGIN
    -- Update the product quantity
    UPDATE Products
    SET Quantity = @NewQuantity
    WHERE ProductID = @ProductID;

    -- Log the action in the AuditLog table
    INSERT INTO AuditLog (LogDate, Action, ProductID)
    VALUES (GETDATE(), 'Quantity Updated', @ProductID);
END;

In this example, the stored procedure provides an efficient, secure, and modular way to update product quantities while maintaining a log of the actions taken.

Cheat Sheet Common Attack Techniques

SQL injection is a serious threat. To avoid it, you need to regularly update security measures and conduct audits to stay ahead of potential attackers and maintain a robust defense against SQL injection. Let’s look at some SQL-based injection attacks:

Union-Based SQL Injection

Union-based SQL injection is a hacking technique that leverages the “UNION” keyword in SQL queries to merge results from multiple SELECT statements. This allows attackers to gain insights into the database structure and extract confidential information, posing a significant security threat.

For example, an attacker inputs malicious code like ‘UNION SELECT username, password FROM users’ into a vulnerable login form. This can reveal usernames and passwords from the database, compromising security.

Error-Based SQL Injection

Error-based SQL injection is a malicious technique that exploits SQL errors to glean details about the database structure. This information is exploited by attackers to devise subsequent attacks, posing a serious security risk.

For example, an attacker inputs a query that triggers an error message. By analyzing the error message, the attacker can extract valuable information about the database, helping in further exploitation.

Blind SQL Injection

Blind SQL injection is a malicious technique where attackers insert harmful queries into a system. Unlike regular attacks, the application doesn’t reveal the outcome. Attackers analyze results through true/false conditions, allowing them to exploit vulnerabilities without direct feedback.

For example, a hacker inputs a query that asks if a certain condition is true. This allows them to interpret results based on the application’s response, without directly seeing the data.

Conclusion

SQL injection remains a prevalent threat to web applications, endangering sensitive data and compromising user trust. By understanding the attack techniques and following the prevention methods provided in this SQL injection cheat sheet, you can reinforce your web application’s security posture. You can begin with this online resource on SQL for beginners, which offers a step-by-step guide to start your learning process.