Insecure Use of SQL Queries

Why is this important?

SQL injections are dangerous because they can be easily identified by attackers. Hackers can use SQL injections to read from and sometimes even write to your database. SQL injections are very common and have been the cause of many high-profile breaches.

PHP packages with combined downloads of 50 million were affected by this.

Check out this video for a high-level explanation:

Read below to find out how to fix it.

Fixing Insecure Use of SQL Queries

Option A: Use Prepared Statements - Mysqli

1. Setup Mysqli.
2. Go through the issues that GuardRails identified in the PR.
3. Replace the code that looks like:

mysql_query("SELECT * from users where email = '" . $_GET['email'] . "';");

with:

// $mysqli instance of mysqli class
if ($stmt = $mysqli->prepare("SELECT * FROM users WHERE email=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $_GET['email']);

    /* execute query */
    $stmt->execute();
    [...]
}

4. Test it
5. Ship it 🚢 and relax 🌴

Option B: Use Prepared Statements - PDO

1. Setup PDO.
2. Go through the issues that GuardRails identified in the PR.
3. Replace the code that looks like:

mysql_query("SELECT * from users where email = '" . $_GET['email'] . "';");

with:

// $pdo instance of PDO class
$stmt = $pdo->prepare('SELECT * FROM users WHERE email=?');
$sth->execute($_GET['email']);
$result = $stmt->fetchAll();

4. Test it
5. Ship it 🚢 and relax 🌴

Option C: Use Prepared Statements - Doctrine ORM

1. Install an ORM like doctrine/orm.
2. Go through the issues that GuardRails identified in the PR.
3. Replace the code that looks like:

mysql_query("SELECT * from users where email = '" . $_GET['email'] . "';");

with:

// $em instance of EntityManager
$qb = $em->createQueryBuilder();
$qb->select('u')
 ->from('users', 'u')
 ->where('u.email = ?1')
 ->setParameter(1, $_GET['email']);

$query = $qb->getQuery();
$result = $query->getResult();

4. Test it
5. Ship it 🚢 and relax 🌴

More information:

• Getting Started with Doctrine
• ORM
• SQL Injection Prevention Cheat Sheet
• PDO documentation
• Mysqli documentation
• Common Weakness Enumeration (CWE-89)