MySQL Syntax error from INSERT INTO email address [duplicate]

This question already has an answer here:

I'm having trouble inserting an email address into a MySQL database. I'm getting the dreaded 1064 error like so..

Error creating new user You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@email.com, password, 20)' at line 1 -- 1064

So I'm guessing I need to escape the @ in the email variable? But nothing i do seems to please Maria. Generating the query in PHPMyAdmin was no help either.
Any advice gratefully accepted.

// update SQL
$sql2 = "INSERT INTO members (username, email, password, balance) VALUES ($new_player, $new_email, $new_password, 20)";
if ($db->query($sql2) == TRUE) {
echo "** New User Created **<BR>";
} else {
echo "Error creating new user " . $db->error." -- ". $db->errno ."<BR>";
}


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer
1

Changing your values to use quotes should fix this specific problem. However to prevent SQL injections you should use a prepared statement. The prepared statement will go as below:

$sql2 = INSERT INTO members (username, email, password, balance) VALUES (?,?,?,20)";
$pStmt = $db->prepare($sql2);
$pStmt->bind_param("sss", $username,$email, $password);
$pStmt->execute();


If you are sure you want to use your present setup because you're in a test environment or you specifically need it, changing your query to this will work:

$sql2=INSERT INTO members (username, email, password, balance) VALUES ('$new_player', '$new_email', '$new_password', 20);