homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

sql update statement mistake

Msg#: 4325891 posted 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?



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

Msg#: 4325891 posted 1:44 pm on Jun 14, 2011 (gmt 0)

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:


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

Msg#: 4325891 posted 4:54 pm on Jun 14, 2011 (gmt 0)

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 (?)

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved