Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

sql update statement mistake



12:54 pm on Jun 14, 2011 (gmt 0)

$sql = " UPDATE persons SET fn = '".$_POST[firstname]."', sn ='".$_POST[surname]."', cmp = '".$_POST[company]."',";
$sql .=" addr1 = '".$_POST[addr1]."', addr2 = '".$_POST[addr2]."', addr3 = '".$_POST[addr3]."', county = '".$_POST[addr4]."', ";
$sql .=" hph = '".$_POST[phone1]."', wph = '".$_POST[phone2]."', mph1 = '".$_POST[phone3]."', mph2 = '".$_POST[phone4]."', ";
$sql .=" dateOfBirth = '".$dob."', cno = '".$_POST[sky_no]."', payment = '".$_POST[pnote]."', cstat= '".$_POST[cstatus]."', shutoff = '".$shutoff."',";

this is from a php script that sets the sql query (mysql), all post array values exist, + the variables were set before usage. I'm getting this mistake:

cannot execute 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 '' at line 1

I have tried to execute the statement directly on the db with actual values, no problem there. I must be missing a quote or something but I really can't see it.. any ideas?


1:44 pm on Jun 14, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

You should never trust and never use raw user-supplied data in your query like that. Always edit the data to be certain you will accept what you expect and then prepare that data before using it. After doing so you may consider writing your query statements out in a fashion that make it much easier to spot syntax issues. Something along these lines ...
// get and edit our information; set a default if not found 
$firstname = isset($_POST['firstname']) ? trim($_POST['firstname']) : '';
$lastname = isset($_POST['lastname']) ? trim($_POST['lastname']) : '';
// prepare our data for safe sql use
$firstname = mysql_real_escape_string($firstname);
$lastname = mysql_real_escape_string($lastname);
// write out a query that we can see a little more plainly ...
$sql =
"UPDATE persons SET
fn = '{$firstname}',
ln = '{$lastname}'
WHERE ..."

Further reading:


4:54 pm on Jun 14, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

From what you've given, the error is in your last field.

shutoff = '".$shutoff."',";

ends with a comma


Unless the where clause is missing, it's still an error though

shutoff = '".$shutoff."', where record_id=23456;

should be

shutoff = '".$shutoff."' where record_id=23456;

If not, without a where this will update all records in the database. Not good, that's a big mistake . . .

Otherwise you probably have a quote in the input (?)

Featured Threads

Hot Threads This Week

Hot Threads This Month