CODEWITHSUNDEEP
phpmysqlisql-injection
Lemon Drop
Lemon Drop

Reputation: 2133

Do mysqli prepared statements protect fully from mysql injections?

So I have been reading a lot about prepared statements and keep getting varied answers regarding how well prepared statements protect from mysql injections. Some people say it fully covers it while others say theres still some small work arounds or something. Pretty much I just wanted to make sure this code I have is safe and know if there are actually any holes in prepared statements:

<?php
ignore_user_abort(true);

$user = $_REQUEST['username'];
$pass = $_REQUEST['password'];

if (isset($user) && isset($pass)) {
require('/var/www/data/config.php'); //contains the db connection

if ($stmt = mysqli_prepare($mysqli, "SELECT password FROM users WHERE username=?")) {
    mysqli_stmt_bind_param($stmt, 's', $user);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $gpass);
    mysqli_stmt_fetch($stmt);
    mysqli_stmt_close($stmt);

    if ($gpass) {
        require('/var/www/data/handles/fCrypt.php');

        $chk = verify($pass, $gpass); //custom blowfish validation

        if ($chk) {
            //password correct, continue
        } else {
            mysqli_close($mysqli);
            //echo invalid password stuff
        }
    } else {
        mysqli_close($mysqli);
        //echo invalid username stuff
    }
} else {
    mysqli_close($mysqli);
    die('Query Error');
}
} else {
die('Invalid Request');
}
?>

Upvotes: 0

Views: 346

Answers (2)

didierc
didierc

Reputation: 14730

I am not an expert in this topic, but I would say that security is first and foremost a matter of practice. But it sure helps a lot to have the tools which make it easier to follow the right way, as long as you are following it.

There are limitations as to how the tool will help you reach that goal: that post on PDO usefullness mentioned in the comments covers them pretty well. For some of them, it's difficult to understand why (the IN clause issue is probably a technical one), but others makes a lot of sense: for syntactical parts of a query like a table or a column name - outside the fact that it would be quite diificult for a DB engine to compile and optimize a query without knowing which column or table it is working on, there should not be any reason whatsoever that such a data comes from a user (people will argue that db admin tools are a situation where this might occur, but that category of program already assumes a certain level of trust with the end user).

The only place where db metadata is known and manipulated is the program. That assertion really means that it's ok to build a query dynamically, bits by bits, and even use a library to help you out making sure that the query is syntactically correct, as long as you respect that core rule: don't leak the db schema to userland.

Regarding your code, it seems to me that you have followed the rules, and I don't see any dangerous statement there.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157839

Like any other measure, they works when applicable.
Therefore, they can't help when inapplicable.

So, as long as you can add every dynamical query part via placeholder, you can consider your query safe.

A note. That's why you shouldn't use mysqli but move to PDO or SafeMysql.

Just try to create a simple IN() statement, to bind an array of arbitrary number of values, using mysqli prepared statements. You will quit using it immediately.

PDO is not so good with IN() statements too, but the code at least reasonable.

Upvotes: 2

Related Questions