SQL Injection in PHP
Vulnerable example
Consider the following Symfony controller for the endpoint /items?q=...:
class ItemsController extends Controller
{
/**
* @Route("/", name="items_index")
* @Method("GET")
*/
public function index(Request $request)
{
// fetch the query parameters
$pattern = $request->query->get('q');
// get matching list
$manager = $this->getDoctrine()->getManager();
$rsm = new ResultSetMapping();
$rsm->addScalarResult('name', 'name');
$query = $manager->createNativeQuery("SELECT * FROM items WHERE name LIKE '%$pattern%'", $rsm);
$items = $query->getResult();
// render the template
return $this->render('items/index.html.twig', [
'items' => $items
]);
}
}
The user-supplied query parameter q is inserted as-is in the SQL query via the $pattern variable. An attacker could craft a special value for q to force the page into returning data from other tables in the database, reading files on the system and, depending on the privileges, even planting PHP web shells.
For example, an attacker could exfiltrate the password field from the users table, and they may also use the UNION SQL construct to append the results of another query to the vulnerable one.
The crafted payload q must escape the string and terminate the query with a comment, for example ' INJECT_HERE --. This produces the first query below:
SELECT * FROM items WHERE name LIKE '%' INJECT_HERE --%'
Then the keyword UNION is used to append the results of another query provided that the number of fields match. Since the attacker does not know this value, he or she can probe it with the following until an error is returned:
SELECT * FROM items WHERE name LIKE '%' UNION SELECT 1,2,3,4,5 --%'
Eventually, the above query returns a list of all the item names (% matches all) and a number representing the correct number of columns for that table. To fetch the password column of the users table, the attacker could inject the string:
' UNION SELECT 1,2,password,4,5 FROM users --
resulting in the query:
SELECT * FROM items WHERE name LIKE '%' UNION SELECT 1,2,password,4,5 FROM users --%'
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
$stmt = $mysqli->prepare("SELECT * FROM items WHERE name LIKE ?");
$stmt->bind_param("s", "%$value%");
$stmt->execute();
$result = $stmt->get_result();
Doctrine
$items = $manager->getRepository('AppBundle:Item')->createQueryBuilder('i')
->where('i.name LIKE :pattern')
->getQuery()
->setParameter('pattern', "%$value%")
->getResult();
References
CWE - CWE-89: Improper Neutralization of Special Elements used in an SQL Command