How to avoid sql injection?

Question

Possible Duplicate:
How to prevent SQL injection in PHP?

I have this code

$where = '';
if (isset($_POST['lvl']) && $vals = $_POST['lvl']) {
    $where = 'WHERE ';
$first = false;
if ($vals[0] === '0') {
    $where .= 'team = "neutral"';
    unset($vals[0]);
    $first = true;
}
if (count($vals)) {
    if ($first) $where .= ' OR ';
    $where .= 'lvl IN (\'' . implode('\',\'', $vals) . '\')';
}}
    $sql = "SELECT * FROM $table $where";
$res = $DBH->prepare($sql);
$res->execute();
$num = $res->rowCount();
echo "<h2>".$num."</h2>";

It works, but if someone did something, then this happens. How to fix this?

UPD: added PDO code

Take a look here: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — sgeddes, Jan 26 '13 at 06:01
Please, do not close this question. It require a bit more complex answer rather than just "use prepared statements" with basic example. — Your Common Sense, Jan 26 '13 at 07:05

score 0 · Answer 1 · answered Jan 26 '13 at 06:01

0

Use PHP's mysqli_real_escape_string() in order to escape values. Note that it is mysqli because mysql functions have been depreciated.

answered Jan 26 '13 at 06:01

Ryan Tse

1,559
11
15

2

I'm not going to downvote, but this is the future, and prepared statements are now. – Tim Jan 26 '13 at 06:05
try this http://simon.net.nz/articles/protecting-mysql-sql-injection-attacks-using-php/ – Rakesh Sharma Jan 26 '13 at 06:07
I should have been more clear and used "will be depreciated". – Ryan Tse Jan 26 '13 at 06:14
escaping "values" has nothing to do with injections – Your Common Sense Jan 26 '13 at 06:49

Your Common Sense · Accepted Answer · 2013-01-26T08:48:29.000

You need to use PDO::quote() for all string values.
Also you should never select all the records when you need only to count them. Ask a database to do it for you

$where = '';
if (!empty($_POST['lvl'])) {
    $vals = $_POST['lvl'];
    $where = 'WHERE ';
    if ($vals[0] === '0') {
        $where .= "team = 'neutral'";
        unset($vals[0]);
        if ($vals) {
            $where .= " OR ";
        }
    }
    if ($vals) {
        foreach ($vals as $i => $val) {
           $vals[$i] = $DBH->quote($val);
        }
        $where .= "lvl IN (".implode(',', $vals).")";
    }
}
$sql = "SELECT count(*) as cnt FROM $table $where";
$res = $DBH->query($sql);
$res->execute();
$row = $res->fetch();
echo "<h2>".$row['num']."</h2>";

By the way, with my own class the code would be slightly less complex, because it makes manual escaping unnecessary (it is using mysqli, not PDO though).

$where = '';
if (!empty($_POST['lvl'])) {
    $vals = $_POST['lvl'];
    $where = 'WHERE ';
    if ($vals[0] === '0') {
        $where .= "team = 'neutral'";
        unset($vals[0]);
        if ($vals) {
            $where .= " OR ";
        }
    }
    if ($vals) {
        $where .= $db->parse("lvl IN (?a)",$vals);
    }
}
$num = $db->getOne("SELECT count(*) as cnt FROM ?n ?p",$table, $where);
echo "<h2>".$num."</h2>";

rowCount is proof that code works and do right things. Original code receives all from db and creates many html code. Anyway, thanks, first code block is that i need. — user1955915, Jan 26 '13 at 09:21

score -1 · Answer 3 · answered Jan 26 '13 at 06:09

-1

Use PDO with named parameters

http://php.net/manual/en/pdo.prepared-statements.php

answered Jan 26 '13 at 06:09

Ayaz

376
1
3
14

this doesn't actually answers the question – Your Common Sense Jan 26 '13 at 06:51

How to avoid sql injection?

3 Answers3