CODEWITHSUNDEEP
phpmysqlmysql-error-1064
max_
max_

Reputation: 24481

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

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.

Upvotes: 0

Views: 1919

Answers (3)

mu is too short
mu is too short

Reputation: 434665

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.

Upvotes: 5

Ibu
Ibu

Reputation: 43810

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

Upvotes: 0

SIFE
SIFE

Reputation: 5695

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

Upvotes: 4

Related Questions