samedi 11 juin 2016

Short Solution for SQL Injection Attacks with MySQLI

I've gotten into the habit of writing the following sort of code:

$q = mysqli_query($mysqli,"SELECT * FROM table WHERE a='$a', b=$b;");
while ($row = mysqli_fetch_array($q)) {
    // do something
    }

Where $a is a string entered by the user (gotten through $_GET) and $b is a user-entered integer.

Obviously the code I have above is vulnerable to SQL injection attacks, so my habit is to rewrite it like this:

$q = mysqli_query($mysqli,"SELECT * FROM table WHERE a='".str_replace("'","",$a)."', b=".($b+0).";");

But this of course has problems if $a needs to have apostrophes (or quotation marks when quotation marks are used to mark the string).

Recently I learned about prepared statements in mysqli and started playing around with them. I wrote the following function to make it easier to make calls without having to change much of my code:

function safequery($a,$b,$c) {
    global $mysqli;
    $q = mysqli_prepare($mysqli,$a);
    $e = "mysqli_stmt_bind_param($q,$b";
    $i = 0;
    while ($i < count($c)) {
        $e.=",";
        $e.="$c[$i]";
        $i++;
        }
    $e.=");";
    eval($e);
    mysqli_stmt_execute($q);
    return $q;
    }

safequery("SELECT * FROM table WHERE a=? AND b=?;","si",array("unsafestring",37));

But what is returned from this function turns out not to be a mysqli_result and thus doesn't work with the first bit of code above. After some more research, I found an alternative, but it would require a complete rethink of how I write my code. Is this necessary or is it possible to protect against MySQL injection attacks with only small changes to the first bit of code (no new lines, same output style, etc.)?

I have looked around on StackOverflow and the rest of the web but I can't find a good simple solution; all of them require the edition of at least three more lines for every call and a different way of reading each row. I'd prefer to do this procedural-y...

Aucun commentaire:

Enregistrer un commentaire