My SQL Query gives a wrong result

Question

My SQL Query gives a wrong result

Navigation

#1 by (2 votes)

1

I'm trying this:

$email=$conn->real_escape_string($_POST['email']);
$pwd=$conn->real_escape_string($_POST['password']);

$cons='SELECT * FROM Usuarios WHERE email=? AND password=?';
$stmt = $conn->prepare($cons);
if($stmt){
    $stmt->bind_param("ss",$email,$pwd);
}
else{
echo "mm";
}
if($stmt->execute()){
    $filas=$stmt->num_rows;
    echo $stmt->error;

    if($filas>0){
        header('Location: consola.php');
    }
    else{
        echo $filas;
        echo $filas->error;
        echo $filas->errno;
    }
}
else{
    echo $stmt->error;
    echo "sss";

}

$conn->close();

It is a modification to another code that was more vulnerable to SQL injections so I decided to use prepared statements and the bind_param suspect that the problem is in $filas=$stmt->num_rows; because when I give echo print 0 obviously verify that what I wrote in the inputs were in the table. I also want to know what other advice they have for the security of the code

php mysqli

asked by ger 20.06.2018 в 00:46

source

1 answer

How to remove the edges in the bootstrap modal header Mysqli - multiple help consultation

score 2 · Accepted Answer

There are several things in the code, I will comment on them in the order in which I find them and in the end I propose a solution:

If you use prepared queries, you do not need real_escape_string . Currently prepared queries are a powerful tool that do even what this function does not do in some cases. Yes, it is proven that in certain cases this function does not help you to escape from anything.

What you should check is if the variables were passed in POST , we will use empty for it, and a ternary operator.

From the moment you prepare the query, you must refer to the variable that is created by calling prepare for any operation relative to the results.

I do not see any sense in evaluating also the execute , with evaluating the rough preparation. From there you will have at most 0 rows if you can not find data, but hardly a result FALSE . In addition, the logic of that part (where if($stmt->execute()){ is evaluated) seems to be inverted.

All the evaluations that you do after a possible data collection in the consultation are illogical. I have corrected / ordered all that part so that, if the number of rows is greater than 0 redirects to the page, and if not, print appropriate messages.

There is something very important regarding the use of num_rows . The PHP Manual says that: The behavior of mysqli_num_rows() depends on whether results with or without buffer are used. If used without buffer mysqli_num_rows() will not return the correct number of rows until all rows of the result have been retrieved . This means that, to obtain the number of rows once Once the query is executed, you must put the results in the buffer. For this, you can invoke store_result() before using num_rows , otherwise it will always throw you 0 rows even if there are results .

As a note, if the only thing you are interested in knowing is whether there are records, the correct thing would be to make a SELECT COUNT(*) , it is the most optimal way to verify the existence of records in tables. I have not modified the query, so as not to alter your code too much. I mention it here so you have it in mind.

I propose this code:

$email=(empty($_POST['email']))    ? NULL : $_POST['email'];
$pwd  =(empty($_POST['password'])) ? NULL : $_POST['password'];

if ($email && $pwd && $conn){
    $sql='SELECT * FROM Usuarios WHERE email=? AND password=?';
    $stmt = $conn->prepare($sql);
    if($stmt){
        $stmt->bind_param("ss",$email,$pwd);
        $stmt->execute();
        $stmt->store_result();
        $filas=$stmt->num_rows;
        if($filas>0){
            $stmt->close();
            $conn->close();
            header('Location: consola.php');
        }
        else
        {
            echo "No se encontraron registros";
        }
    }
    else
    {
    echo "Error en la consulta: ".$stmt->error;
    }   
}
else
{
    echo "Falta alguno de los datos del POST o la conexión es nula";
}