使用PHP在MySQL中插入电子邮件地址[关闭]

Question

The last few hours I've spent in struggling with a quite ridiculous error. Simply what I'm making a registration form. User need to enter a e-mail and the mail will be stored in a mysql table. (I have to warn you - please disregard the security issue, the whole thing is just a exercise). But I constantly get the following error from the sql:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com,[e-mail],1)' at line 1

The html is as follows:

      <form method="post" action="createnew.php">
            <label >user name</label>
            <input name="login_id"></br>
            <label>password</label>
            <input name="password" password></br>
                    ...some more stuff...
            <label>Privet e-mail</label>
            <input name="prv_email"></br>
            <label>Public e-mail</label>
            <input name="pub_email"></br>
            <label>Are you a reseller</label>
            <input type="checkbox" name="reseller"></br>
            <input type="submit">
        </form>

Then comes the PHP:

$login_id=$_POST['login_id'];
$password=$_POST['password'];
...some more stuff...
$pub_email=$_POST['pub_email'];
echo $prv_email;
echo $pub_email;
if (array_key_exists('reseller',$_POST)) {
    $reseller=1;
}
else {
    $reseller=0;
}
$sql="INSERT INTO users".
    "(login_id,password,usr_phone,region,prv_email,pub_email,reseller)".
    "VALUES".
"($login_id,$password,$usr_phone,$region,$prv_email,$pub_email,$reseller)"

And finally the MySQL formatting for the mail field(practically they prv_email and pub_email are with the same formatting)

    Field     | Type         | Collation
    ----------|--------------|-------------------  
    pub_email | varchar(255) | utf8_general_ci

I did try a lot off stuff but nothing seems to work. It always crushes at @. and one more thing - I'm running this who code on localhost(using WAMP), not sure if this have anything to deal with the problem. Please let me know if you need any other information and thanks a lot.

Answer 1

The following:

"($login_id,$password,$usr_phone,$region,$prv_email,$pub_email,$reseller)"

will be parsed as literals, rendering it the error you see above. You'll need to wrap each variable in single quotes for it to be seen as a string value instead.

I'd recommend that you have a look at PDO & Prepared Statements, as PDO provides an abstraction layer towards the database, as well as great security (for example via Prepared Statements).

Additionally, you're inserting to the field pub_email, but from your given output in the post, it's supposed to be pub_mail.