SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Solution: Trust No One

Solution: Trust No One

There is no universal remedy against SQL Injection. Let's learn how we can use different techniques for different scenarios.

We'll cover the following...

There is no single technique for securing our SQL code. A good approach is to learn all of the following techniques and use them in appropriate cases.

Filter input

Instead of wondering whether some input contains harmful content, we should strip away any characters that aren’t valid for that input. That is, if we need an integer, we should use only the part of the content that comprises an integer. The best way to do this depends on our programming language; for example, in PHP, we should use the filter extension:

<?php 
$bugid = filter_input(INPUT_GET, "bugid", FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT * FROM Bugs WHERE bug_id = {$bugid}";
$stmt = $pdo->query($sql);
?>

We can use type casting functions for simple cases like numbers:

<?php
$bugid = intval($_GET["bugid"]);
$sql = "SELECT * FROM Bugs WHERE bug_id = {$bugid}";
$stmt = $pdo->query($sql);
?>

Rule #31: Check the back seatCheckBackseat

We can also use regular expressions to match safe substrings, filtering out illegitimate content:

<?php 
$sortorder = "date_reported"; // default
if (preg_match("/[_[:alnum:]]+/", $_GET["order"], $matches)) { }
$sortorder = $matches[1];
$sql = "SELECT * FROM Bugs ORDER BY {$sortorder}";
$stmt = $pdo->query($sql);
?>

Parameterize dynamic values

When the dynamic parts of our query are simple values, we should use query parameters to separate them from SQL expressions.

<?php 
$sql = "UPDATE Accounts SET password_hash = SHA2(?) WHERE account_id = ?";
$stmt = $pdo->prepare($sql);
$params = array($_REQUEST["password"], $_REQUEST["userid"]);
$stmt->execute($params);
?>
...