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

Do a "echo $sql;" before sending the query to the MySQL server to check what actually is being sent to the server. Also please note that you should NEVER put user input directly into a SQL query (aka. "SQL injection") - always filter, validate and/or sanitize the user input! — Stefan Gehrig, Jun 24 '09 at 13:21
Quite weird that no one suggested a prepared statement instead of escaping input. See http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php for example. — mlt, Jun 13 '12 at 21:29

score 3 · Accepted Answer · answered Jun 24 '09 at 13:21

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

score 2 · Answer 2 · answered Jun 24 '09 at 13:19

2

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.

answered Jun 24 '09 at 13:19

Daniel A. White

187,200
47
362
445

score 2 · Answer 3 · answered Jun 24 '09 at 13:23

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!

score 1 · Answer 4 · edited May 23 '17 at 11:55

1

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.

edited May 23 '17 at 11:55

Community

1
1

answered Jun 24 '09 at 13:21

Tim Hoolihan

12,316
3
41
54

Ummmm... wrong language? There is no SET in PHP. And you can as well use it without braces. – soulmerge Jun 24 '09 at 13:22
@soulmerge - the SET is within the SQL query. – Eli Jun 24 '09 at 13:26
@soulmerge as Eli said, I clipped the example to just show an example of my explanation – Tim Hoolihan Jun 24 '09 at 13:39

score 1 · Answer 5 · answered Jun 24 '09 at 13:21

1

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.

answered Jun 24 '09 at 13:21

soulmerge

73,842
19
118
155

Better yet: [mysqli_real_escape_string](http://php.net/manual/en/mysqli.real-escape-string.php) – Gaʀʀʏ Jun 13 '12 at 21:20

score 1 · Answer 6 · answered Jun 24 '09 at 13:23

1

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....

answered Jun 24 '09 at 13:23

KM.

101,727
34
178
212

score 1 · Answer 7 · answered Jun 24 '09 at 13:24

1

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.

answered Jun 24 '09 at 13:24

Kev

118,037
53
300
385

Thank you. i did sanitize my input. and it works well now. thanks – Zeeshan Rang Jun 24 '09 at 13:38

score 0 · Answer 8 · answered Jun 13 '12 at 21:26

Try concatenating the $_POST values. Im not sure if including them without quoting the key is possible?

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

Note: mysql_* commands are depreciated. You should switch over to mysqli_*.

Error in MySQL update command. (in php)

8 Answers8