Prepared statements to update a MySQL database with user inputs

Question

Is there a good practice to update a MySQL database with PHP? Should I use this code:

function change_email($email, $email_new) {
    $sql = "UPDATE users SET email = '$email_new' WHERE email = '$email' LIMIT 1";
    $this->_db->query($sql);
}

Or is there a better solution? I heard of prepared statements and I think I should better use them here because $email and $email_new are user inputs.

Thank you very much.

Answer 1

1. There is no such thing like Mysqli DB
2. There is nothing special in updates performed via mysqli API. ALL queries are run the same way, be it update, select or show tables.
3. Mysqli API is quite hard in use
4. NEVER connect in the function to run one single query. Have already opened connection outside and pass it into function.
5. You should never add a variable in the query directly, but usa placeholder instead.

So, to update mysql database correctly better use PDO API:

function change_email($pdo, $email,$email_new)
{
    $sql = "UPDATE users SET email = ? WHERE email = ?";
    $pdo->prepare($sql)->execute([$email,$email_new]);
}

Answer 2

If you want to do it the non OOP-way. You can do it the following way. But be sure to escape your data first (If it is user input), cause otherwise you are vulnerable to SQL injections.

$email_new = mysqli_real_escape_string($db, $email_new); //If you didn't did this already
$email = mysqli_real_escape_string($db, $email);
//You could also use htmlentities to secure against cross-site scripting
$email_new = htmlentities($email_new); //You can use htmlspecialchars() if you only want to do minimum conversion.
//This is not needed for email cause you are not inserting it into the database.

$query = "UPDATE users SET email = '$email_new' WHERE email = '$email'";
mysqli_query($db, $query);

Answer 3

try this

$query = "UPDATE users SET email ='".$email_new."' WHERE email = '".$email."'";