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
– Stefan Gehrig, Commented Jun 24, 2009 at 13:21
Quite weird that no one suggested a prepared statement instead of escaping input. See stackoverflow.com/questions/60174/… for example. — mlt
– mlt, Commented Jun 13, 2012 at 21:29

cletus · Accepted Answer · 2009-06-24 13:21:07Z

3

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

answered Jun 24, 2009 at 13:21

cletus

627k169 gold badges922 silver badges945 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel A. White · Accepted Answer · 2009-06-24 13:19:52Z

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, 2009 at 13:19

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Comments

Paul A Jungwirth · Accepted Answer · 2009-06-24 13:23:21Z

2

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!

answered Jun 24, 2009 at 13:23

Paul A Jungwirth

24.9k16 gold badges79 silver badges96 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:55:42Z

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, 2017 at 11:55

CommunityBot

11 silver badge

answered Jun 24, 2009 at 13:21

Tim Hoolihan

12.4k3 gold badges43 silver badges54 bronze badges

3 Comments

soulmerge Over a year ago

Ummmm... wrong language? There is no SET in PHP. And you can as well use it without braces.

Eli Over a year ago

@soulmerge - the SET is within the SQL query.

Tim Hoolihan Over a year ago

@soulmerge as Eli said, I clipped the example to just show an example of my explanation

soulmerge · Accepted Answer · 2009-06-24 13:21:24Z

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, 2009 at 13:21

soulmerge

76.2k20 gold badges121 silver badges160 bronze badges

1 Comment

Gaʀʀʏ Over a year ago

Better yet: mysqli_real_escape_string

KM. · Accepted Answer · 2009-06-24 13:23:34Z

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, 2009 at 13:23

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

Kev · Accepted Answer · 2009-06-24 13:24:24Z

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, 2009 at 13:24

Kev

120k53 gold badges308 silver badges396 bronze badges

1 Comment

Zeeshan Rang Over a year ago

Thank you. i did sanitize my input. and it works well now. thanks

Gaʀʀʏ · Accepted Answer · 2012-06-13 21:26:31Z

0

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

answered Jun 13, 2012 at 21:26

Gaʀʀʏ

4,5403 gold badges43 silver badges59 bronze badges

Collectives™ on Stack Overflow

Error in MySQL update command. (in php)

8 Answers 8

Comments

Comments

Comments

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

Comments

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related