MySQL syntax:You have an error in your SQL syntax…

Question

I am receiving the following error from the code below.

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 '@doe.com,username,5f4dcc3b5aa765d61d8327deb882cf99,09/05/2011 1:11:13 AM)' at line 1

$username = $_GET['username'];
$password = md5($_GET['password']);
$firstname = $_GET['firstname'];
$lastname = $_GET['lastname'];
$email = $_GET['email'];
$date = uk_date();
$conn = mysql_connect('localhost', 'myuser', 'mypass');
mysql_select_db('dbname');
$query = "INSERT INTO accounts (FirstName, LastName, Email, Username, Password, LastLoginDate) VALUES (". $firstname . ",". $lastname ."," . $email . "," . $username . "," . $password . "," . $date . ")";
$result = mysql_query($query) or die(mysql_error());
echo 'Success';
mysql_close($result);

Please could you let me know what my problem is? I am new to MySQL and PHP so please can you provide an explanation to what I have done wrong for later reference.

The problem is that you're (blah blah quotes blah blah escape blah blah SQL injection) and you're not using prepared statements. — BoltClock
– BoltClock, Commented May 9, 2011 at 0:17
Take a look at this article in the PHP PDO manual. Prepared statements are indispensable in helping guard against SQL injection attacks, which most web apps are vulnerable to. — BoltClock
– BoltClock, Commented May 9, 2011 at 0:18
prepared statements or escape your variables before inserting them in your sql query, or you might become of sql injection. Meaning someone can insert some code in your sql and do stuff like... delete all your tables — Ibu
– Ibu, Commented May 9, 2011 at 0:24

mu is too short · Accepted Answer · 2011-05-09 00:46:42Z

5

You haven't quoted any of the values in your INSERT, you should be saying something more like this:

$query = "INSERT INTO accounts (FirstName, LastName, Email, Username, Password, LastLoginDate) VALUES ('". $firstname . "','". $lastname ."','" . $email . "','" . $username . "','" . $password . "','" . $date . "')";

You should also be using mysql_real_escape_string on all those variables to make sure that any embedded quotes and such are properly encoded.

A better version would be something like this:

$query = sprintf("INSERT INTO accounts (FirstName, LastName, Email, Username, Password, LastLoginDate) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
    mysql_real_escape_string($firstname),
    mysql_real_escape_string($lastname),
    mysql_real_escape_string($email),
    mysql_real_escape_string($username),
    mysql_real_escape_string($password),
    mysql_real_escape_string($date));

You should also listen to BoltClock and use PDO and placeholders so you don't have to worry about your quotes and escaping so much. PDO will also make it easier to switch databases.

edited May 9, 2011 at 0:46

answered May 9, 2011 at 0:19

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

judda Over a year ago

Your quotes is wrong in the first query (the first '). You have them switched.

mu is too short Over a year ago

@judda: Good eyes, thanks and fixed. You've pointed out a good reason not to try building a query that way: too many quotes fighting each other and making an unreadable mess.

SIFE · Accepted Answer · 2011-05-09 00:21:42Z

4

Probably user input have a single quote character, so it will be safe to escape special character before send it as query to database, this will prevent your script from sql injection.

$query = "INSERT INTO accounts (FirstName, LastName, Email, Username, Password, LastLoginDate) VALUES ('$firstname', '$lastname', '$email','$username','$password', '$date')";

answered May 9, 2011 at 0:21

SIFE

5,7537 gold badges34 silver badges48 bronze badges

Comments

Ibu · Accepted Answer · 2011-05-09 00:28:27Z

0

Once you have escaped your variables like suggested by other, you need to surround them with quotes if they are string varialbles :

mysql_select_db('dbname'); 
$query = "INSERT INTO accounts 
       (FirstName, LastName, Email, Username, Password, LastLoginDate) 
       VALUES ('". $firstname . "','". $lastname ."','" . $email . "','" .
       $username . "','" . $password . "','" . $date . "')"; 
$result = mysql_query($query) or die(mysql_error());
echo 'Success'; mysql_close($result);

In this case i added single quotes. you shouldnt have any errors now

answered May 9, 2011 at 0:28

Ibu

44k14 gold badges82 silver badges109 bronze badges

Collectives™ on Stack Overflow

MySQL syntax:You have an error in your SQL syntax…

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related