SQL error in php

Question

Hey, I wrote some code for extracting some information out of the database and checking to see if it met the $_COOKIE data. But I am getting the 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 ')' at line 1

My code so far is:

$con = mysql_connect("XXXX","XXXXX","XXXXXXX");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("XXXXXX", $con);
$id = $_COOKIE['id']; 
$ends = $_COOKIE['ends'];
$userid = strtolower($_SESSION['username']);
$queryString = $_GET['information_from_http_address'];
$query = "SELECT * FROM XXXXX"; 
$result = mysql_query($query) or die(mysql_error());
  while($row = mysql_fetch_array($result)){
    if ($queryString == $row["orderid"]){
       $sql="UPDATE members SET orderid = ''WHERE (id = $id)";
       $sql="UPDATE members SET level = 'X'WHERE (id = $id)";
       $sql="UPDATE members SET payment = 'XXXX'WHERE (id = $id)";
       $sql="UPDATE members SET ends = '$ends'WHERE (id = $id)";
       if (!mysql_query($sql))
        {
            die('Error: ' . mysql_error());
        }
    }
  }

Any help would be appreciated, Thanks.

Answer 1

I'm not sure if this is the error, but do you realize you're code only runs the last UPDATE? You're assigning $sql 4 time, and only running it after the fourth assignement...

Answer 2

first of all you keep overwriting $sql variable so only the

$sql="UPDATE members SET ends = '$ends'WHERE (id = $id)";

is being executed.

And I would say that $id variable is not what you think it is (maybe empty as query like the one above without id:

$sql="UPDATE members SET ends = '$ends'WHERE (id = )";

would throw such error back.

Try

$id = NULL;

before

$id = $_COOKIE['id'];

if the error is gone that means that $id is not what you think it is

Answer 3

If $_COOKIE['id'] does not have a value, then $id in your SQL statements will be blank, leaving your SQL looking like this:

UPDATE members SET ends = 'something' WHERE (id = )

which, of course, is invalid SQL.

Answer 4

$sql="UPDATE members SET ends = '$ends'WHERE (id = $id)";

should be

$sql="UPDATE members SET ends = '$ends'WHERE (id = '$id')";

(IE add the ' around $id)

Answer 5

Only one of the SQL statements will execute, and that's the last one. You need to add some whitespace before the WHERE clause, like this:

$sql="UPDATE members SET ends = '$ends' WHERE (id = $id)";

Also be wary of SQL injection attacks in the event that your cookie is altered by the end user. One other thing of note is your orderid column. Is it a VARCHAR or some other unique identifier? If it's an integer, then setting it to empty string will not work. You might want to rethink your schema a bit here.

EDIT: Another thing you need to do is check to make sure the cookies actually have values. If not, your SQL strings will be messed up. Have you though about using parameterized queries through PDO so you don't have to worry about SQL injection at all?