1. Home
  2. Forums Index
  3. Server Side / Databases 2:02 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

sql update statement mistake

         

snehula

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

10+ Year Member 



$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?

coopster

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

WebmasterWorld Administrator 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:
[php.net...]
[php.net...]

rocknbil

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

WebmasterWorld Senior Member 10+ Year Member



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

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

ends with a comma

shutoff='12345',

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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 2:02 am May 21, 2026