Prepare Statement not fetching/binding any result. PHP

Question

I wanted to make this query with a prepare statement, but somehow it doesnt fetch any data. The username I type in the form is in the database, I guess the problem must be somewhere in the prepare stmt.

if(isset($_POST['login'])){

    $typed_username = mysqli_real_escape_string($connection, $_POST['login_username']);
    $typed_password = $_POST['login_password'];

    $column        = "username";

    $stmt = mysqli_prepare($connection, "SELECT user_password FROM users WHERE ? = ?");
    mysqli_stmt_bind_param($stmt, "ss", $column, $typed_username);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $user_password);

    if(mysqli_stmt_num_rows($stmt) < 1){
        echo "no results";
    }

    if(password_verify($typed_password, $user_password)){

        echo "login yeah!";

    }
}

I get "no results" no matter what I try.

You're actually getting an error because you cannot bind a column. If you need to make a column dynamic, make a whitelist so user input won't effect the SQL. — IsThisJavascript, Mar 27 '18 at 08:34
Just a few issues I noticed with your code that you should consider changing: **1.** `mysqli_real_escape_string` is not needed when you're using `prepare`, so remove that. **2.** You have two if's depending if no results or if login was successful, I'd suggest adding an `else` before the second `if` so PHP doesn't need to waste time running a `password_verify` if no user is found. **3.** You should look into getting [mysqli errors](http://php.net/manual/en/mysqli.error.php) to display during your initial development phase. — IsThisJavascript, Mar 27 '18 at 08:42
mysqli_real_escape_string is not “not needed”, it is _nonsense_ in this case, that will _corrupt_ your data. — CBroe, Mar 27 '18 at 08:44
@IsThisJavascript Thanks for all the tips, yes, the second if and the escape_string are not needed. I dont fully understand your comment for the prepare statement. You mean binding the column "username". If I just bind the "typed_username" I still dont get a result. — Alexandre Allegro, Mar 27 '18 at 09:05
I guess you just need to understand how `prepare` works. When you run `mysqli_prepare` you are sending the whole query to MySQL. MySQL receives this and checks that the statement is valid. It then waits for the user input (bind_param). This is why you cannot inject prepared statements. As prepare checks your statement, it reads the `table` you are querying from aswell as the columns (to match the input types it requires). If you try to bind an actual column name, MySQL cannot read the data type and therefore, your query fails on `mysqli_prepare` — IsThisJavascript, Mar 27 '18 at 09:11

IsThisJavascript · Accepted Answer · 2018-03-27T09:48:43.023

1

Although I've added a comment on how to solve this, I guess for your learning purpose I should add the solution here.

This becomes a very simple solution if $column = "username"; never changes.

If this is the case; you must change your prepare from this:
$stmt = mysqli_prepare($connection, "SELECT user_password FROM users WHERE ? = ?");
to this:
$stmt = mysqli_prepare($connection, "SELECT user_password FROM users WHERE username = ?");

Following that change, you no longer need to bind $column (mysql says binding a column is pointless anyway because it won't accept it.) So your bind_param changes from:
mysqli_stmt_bind_param($stmt, "ss", $column, $typed_username);
to this (you no longer need to myqsli_real_escape_string so you can throw the $_POST directly into the query:
mysqli_stmt_bind_param($stmt, "s", $_POST['login_username']);

Therefore, your overall code now looks like:

 if(isset($_POST['login'])){

    $typed_password = $_POST['login_password'];

    $stmt = mysqli_prepare($connection, "SELECT user_password FROM users WHERE username = ?");
    mysqli_stmt_bind_param($stmt, "s", $_POST['login_username']);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $user_password);
    //you where missing fetch
    mysqli_stmt_fetch($stmt);

    //store the result
    mysqli_stmt_store_result($stmt);
    //now we can use mysqli_stmt_num_rows
    if(mysqli_stmt_num_rows($stmt) < 1){
        echo "no results";
    }
    //added an else here as I said in the comments
    else if(password_verify($typed_password, $user_password)){

        echo "login yeah!";

    }
}

edited Mar 27 '18 at 09:48

answered Mar 27 '18 at 09:15

IsThisJavascript

1,726
2
16
25

Thank you, I understood what you meant and thats what I did, but still it returns 0 results. And `echo mysqli_stmt_error($stmt);` wont return anything. – Alexandre Allegro Mar 27 '18 at 09:20
It appears I overlooked a `mysqli_stmt_fetch($stmt)` that you were missing initially, please see my revised solution @AlexandreAllegro – IsThisJavascript Mar 27 '18 at 09:23
Aaah, oh man, of course. Thanks so much. I completely forgot that. But it still doesnt work just like this, I had to make a while loop and echo the variable to see a result. Why could that be? – Alexandre Allegro Mar 27 '18 at 09:31
What I mean is that the result is fetched, but the `mysqli_stmt_num_rows($stmt)`is always returning 0, although the variable $user_password is there and I can print it. – Alexandre Allegro Mar 27 '18 at 09:42
@AlexandreAllegro It appears you cannot use `mysqli_stmt_num_rows` without first storing the results with `mysqli_stmt_store_result($stmt);` as it says in the documentation: [mysqli_stmt_num_rows](http://php.net/manual/en/mysqli-stmt.num-rows.php) I've edited my answer slightly to show you where you would place it, but anywhere after execute should be fine. – IsThisJavascript Mar 27 '18 at 09:49
1

Thanks so much for all the trouble! Appreciate it! You´re awesome. – Alexandre Allegro Mar 27 '18 at 10:36

Prepare Statement not fetching/binding any result. PHP

1 Answers1