Do mysqli prepared statements protect fully from mysql injections?

Question

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');
}
?>

Essentially a duplicate of [how safe are PDO prepared statements](http://stackoverflow.com/questions/1314521/how-safe-are-pdo-prepared-statements) — deceze, Apr 04 '13 at 06:49

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

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.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 04 '13 at 06:52

Your Common Sense

156,878
40
214
345

But doesn't the 's' thing act the same way as the pdo stuff? Edit: well thanks for the help I'll switch my site over to PDO. – Lemon Drop Apr 04 '13 at 06:54

score 1 · Answer 2 · edited May 23 '17 at 10:25

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.

That's impractical position. One have to be prepared for the every case, not just deny the possibility. Consider my SafeMysql library I linked above. It offers protection for the identifiers as well — Your Common Sense, Apr 04 '13 at 07:42

Do mysqli prepared statements protect fully from mysql injections?

2 Answers2