Inserting e-mail address in MySQL using PHP

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,blqh2@gmail.com,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.

do a `echo $sql;die();` after the $sql line. Then post that here. — Blenderer, Apr 11 '13 at 15:26
Read more about the `@` and why your getting this error: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — Samuel Cook, Apr 11 '13 at 15:28

score 3 · Accepted Answer · answered Apr 11 '13 at 15:25

3

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.

answered Apr 11 '13 at 15:25

karllindmark

6,031
1
26
41

aha, now i see. Thanks for the recommendations. I'm bit new in PHP but PDO is something that almost everyone is pushing me to have a look at, so this will be my next step. And, yes, it was a mistype :) – Ale Apr 11 '13 at 16:08
Enjoy the ride! @Alordiel :-) – karllindmark Apr 11 '13 at 21:29

score 2 · Answer 2 · answered Apr 11 '13 at 15:26

You have to enclose your values in single quotes (') Change this snippet of code:

$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')"

score 1 · Answer 3 · answered Apr 11 '13 at 15:28

1

You need to, at the very least, add ''s around the strings and do a mysql-real-escape-string() on all passed arguments. Far better would be to use prepared statements with PDO or mysqli

answered Apr 11 '13 at 15:28

Andrew Leap

956
4
9

Inserting e-mail address in MySQL using PHP

3 Answers3