Need

Implementation of secure coding practices to prevent SQL injection attacks

Context

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

Description

Non compliant code

        public function getUser($id) {
    $query = "SELECT * FROM users WHERE id = $id";
    $results = DB::select(DB::raw($query));
    return $results;
}
        
        

In the code above, we have a function named getUser which accepts an $id as an argument. This $id is directly used in an SQL statement without any form of validation or sanitization. This makes the application vulnerable to SQL Injection attacks.

An attacker can manipulate the $id parameter to alter the SQL query and gain unauthorized access to data or perform malicious actions. For example, an attacker might pass 1 OR 1=1 as the $id, which would result in the query SELECT * FROM users WHERE id = 1 OR 1=1. Since 1=1 is always true, this would return all users, potentially exposing sensitive information.

To mitigate this, we should use parameterized queries or prepared statements which ensure that the parameters (in this case $id) are properly handled and escaped before they are included in the SQL query. Laravel provides the Query Builder and Eloquent ORM which can be used to safely build and execute SQL queries.

# Fixed Code

                public function getUser($id) {
    return DB::table('users')->where('id', $id)->get();
}

            

Steps

• Replace the dynamic SQL statement with a parameterized query.
• Use the query builder provided by the database library to ensure proper escaping and quoting of variables.
• Avoid using raw SQL queries whenever possible.
• Validate and sanitize the input data to ensure it does not contain SQL code.
• Consider using a database abstraction layer or an ORM (Object-Relational Mapping) tool that can provide protection against SQL injection.

Compliant code

        public function getUser($id) {
    $id = (int) $id; // Ensure the id is an integer
    $results = DB::table('users')->where('id', $id)->get();
    return $results;
}
        
        

The original code was vulnerable to SQL injection because it was using a raw SQL query with a variable directly inserted into it. This could allow an attacker to manipulate the SQL query by providing a specially crafted value for the $id parameter.

The fixed code uses Laravel's query builder to create a parameterized query. The where method automatically escapes the provided values, preventing SQL injection. This way, the $id value is treated as a literal string in the SQL query, not as a part of the SQL syntax. Therefore, even if an attacker provides a value that contains SQL code, it will not be executed as SQL.

Additionally, the $id parameter is cast to an integer before being used in the query. This is an extra layer of protection that ensures the value used in the SQL query is a valid integer, which is what we would expect for a user ID. This helps to prevent SQL injection and also helps to catch any errors if an invalid value is provided.

It's important to note that using raw SQL queries in your application can be dangerous if not handled correctly, as they can open up your application to SQL injection attacks. Laravel's query builder or Eloquent ORM should be used whenever possible as they automatically protect against SQL injection.

References

• 146. SQL injection