0

I was trying to insert an entry to MySQL table using following query.If I use MySQL parameterized query with mysqli, as below, it works without any issues.

insert into notelist(checksum,details,status,location) values('$checksum',JSON_OBJECT('filename','$filename','checksum','$checksum'),0,'$path')

But if I use parameterized query with mysqli as below, the error 'Cannot create a JSON value from a string with CHARACTER SET 'binary' is put into the debug file.Somebody please help to fix this.

$stmt = $con->prepare("insert into notelist(checksum,details,status,location) values(?,?,?,?)");
$stmt->bind_param("sbis", $checksum, $a = "JSON_OBJECT('filename','$filename','checksum','$checksum')", $b = 0, $path);
    $result = $stmt->execute();
if (false === $result) {
     file_put_contents("db.txt", "connecterror" . $stmt->error);
}
user3783243
  • 5,368
  • 5
  • 22
  • 41
Basil K Y
  • 490
  • 5
  • 8

1 Answers1

1

When binding you only want to bind values, not SQL functions, columns, or tables. The binding quotes the value and escapes all other quotes. Your query should be:

insert into notelist(checksum,details,status,location) values(?,JSON_OBJECT('filename',?,'checksum',?),?,?)

and then you should bind in the 5 values that are going to be bound.

$stmt->bind_param("sssis", $checksum, $filename, $checksum, 0, $path);

As currently written your query would have had the JSON_OBJECT as a string containing:

'JSON_OBJECT(\'filename\',\'filename_VALUE\',\'checksum\',\'$checksum_VALUE\')'
user3783243
  • 5,368
  • 5
  • 22
  • 41
  • Great, please accept the answer in that case. https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – user3783243 Aug 19 '18 at 18:57