0

After this question,I run below script: 

$newrecord='{"firsttime":"19.53","secondtime":null,"sX464time":"6.25","wX1X465time":"4.14"}';//JSON
$sql='UPDATE  `'.$tablesnames[$i].'` SET `postrecords`='.$newrecord.' WHERE `id`='.$id;
//var_dump($sql) here
$update3=$pdo->exec($sql);

var_dump($sql) get result: 

string 'UPDATE  `table651` SET `postrecords`={"firsttime":"19.53","secondtime":null,"sX464time":"6.25","wX1X465time":"4.14"} WHERE `id`=1'

But finally get warning:

Warning: PDO::exec(): 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 '"firsttime":"0","secondtime":null,"sX464time":null

What's the problem?

kittygirl
  • 2,255
  • 5
  • 24
  • 52
  • In the other question you were told that you can't use parameters for the table name. Why did you remove the parameters for the data as well? – Barmar Apr 29 '18 at 16:52
  • In that question,I know cannot use parameters in `prepare`,then I use `exec` directly.what's my mistake? – kittygirl Apr 29 '18 at 16:58
  • You were only supposed to remove the parameter for the table name, you can keep them for everything else. – Barmar Apr 29 '18 at 17:02
  • You printed the SQL, you don't see the obvious error that you have a string with no quotes around it? – Barmar Apr 29 '18 at 17:03

2 Answers2

0

The syntax error is because you don't have quotes around the JSON string in the SQL.

Use bindParam() to solve problem of missing quotes, as well as ensuring that everything is escaped properly to prevent SQL-injection.

$stmt = $pdo->prepare('UPDATE  `'.$tablesnames[$i].'` SET `postrecords` = :record WHERE `id`= :id');
$stmt->bindParam(':record', $newrecord);
$stmt->bindParam(':id', $id);
$stmt->execute();
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

When you are using PDO or Object Orient MySQL, always bind parameters.

Strings always need to be wrapped within quotes and numbers can be without quotes. And don't try to make a json, instead create an array then convert it to a json.

$post_records = json_encode(array(
    'firsttime'=> '19.53',
    'secondtime' => null,
    'sX464time' => '6.25',
    'wX1X465time' => '4.14'
));

$sql='UPDATE `'.$tablesnames[$i].'` SET `postrecords`="'.$newrecord.'" WHERE `id`='.$id;
Prateek
  • 368
  • 3
  • 16
  • The json data come from my `json_encode(unserialize())` data, see [this post](https://stackoverflow.com/questions/50045230/how-to-update-column-from-serialize-to-json-with-pdo).I think it should be a `string` then I don't understand why should add `"`? – kittygirl Apr 29 '18 at 17:11
  • Because you are concatenating a variable instead of binding it. If you'll bind it, you would not need the quotes. But if you directly write in a string you would need it. You can see your query output, there are no quotes around your json string. – Prateek Apr 29 '18 at 17:49