prevent sql injection in mysqli [duplicate]

Question

I am very new to mysqli earlier i am writing queries in mysql but mysqli is more advanced so, i am first time using it. Below is my php code.

    function clean($str) {
        $str = @trim($str);
        if(get_magic_quotes_gpc()) {
            $str = stripslashes($str);
        }
        return mysql_real_escape_string($str);
    }

        $email = clean($_POST['email']);
        $password = clean($_POST['password']);
        //$password =md5($password);


    if(empty($res['errors'])) {
        $result = $mysqli->query("SELECT uid FROM users where email='$email' and password = '$password'");
        if($result->num_rows == 1){
            $res['success'] = true;
        }
        else{
            array_push($res['errors'], 'Invalid login details');
            $res['success'] = false;

        }
    }else{
        $res['success'] = false;        
    }
    echo json_encode($res);
}

clean function is not working as expected because sql queries return false if i enter username and password correct. So, it seems like this is not valid in mysqli case.

I checked this link PHP MySQLI Prevent SQL Injection and got to know that we have to prepare query.

I can see there is an example but i am not able to understand how to prepare/bind if i have to use two or more form data.

Thanks for your time.

Updated code

$result = $mysqli->prepare("SELECT uid FROM users where email=:email and password = :password");
        $result->execute([
':email' => $email,
         ':password' => $password]);
        //$result->execute();
        if($result->num_rows == 1){
        //if(mysqli_num_rows($result) === 1) {
            $res['success'] = true;

        }
        else{
            array_push($res['errors'], 'Invalid login details');
            $res['success'] = false;

        }

Answer 1

As already stated in comments, you need to be consistent with your API choice. You can't mix APIs in PHP.

You started out with mysqli_*, so I'll continue with that. You had some mysql_* and PDO in there, and it might not be a bad idea to use PDO over mysqli_* - but if your server supports mysqli_*, there is nothing wrong with using that. See Choosing an API and decide for yourself (just stay away from mysql_*, it's outdated).

Using mysqli_*, you connect to the database like this (you didn't show your connection).

$mysqli = new mysqli("host", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (".$mysqli->connect_errno.") ".$mysqli->connect_error;
}
$mysqli->set_charset("utf8");

As for preventing SQL injection in it self, all you need is to use prepared statements. You can still clean or sanitize your data if there are some kind of values you don't want sitting in your tables - but that's kind of another discussion.

You also need to know if your passwords are hashed in the database. They really should be, and you should be using password_hash($password, $algorithm) and password_verify($password, $hash) if you're on PHP5.5 and above (if not, look into something like password_compat).

You need to be consistent with your hashes too, you can't insert it with md5 and selecting it with no hash. It all needs to be the same. Because if you are selecting an md5 hash, and comparing it to an unhashed string, they will be different, and the query fails.

I'm showing you an example of using password_verify(), so that means that the password stored in the database will also need to be stored with password_hash() (or your query fails).

if ($stmt = $mysqli->prepare("SELECT uid, password FROM users where email=?")) {
    $stmt->bind_param("s", $_POST['email']);           // Bind variable to the placeholder
    $stmt->execute();                                 // Execute query
    $stmt->bind_result($userID, $password);         // Set the selected columns into the variables
    $stmt->fetch();                                   // ...and fetch it
    if ($stmt->num_rows) {
        if (password_verify($_POST['password'], $password)) {
            // Password was correct and matched the email!
        } else {
            // Password was incorrect...
        }
    } else {
        // Accountname not found
    } 
}

This is just a basic example, but it will get you started. Never trust user input, use prepared statements.