Time-based SQL Injection
Need
Prevention of time-based SQL injection attacks
Context
- Usage of PHP 5.0 or later for server-side scripting and web development
- Usage of mysqli for interacting with MySQL databases in Node.js
Description
Non compliant code
<?php
$user_id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $user_id";
$result = mysqli_query($connection, $query);
?>
The above code is a simple PHP code snippet that fetches user information from a database using a user id. The user id is taken from the URL parameters using the $_GET['id'].
This code is vulnerable to Time-Based SQL Injection attacks because it directly uses the user-provided id in the SQL query without any sanitization or parameter binding.
An attacker can exploit this vulnerability by providing a payload like 1 OR SLEEP(5)=0 -- as the id. This would cause the database to delay for 5 seconds before returning the response, allowing the attacker to infer that the SQL injection vulnerability exists.
The SLEEP function in SQL causes a delay in the execution. If the attacker sees a delay in the response, they can confirm that the input is being inserted into a SQL query and executed, hence confirming the presence of the SQL Injection vulnerability.
In the context of a Laravel application, this vulnerability can be introduced when raw queries are used without proper sanitization or parameter binding. Laravel provides several ways to prevent this, such as using Eloquent ORM or query builder which automatically handles these issues.
Steps
- Sanitize the user input to ensure it does not contain any SQL commands. This can be done using PHP's built-in functions such as mysqli_real_escape_string().
- Use prepared statements or parameterized queries to separate SQL logic from data. This ensures that user input is never treated as part of the SQL command.
- Limit the privileges of the database account used by the PHP application. It should only have the minimum permissions necessary to perform its tasks.
- Implement a web application firewall (WAF) to detect and block SQL injection attacks.
- Regularly update and patch your database management system to protect against known vulnerabilities.
Compliant code
<?php
$user_id = $_GET['id'];
$stmt = $connection->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();
?>
The original code was vulnerable to Time-based SQL Injection attacks because it directly included user input ($user_id) in the SQL query. This could allow an attacker to manipulate the query, potentially causing delays in execution or even retrieving unauthorized data.
The revised code mitigates this vulnerability by using a prepared statement, which separates the SQL logic from the data. The prepare() function is used to create the SQL statement, with a ? placeholder where the user input will be inserted. The bind_param() function is then used to bind the actual user input to the placeholder. This function automatically sanitizes the input, ensuring it is treated as a string and not part of the SQL command.
The "i" in the bind_param() function indicates that the parameter is an integer. This provides an additional layer of security by ensuring that the user input is of the correct data type.
The execute() function is then used to run the prepared statement. The get_result() function retrieves the result of the query.
This approach effectively prevents SQL Injection attacks by ensuring that user input is never directly included in the SQL query. It is also a good practice to limit the privileges of the database account used by the PHP application and to implement a web application firewall (WAF) to detect and block SQL injection attacks. Regular updates and patches to your database management system can also help protect against known vulnerabilities.