Mysql working correctly but show syntax error

Question

I have a non buggy mysql code (it's working and input correct data into every field)

$sql = "UPDATE bloggers SET img_name = '" . $img_name . "', name = '" . $blogger_name . "', blog_url = '" . $blog_url . "', google_plus = '" . $google_plus . "' WHERE blogger_id = '" . $blogger_id . "'";

        $res = mysql_query($sql);
        if($res) 
        {return 99;}
        else
        {return 0;}

where $res will return 99.

However, it's giving me this error.

Invalid query: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 '1''' at line 1

How do I go about solving this problem?

Let me know! Thanks!

Regards

You are vulnerable to [sql injection attacks](http://bobby-tables.com). If you were using a non-deprecated/obsolete library and properly using placeholders, you wouldn't be having this problem. — Marc B, May 21 '14 at 16:51
Your `$blogger_id` values seems to contain an unescaped `'`, which may lead to SQL injection. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). This may also fix your problem. — Gumbo, May 21 '14 at 16:53
I'm assuming your $blog_url has slashes, have you escaped them properly? — ksealey, May 21 '14 at 16:56
`mysql_query` is an obsolete interface and should not be used in new applications and will be removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). If you're new to PHP, a guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. — tadman, May 21 '14 at 17:06
echo $sql gives me UPDATE bloggers SET img_name = '0', name = 'ellenitn', blog_url = 'ellenitn.blogspot.com', google_plus = 'https://plus.google.com/12345555/' WHERE blogger_id = '1' — Architheutus, May 22 '14 at 13:01
i have also escaped as suggested, but still return me the same error. :( — Architheutus, May 22 '14 at 13:02

score 1 · Answer 1 · answered May 21 '14 at 16:53

1

I think there is a special character in your parameters. So sanitize your params by mysql_real_escape_string. It will prevent sql injection attacks as well.

$img_name = mysql_real_escape_string($img_name);
$blogger_name = mysql_real_escape_string($blogger_name);
$blog_url = mysql_real_escape_string($blog_url);
$google_plus = mysql_real_escape_string($google_plus);

answered May 21 '14 at 16:53

Harikrishnan

9,688
11
84
127

hi, i have tried to escape it, but still doesn't work. :( have no idea what's happening. – Architheutus May 22 '14 at 12:53
@Architheutus is value of ` $blogger_id` 1 ? – Harikrishnan May 22 '14 at 12:58
hi Harikrishnan,echo $sql gives me UPDATE bloggers SET img_name = '0', name = 'ellenitn', blog_url = 'ellenitn.blogspot.com', google_plus = 'http://plus.google.com/12345555/'; WHERE blogger_id = '1' – Architheutus May 22 '14 at 13:03
Can you just try this `$sql = "UPDATE bloggers SET img_name = '" . $img_name . "', name = '" . $blogger_name . "', blog_url = '" . $blog_url . "', google_plus = '" . $google_plus . "' WHERE blogger_id = ". $blogger_id;` – Harikrishnan May 22 '14 at 13:18
error is still there, echo $sql will give me : UPDATE bloggers SET img_name = '0', name = 'Carrie', blog_url = 'http://cheddarina.blogspot.sg/', google_plus = '0' WHERE blogger_id = 10 Warning: Cannot modify header information - headers already sent by (output started at /home3/sun/public_html/lib/functions/functions.php:2067) in /home3/sun/public_html/upload/process_editbloggerdetails.php on line 64 – Architheutus May 22 '14 at 13:27
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54208/discussion-between-harikrishnan-and-architheutus). – Harikrishnan May 22 '14 at 13:34

score 1 · Answer 2 · answered May 21 '14 at 16:57

Well to answer your question, you are performing SQL Injection on your own code since on of your variables contain single quote or slash in them.

You need to either escape them using mysql_real_escape_string BUT I would not do that since mysql_* functions have been deprecated.

Use PDO prepared statements or MySQLi instead where i stands for improved.

Using PDO it could be as simple as:

 $stmt = $pdoInstance->prepare("
            UPDATE bloggers 
            SET img_name = :imgname, name = :blogname, 
                blog_url = :blogurl, google_plus = :googleplus
            WHERE blogger_id = :blogid
         ");

  //bind parameters
  $stmt->bindParam(':imgname', $img_name, PDO::PARAM_STR);
  $stmt->bindParam(':blogname', $blogger_name, PDO::PARAM_STR);
  $stmt->bindParam(':blogurl', $blog_url, PDO::PARAM_STR);
  $stmt->bindParam(':googleplus', $google_plus, PDO::PARAM_STR);
  $stmt->bindParam(':blogid', $bloger_id, PDO::PARAM_INT);

  if ($stmt->execute()) {
     //success
  }

hi, i have also tried using your method. i tried to declare $pdoInstance = new PDO('mysql:host=localhost;dbname=**name**', 'username', 'password'); but it doesn't work as well. — Architheutus, May 22 '14 at 12:54

Mysql working correctly but show syntax error

2 Answers2