SQL Injection in PHP
Play PHP Labs on this vulnerability with SecureFlag!
Vulnerable example
Consider the following standard PHP code for the endpoint /login:
$query = "SELECT user FROM users WHERE user = '$user' AND password = '$password'";
$result = pg_query($conn, $query);
In the example provided, the attacker could provide the user input admin'; DROP TABLE users; -- and the password password, which would result in the following query being executed:
$query = "SELECT user FROM users WHERE user = 'admin'; DROP TABLE users; --' AND password = 'password";
This query would first select the user “admin” and then drop the “users” table. The -- at the end of the user input is used to comment out the rest of the query and is ignored by the database.
Prevention
The solution is always to avoid string concatenation; common solutions are (in order of preference, where possible):
- using higher-level APIs provided by the framework at hand.
- using prepared statements.
- using appropriate escaping.
MySQLi
The mysqli_prepare(), mysqli_stmt_bind_param(), mysqli_execute(), and mysqli_stmt_get_result() functions are all part of the MySQLi (MySQL Improved) extension and are used to create and execute prepared statements. The mysqli_stmt_bind_param() function binds the values of the $user and $password variables to the placeholders ? in the query, respectively.
This code assumes that you have a database connection object stored in a variable called $conn.
$sql = "SELECT user FROM users WHERE user = ? AND password = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $user, $password);
mysqli_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
Doctrine
The setParameter method binds the values of the $user and $password variables to the placeholders :user and :password in the query, respectively.
This code assumes that a user entity is set up with properties for “user” and “password” and that you have an Entity manager object set up and stored in a variable called $entityManager.
$repository = $entityManager->getRepository(User::class);
$query = $repository->createQueryBuilder('u')
->where('u.user = :user')
->andWhere('u.password = :password')
->setParameter('user', $user)
->setParameter('password', $password)
->getQuery();
$result = $query->getResult();
Laravel
DB::select() is a method provided by the Laravel query builder, which is used to execute a SELECT statement on a database table. The method takes two arguments: the first is the raw SQL query as a string, and the second is an array of values to bind to the placeholders in the query.
$query = DB::select("SELECT user FROM users WHERE user = ? AND password = ?", [$user, $password]);
References
CWE - CWE-89: Improper Neutralization of Special Elements used in an SQL Command