PHP / MySQLi: How to prevent SQL injection on INSERT (code partially working)

Question

II would like to store two values (an email and a password) in a MySQL db using PHP. The input is passed to the PHP page via Ajax in jQuery (through an onclick event on a website).

Now I want to protect this PHP query against SQL injection - I am talking about a standard website so I don't want to overdo it but I think some standard protection can't be bad.

Below is what I had before just to ensure that the general procedure is working which it is. I then tried to increase security by using password encryption and statements but I am not sure if I did this the right way + I don't know if and how this needs to be applied to the Select part as well (where I am checking if the email already exists).

I've seen plenty of pages on this topic but as a beginner that's exactly my problem here. Can someone tell with this as an example what should be changed or added here and maybe provide some short explanations ? I would like to keep using MySQLi if possible.

Old PHP:

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$pw = $_POST["pw"]; 

$sql = "SELECT email FROM Users WHERE email = '" . $email . "'";
$query = $conn->query($sql);
if(mysqli_num_rows($query) > 0){
    echo "Record already exists";
}else{
    $sql = "INSERT INTO Users (email, pw) VALUES ('" . $email . "', '" . $pw . "')";
    if($conn->query($sql)){
        echo "Update successful";
    }else{
        echo "Update failed";
    };
}
$conn->close();

New PHP:

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$pw = password_hash($_POST["pw"], PASSWORD_BCRYPT); 

$sql = "SELECT email FROM Users WHERE email = '" . $email . "'";
$query = $conn->query($sql);
if(mysqli_num_rows($query) > 0){
    echo "Record already exists";
}else{
    $sql = $conn->prepare("INSERT INTO Users (email, pw) VALUES ('" . $email . "', '" . $pw . "')");
    $sql->bind_param('s', $name);
    $sql->execute();
    $result = $sql->get_result();
    if($result){
        echo "Update successful";
    }else{
        echo "Update failed";
    };
}
$conn->close();

Answer 1

In the New PHP code snippet, you are still vulnerable to injections.
You are using a prepared statement in the insert part, but you are not actually using the preparations strengths correctly.

When creating a prepared statement, you create a query in which you add placeholders instead of the raw values:

$stmt = $conn->prepare("INSERT INTO Users (email, pw) VALUES (?, ?)");

The question marks are the placeholders and are later replaced by using the bind_param method:

$stmt->bind_param('ss', $email, $pw);

The ss part of the bind call tells the mysql db that its two strings that are passed to the database (s for string, i for int etc).
You are binding a param ($name) but it has no placeholder nor any type of reference in the query..?

Your select statement on the other hand is still unsafe and open to vulnerabilities.
I would probably use a prepared statement there to, just as with the insert part.

You always want to make sure that input from the user is "safe" for the database, if you concat a query string and add user input into it, the database will not escape the strings, it will just run it.

Only use standard query method calls when you write the full query yourself, without any input params, and especially no input params that the user passed!

Answer 2

$query = $mysqli->prepare("INSERT INTO `table` (`col1`, `col2`) VALUES (?, ?)");
$col1 = 100;
$col2 = 14;
$query->bind_param('ii', $col1, $col2);
$query->execute();
$query->close();