0

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>";
    }
noisy andrew
  • 23
  • 8
  • 1
    Strings need to be quoted. You should use parameterized queries. Also hopefully that password is hashed. – user3783243 Jul 01 '18 at 03:37
  • In your values, change them to: ('$new_player', '$new_email', '$new_password', 20). That will work when entering into the database. – Sarhad Salam Jul 01 '18 at 03:37
  • 3
    That's true, OP should think about using a prepared statement to prevent SQL injections. – Sarhad Salam Jul 01 '18 at 03:41
  • Hi all and thanks. this is nothing important, it's just a learning exercise for me. But I will look into using prepared statements. Quoting the Variable worked. I thought i needed to escape them first as that's what PHPMyAdmin was doing. – noisy andrew Jul 01 '18 at 04:01

1 Answers1

0

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);
Sarhad Salam
  • 428
  • 3
  • 12