Preventing SQL Injection Attacks in PHP Applications Using MySQLi

Understanding the Risks of SQL Injection Attacks

Introduction to SQL Injection

SQL injection (SQLi) is a type of web application security vulnerability that occurs when an attacker is able to inject malicious SQL code into a web application’s database. This allows the attacker to extract, modify, or delete sensitive data, and can also be used to perform unauthorized actions on the database.

One common technique used in SQL injection attacks is to manipulate user input to execute arbitrary SQL code. In this blog post, we will explore how to identify and prevent SQL injection attacks using MySQLi, a popular PHP extension for interacting with MySQL databases.

The Problem with Basic `mysqli_query()`

When using the mysqli_query() function, it executes a single SQL query at a time. This means that if an attacker is able to inject malicious code into user input, they may be able to execute multiple statements, which can lead to serious security issues.

However, as demonstrated in the provided Stack Overflow post, simply switching from mysqli_query() to mysqli_multi_query() does not provide sufficient protection against SQL injection attacks.

How Multiple Statements Can Be Executed with `mysqli_multi_query()`

The mysqli_multi_query() function allows you to execute multiple statements as a single query. This can be useful for complex queries that need to retrieve data from multiple tables, or when working with stored procedures.

However, if an attacker is able to inject malicious code into user input using mysqli_multi_query(), they may be able to execute multiple statements, including those that read sensitive data or cause denial-of-service attacks.

<?php
$connection = new mysqli('localhost', 'username', 'password', 'database');

// Attempt to execute a malicious query with mysqli_multi_query()
$query = "SELECT * FROM temp_table; drop table temp_table";
mysqli_multi_query($connection, $query);

// If the above query is successful, an attacker could potentially execute multiple statements,
// such as reading sensitive data or causing a denial-of-service attack.

$statement = $connection->prepare("SELECT last_name FROM Users");
$statement->execute();
$result = $statement->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['last_name'] . "<br>";
}

In this example, if the attacker is able to inject malicious code into user input using mysqli_multi_query(), they may be able to execute a query that reads sensitive data from the Users table.

How Basic SQL Injection Attacks Work

A basic SQL injection attack works by manipulating user input to execute arbitrary SQL code. This can be done in several ways:

Using comments: Comments are used to add notes to the code. If an attacker is able to inject malicious code into a comment, it may be executed as part of the query.
Using quotes and semicolons: By using quotes around user input and followed by a semicolon, an attacker can execute multiple statements as separate queries.
Using UNION operators: The UNION operator is used to combine the results of two or more queries. An attacker may use this operator to inject malicious code into user input.

For example, consider the following query:

SELECT * FROM Users WHERE last_name = 'O'Reilly';

If an attacker is able to inject malicious code into the last_name parameter using comments, it may look like this:

SELECT * FROM Users WHERE last_name = 'Robert'); DROP TABLE Users; --

In this example, if the query is executed as-is, it will read data from the Users table where the last_name column contains the string 'Robert'. However, if the attacker has successfully injected malicious code into user input, the second part of the query (DROP TABLE Users;) will be executed, causing a denial-of-service attack.

How to Prevent SQL Injection Attacks

Preventing SQL injection attacks is relatively simple and requires following best practices when writing web applications that interact with databases. Here are some steps you can take:

Always validate user input: Use prepared statements or parameterized queries to ensure that user input is properly sanitized.

**Never use comments as part of user input**: Comments should be used to add notes to the code, not inject malicious SQL code into queries.

Use prepared statements with parameterized queries: Prepared statements allow you to separate the query from the parameters, making it more difficult for attackers to inject malicious SQL code.

For example, consider the following code that uses a prepared statement with parameterized queries:

<?php

$connection = new mysqli('localhost', 'username', 'password', 'database');

$stmt = $connection->prepare("SELECT * FROM Users WHERE last_name = ?");
$stmt->bind_param("s", $last_name);
$last_name = "O'Reilly";
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['last_name'] . "<br>";
}

In this example, the ? placeholder is used to separate the query from the parameter. The $last_name variable is then bound to the prepared statement using the bind_param() method. This ensures that user input is properly sanitized and prevents malicious SQL code from being injected into the query.

Conclusion

SQL injection attacks are a serious security vulnerability that can have devastating consequences for web applications. By understanding how these attacks work and taking steps to prevent them, you can help keep your application secure and protect sensitive data.

In this blog post, we explored how using mysqli_multi_query() does not provide sufficient protection against SQL injection attacks. We also examined how basic SQL injection attacks work and provided examples of common techniques used by attackers.

By following best practices for writing web applications that interact with databases, including using prepared statements and parameterized queries, you can help prevent SQL injection attacks and keep your application secure.

Last modified on 2023-06-13