Error in MySQL update command. (in php)

Question

Good Morning everyone,

I am using an update command in php to update data in mysql. This is my code:

$sql=mysql_query("UPDATE blpublication SET JournalName = '$_POST[journal]', AcceptanceDate = '$_POST[acceptancedate]', PublishedDate = '$_POST[publisheddate]', Comment = '$_POST[comment]'
WHERE JobNo = '$_POST[jobno]'");

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "record Updated";

It does updates the field but, it gives me the following error. And i can not figure it out why am i getting this error. "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 '1' at line 1"

Can you help me in this

Best Zeeshan

Answer 1

I would sanitize your input first. This could lead to some very nasty errors such as what you are experincing and malicious attacks. Look up SQL Injection.

Answer 2

Can you tell us what the exactly output of $sql is? By the way, BIG security hole there. You should always escape query inputs namely:

$journal = mysql_real_escape_string($_POST['journal']);
$acceptance_date = mysql_real_escape_string($_POST['acceptancedate']);
$publish_date = mysql_real_escape_string($_POST['publisheddate']);
$comment = mysql_real_escape_string($_POST['comment']);
$job_no = intval($_POST['jobno']); // assuming jobNo is a number
$sql = <<<END
UPDATE blpublication
SET JournalName = '$journal',
AcceptanceDate = '$acceptance_date',
PublishedDate = '$publish_date',
Comment = '$comment'
WHERE JobNo = $jobno
END;
mysql_query($sql);
if (mysql_error()) {
  die("Error executing query '$sql': " . mysql_error());
}
echo "record Updated";

Answer 3

Similar to the following post, i believe when you have any object or array syntax, you need to put in braces.

SET JournalName = '${_POST[journal]}'

edit: and yes, as others pointed out you are risking sql injection.

Answer 4

First of all, your code is prone to SQL injection, escape your POST values:

$journal = mysql_real_escape_string($_POST['journal']);

And to actually debug your query, we need the query itself. Add an echo() statement before the actual execution of the query and post the result, the POST values possibly contain some unexpected value.

Answer 5

I think the problem is that you're running mysql_query twice. The first time it works and returns 1 (true), which you assign to $sql. Then you call mysql_query again, passing $sql (which equals 1). Of course "1" is not a valid SQL query, so you get the syntax error.

I wholeheartedly agree that you must sanitize those inputs!

Answer 6

Your general UPDATE syntax looks ok, except for the obvious injection possibilities, but you need to output $sql. One of your variables probably has a quote in it or some other issue like that....

Answer 7

Looking at the SQL UPDATE statement in your code, one thing leaps out at me. The table name is blpublication, are you maybe missing a 't', i.e. tblpublication?

Also you should really sanitise your input, otherwise you're going to be a victim of a SQL injection attack.