Rule #11-013 Database queries should use parameter binding

Info: The following rule description is a sample.

To get a full access to all rule descriptions, upgrade your plan now.

SQL Injection is possible because of code looking like this:

1
$query = 'SELECT * FROM User WHERE username = "'.$username.'" AND password = "'.md5($password).'"';

In such a request, you can assign to $username value toto" OR 1 = 1. The generated SQL request will be:

1
SELECT * FROM User WHERE username="toto" OR 1 = 1 AND password = "f71dbe52628a3f83a77ab494817525c6"

Because of operators precedence, this request is equivalent to:

1
SELECT * FROM User WHERE username="toto"

Here, since quotes are not escaped, what was initially a field value is now part of SQL query.

To avoid this problem, you can use parameter binding in SQL query. This feature is provided by PDO-PHP module and is used like this:

1
2
3
4
5
6
$calories = 150;
$colour = 'red';
$stmt = $conn->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour');
$stmt->bindParam(':calories', $calories, PDO::PARAM_INT);
$stmt->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$stmt->execute();

Doctrine integrates fully this feature from PDO. You can use it like this:

1
2
3
$query = $em->createQuery("SELECT u FROM CmsUser u LEFT JOIN u.articles a WITH a.topic LIKE :foo");
$query->setParameter('foo', $foo);
$users = $query->getResult();

Doctrine provides many tool to abstract you from raw SQL queries. For this reason, you are automatically protected when you use "high-level" methods like:

1
2
$qb = $this->createQueryBuilder('p');
$qb->where($expr->like('p.u', 'test_%'));

Pointers:

Rule settings

To customize this rule, you can add the following section to your project configuration:

rules:
  # ...
  doctrine.database_query_contains_string_and_variable_concatenation:
    enabled: true