SQL syntax error starting at '@'

Question

I have a mySQL query that is getting stuck when I run a string containing '@'. I have tried htmlentities() and htmlspecialchars() to no avail. Here's what I'm running:

    $name=$_POST['name'];
$first=$_POST['first'];
$last=$_POST['last'];
$bio=htmlentities($_POST['bio']);
$email=htmlentities($_POST['email']);
$pass=$_POST['pass'];
$date=date("m/d/y");
$bd=date('m-d-y',strtotime($_POST['month'].$_POST['date'].$_POST['year']));
$qer="insert into everything (user,first,last,bio,email,pass,date,bd) values ($name,$first,$last,$bio,$email,$pass,$date,$bd)";
if(!(mysql_query($qer,$con))){
    echo "no qer";
    echo mysql_error();
}

Here is the error:

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 '@re5yhgr5tyhrtyhr5tyr5tyhrt,test@first.com,pass,12/13/12,01-01-70)' at line 1

I was first trying it just in my email parameter, but I now know it has trouble no matter where it is. >:|

I'm assuming "line 1" is the first line of sql since my actual line 1 is ""...

Sorry if this is obvious, thanks in advance!

**Please read:** http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php — Matt Ball, Dec 13 '12 at 03:49

score 3 · Answer 1 · edited May 23 '17 at 11:55

3

when inserting a value with a data type of string, it should be enclosed with single quote. (Also for Date, Time, DateTime and other as long as it is not numeric)

$qer="insert into everything (user, first, last, bio, email, pass, date, bd) 
      values ('$name', '$first', '$last', ...)";

but the query above is vulnerable with SQL Injection, please read the article below to learn how to prevent from SQL Injection

How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:55

Community

1
1

answered Dec 13 '12 at 03:44

John Woo

258,903
69
498
492

2

@KuyaJohn Great point about SQL Injection. PHP beginners tend to build sites that could be taken down in a second by a malicious coder. – Anton Dec 13 '12 at 03:51
since i'd rather not switch my stuff to sqli or pdo, would mysql_real_escape_string() work fine for this? edit: nvm... – user1801821 Dec 13 '12 at 05:03

score 2 · Answer 2 · answered Dec 13 '12 at 03:45

2

Try: Add apostrophe (') in the fields that are of type varchar, char or date.

 ...values ('$name','$first','$last','$bio','$email','$pass','$date','$bd')...

answered Dec 13 '12 at 03:45

alditis

4,633
3
49
76

2

While this does answer the question, it will also leave the code wide open to SQL injection attacks. At minimum, use mysql_real_escape_string on everything before inserting in into the database query string, or even better use PDO or mysqli, which are safe, efficient, and don't require quotes. – Anton Dec 13 '12 at 03:49
1

@Anton `mysql_real_escape_string` doesn't fully prevent your from sql injection, see here: [SQL injection that gets around mysql_real_escape_string()](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/5741264#5741264) – John Woo Dec 13 '12 at 03:52
1

@KuyaJohn You're absolutely correct. There are some characters that could bypass mysql_real_escape_string and allow injection. I just didn't want to overwhelm the asker since they're just starting out :) – Anton Dec 13 '12 at 03:53

score 0 · Answer 3 · answered Dec 13 '12 at 03:45

0

you should wrap your args of insert sql within string, like "test@first.com"

answered Dec 13 '12 at 03:45

Richie Min

654
5
15

This misses the point. The OP should be using prepared statements. – Matt Ball Dec 13 '12 at 03:49

SQL syntax error starting at '@'

3 Answers3