MySQL "an error in your SQL syntax"

Question

Hi!

I'm trying to change a database entry with PHP but is stuck with this error message:

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 'Bjelkholm Lövgren AND adress = Brinellgatan 14 AND postnummer = 57135 ' at line 1

Code:

$namn = sanitize($_GET['namn']);
$adress = sanitize($_GET['adress']);
$postnummer = sanitize($_GET['postnummer']);
$postort = sanitize($_GET['postort']);
$email = sanitize($_GET['email']);
$status = 0;
$sql="UPDATE ordrar SET namn = $namn AND adress = $adress AND postnummer = $postnummer
AND postort = $postort AND email $email AND status = $status WHERE email = $email";
if (!mysql_query($sql))
{
    die('Error: ' . mysql_error());
}

Thanks for answers. /Victor

Answer 1

quote your variables (i.e. adress = '$adress')

Answer 2

Assuming those values are strings, you should quote them in your query string, plus you are missing the equals sign when comparing the email.

$sql="UPDATE ordrar SET namn = '$namn' AND adress = '$adress' AND postnummer = '$postnummer'
AND postort = '$postort' AND email = '$email' AND status = '$status' WHERE email = '$email'";

Answer 3

Couple things:

• Your strings need to be quoted (and escaped).
• You are missing an = for the email in the SET clause.

Answer 4

The short answer is that you've got a bunch of syntax errors. First, you'll need to properly quote your column names and values. Column names get a grave (sideways quote) and values get a normal single quote. And secondly you missed an equal sign before the $email variable.

Might I suggest breaking it up into multiple lines as well; this helps make it easier to debug.

$sql="UPDATE `ordrar` 
  SET `namn` = '$namn' AND 
  `adress` = '$adress' AND 
  `postnummer` = '$postnummer' AND 
  `postort` = '$postort' AND 
  `email` = '$email' AND 
  `status` = '$status' 
  WHERE `email` = '$email'";

One final suggestion, consider binding your parameters using prepared statements as opposed to string interpolation. They are more secure, and I personally find them easier to write.

Answer 5

1. SET statement values delimiter is comma, not AND
2. string values should be quoted

To make SET statements it would be nice to use a small function

function dbSet($fields) {
  $set='';
  foreach ($fields as $field) {
    if (isset($_POST[$field])) {
      $set.="`$field`='".mysql_real_escape_string($_POST[$field])."', ";
    }
  }
  return substr($set, 0, -2); 
}

and than just

$table = "ordrar";
$email = mysql_real_escape_string($_POST['email']);
$fields = explode(" ","namn adress postnummer postort email status");
$query  = "UPDATE $table SET ".dbSet($fields)." WHERE email='$email'";

will bring you properly formatted query

however, using email for row identification is bad. I'd suggest to use an auto-increment id field to identify your records instead of email.

Answer 6

$namn = sanitize($_GET['namn']);

$adress = sanitize($_GET['adress']);

$postnummer = sanitize($_GET['postnummer']);

$postort = sanitize($_GET['postort']);

$email = sanitize($_GET['email']);

$status = 0;

$sql="UPDATE ordrar SET namn = '$namn' , adress = '$adress' , postnummer = '$postnummer' , postort = '$postort' , email = '$email' , status = '$status' WHERE email = '$email' ";

if (!mysql_query($sql)) { die('Error: ' . mysql_error()); }

Try this. Hope its work well ;-)