Update Statement Using Pdo

[SigglesMFC]

Hi, I have a few INSERT and SELECT statements using a similar format that all work but I cannot get this UPDATE statement to work..

 

  function updateUserField($username, $field, $value){
  $query = "UPDATE ".TBL_USERS." SET :field = :value WHERE username = :username";
  $stmt = $this->connection->prepare($query);
  return $stmt->execute(array(':username' => $username, ':field' => $field, ':value' => $value));
  }

 

The error is:

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''userid' = '4bb2239345sdc16d5424sb997ad3' WHERE username = 'tom'' at line 1' in /home/ddddd/public_html/login/include/database.php:223 Stack trace: #0 /home/ddddd/public_html/login/include/database.php(223): PDOStatement->execute(Array) #1 /home/ddddd/public_html/login/include/session.php(184): MySQLDB->updateUserField('tom', 'userid', '4bb2239345f02dc...') #2 /home/ddddd/public_html/login/process.php(60): Session->login('tom', 'sempes', false) #3 /home/ddddd/public_html/login/process.php(21): Process->procLogin() #4 /home/dddddy/public_html/login/process.php(236): Process->Process() #5 {main} thrown in /home/ddddd/public_html/login/include/database.php on line 223

 

Any ideas where I am going wrong?

[Muddy_Funster]

looks like the field name userid is quoted.....

[SigglesMFC]

Hi you are right, the function might be used elsewhere in the script like so:

 

$database->updateUserField($subuser,"password",$newpass);

 

What actually causes the problem? If I echo the query it looks like this:

 

UPDATE users SET :field = :value WHERE username = :username

[trq]

You cannot use dynamic identifiers like that.

 

PDO sees the value passed into :field as a string and will surround it with quotes accordingly. Column identifiers cannot be surrounded in quotes.

[SigglesMFC]

Okay thanks.I can get it to work by doing...

 

function updateUserField($username, $field, $value){
  $query = "UPDATE ".TBL_USERS." SET ".$field." = :value WHERE username = :username";
  $stmt = $this->connection->prepare($query);
return $stmt->execute(array(':username' => $username, ':value' => $value));

 

but by doing this I am not checking the $field variable for injection. Any ideas?

[trq]

Why do you need to make this field dynamic? And why on earth would that be coming from user submitted data?

[SigglesMFC]

Why do you need to make this field dynamic? And why on earth would that be coming from user submitted data?

 

It's not user submitted data. It gets called by admin functions carried out on the website. The $field parameter that is passed is always hard coded somewhere else in the script. For example...

 

$database->updateUserField($subusertoedit,"userlevel",$subuserlevel);
$database->updateUserField($this->username,"email",$subemail);

 

But you have sort of answered my own question. As there is no user input for that parameter I dont need to worry about SQL injection.