SQL injection - Code - PHP

SQL injection - Code - PHP

Need

Implementation of proper input validation and the use of parameterized statements or stored procedures to prevent SQL injection attacks.

Context

  • Usage of PHP for server-side scripting and web development
  • Usage of a database for storing and retrieving data

Description

Non compliant code

        public function getUser($username, $password)
{
    $query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
    $results = DB::select($query);
    return $results;
}

The above code is a PHP function in Laravel that retrieves user data from a database. The function takes two parameters, $username and $password, and uses them to construct a SQL query which is then executed.

The vulnerability in this code lies in the way the SQL query is constructed. The $username and $password variables are directly embedded into the query string without any form of sanitization or validation. This means that if a user were to input malicious SQL code as their username or password, it would be directly executed on the database. This is known as a SQL Injection attack.

For example, a malicious user could input the following as their username: admin'; DROP TABLE users; --. This would result in the following query: SELECT * FROM users WHERE username = 'admin'; DROP TABLE users; --' AND password = ''. This query would delete the entire users table from the database.

To prevent this, it's recommended to use parameterized queries or prepared statements, which separate the SQL code from the data. This ensures that user input is always treated as data and never as code, preventing SQL Injection attacks.

Steps

  • Replace the dynamic SQL query with a parameterized query or a prepared statement.
  • Use placeholders in the SQL query for each variable.
  • Bind the actual user input values to these placeholders at runtime.
  • Ensure that all user inputs are properly validated and sanitized before they are used in the SQL query.

Compliant code

        public function getUser($username, $password)
{
    $query = "SELECT * FROM users WHERE username = :username AND password = :password";
    $results = DB::select($query, ['username' => $username, 'password' => $password]);
    return $results;
}

The original code was vulnerable to SQL injection attacks because it directly inserted user input into an SQL query. This could allow an attacker to manipulate the query, potentially gaining unauthorized access to data or performing other malicious actions.

The revised code mitigates this risk by using a parameterized query. Instead of inserting the user input directly into the query, it uses placeholders ( :username and :password). Then, it binds the actual user input values to these placeholders at runtime using an associative array ( ['username' => $username, 'password' => $password]). This ensures that the user input is properly escaped, preventing it from being interpreted as part of the SQL query itself.

In addition to using parameterized queries, it's also important to validate and sanitize all user input before using it in an SQL query. This can help prevent other types of attacks, such as cross-site scripting (XSS) attacks. Laravel provides several methods for validating and sanitizing user input, which you should use as appropriate for your specific use case.

References