SQL Injection

Introduction

In this chapter, we will focus on SQL Injection, a serious security vulnerability that can allow attackers to interfere with the queries an application makes to its database. SQL Injection can lead to unauthorized data access, data manipulation, and other harmful actions. This chapter will cover the definition, examples of SQL Injection, and provide techniques to prevent SQL Injection effectively.

What is SQL Injection?

SQL Injection is a code injection technique that exploits a security vulnerability in an application's software by injecting malicious SQL code into a query. This can allow attackers to manipulate the database and access or modify sensitive data.

Examples of SQL Injection

Example 1: Basic SQL Injection

Consider a simple login form where users enter their username and password. The application might use the following SQL query to check the credentials:

SELECT * FROM users WHERE username = 'user' AND password = 'pass';

An attacker can exploit this by entering malicious input:

username: ' OR '1'='1
password: ' OR '1'='1

The resulting SQL query would be:

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

This query always returns true, allowing the attacker to bypass authentication.

Example 2: Union-Based SQL Injection

An attacker might use the UNION operator to combine the results of the original query with the results of another query. For example:

SELECT id, username, password FROM users WHERE username = 'admin' UNION SELECT 1, 'attacker', 'password';

This can allow the attacker to retrieve additional data from the database.

Preventing SQL Injection

1. Use Prepared Statements and Parameterized Queries

Prepared statements ensure that SQL code is separated from data, preventing attackers from injecting malicious SQL.

Example in PHP

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

2. Use Stored Procedures

Stored procedures can encapsulate SQL code and are less prone to injection.

Example in SQL Server

CREATE PROCEDURE sp_GetUser
    @username NVARCHAR(50),
    @password NVARCHAR(50)
AS
BEGIN
    SELECT * FROM users WHERE username = @username AND password = @password;
END

3. Input Validation

Validate and sanitize user input to ensure it meets the expected format and type.

Example

$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);

4. Least Privilege Principle

Ensure that the database user account used by the application has the least privileges necessary.

5. Use ORM (Object-Relational Mapping) Frameworks

ORM frameworks automatically handle query parameterization, reducing the risk of SQL Injection.

Example in Python with SQLAlchemy

session.query(User).filter(User.username == username, User.password == password).first()

6. Escaping User Input

As a last resort, escape special characters in user input to prevent it from being interpreted as SQL code.

Example in PHP

$username = mysqli_real_escape_string($conn, $username);

Real-World Example

Vulnerable Code

$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);

Secure Code

$username = $_POST['username'];
$password = $_POST['password'];
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();

Conclusion

SQL Injection is a critical security vulnerability that can lead to severe consequences. This chapter covered the basics of SQL Injection, provided examples to illustrate how it works, and demonstrated various techniques to prevent it. Understanding and implementing these prevention techniques will help you secure your applications and protect your data.