0

Although I found this very similar post Php PDO sql statement with single quotes, its solutions didn't work for me.

I have an SQL database table where I have a language and a plates column, the latter of datatype JSON. This latter column has the following structure of a JSON object: {"plate1":[],"plate2":[]}.

What I'm trying to do is: with an ajax request, the browser sends the plateType and the customerName onto the server (PHP script handling the request). Upon reception, the server should add the customerName into the array of the corresponding plateType, if not already present, for a given language.

The SQL statement I use for this is for example:

UPDATE plates_and_clients
SET plates=JSON_ARRAY_APPEND(plates,'$.plate1','customerX')
WHERE language='german'
AND NOT JSON_CONTAINS(plates,'"customerX"','$.plate1');

When I execute this in my SQL console of my phpMyAdmin, it works perfectly. The problem is now that, because I need to wrap the column name in single quotes for this statement to work, i.e. used '"$.plate1"' in the last line of the statement, I cannot make it work in PHP.

What I've tried so far is:

Prepared Statement PHP:

UPDATE plates_and_clients
SET plates=JSON_ARRAY_APPEND(plates,?,?)
WHERE language=?
AND NOT JSON_CONTAINS(plates,?,?);

and the binding:

$column = "plate1";
$column_path = "$.".column;
$name = "client";
$name_query = "'".$name."'";
$language = "german";
mysqli_stmt_bind_param( $stmt, "sssss", $column_path, $name, $language, $name_query, $column_path );

That's not working; the statement is executed perfectly, I even get a 200 response back from the server (without any reported error in the HTTP response, although my PHP error reporting is set to be on), but the database does not get updated as it does with the SQL statement when I run it in the phpMyAdmin. So I guess it's not properly interpreting the single quotes around $name_query. How can I enclose my $name string in single quotes for this prepared statement to work properly??

The Error I get from mysqli_error is:

Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0.
DevelJoe
  • 856
  • 1
  • 10
  • 24
  • Try the dollar sign in single quotes instead of double. The double quotes may try to parse it out. Otherwise, you might want to check for [mysqli errors](http://php.net/manual/en/mysqli.error.php) after your query to see if it's returning anything. – aynber Jun 04 '20 at 17:51
  • Just to make sure you didn't misunderstand me, it's the $name_query parameter which needs to be wrapped into single quotes for the statement to work, not the column path (which are the parameters preceded by the dollar sign)..? – DevelJoe Jun 04 '20 at 18:02
  • 1
    Actually, the name_query should be fine. I was thinking the column_path might mess up with the dollar sign in single quotes. But definitely check for mysqli_errors after your query to find out exactly what is happening. – aynber Jun 04 '20 at 18:05
  • Isn't it, right? also, when I var_dump it, I actually get the correct value "'myString'". I'll check on the mysqli error and let ya know, thx man – DevelJoe Jun 04 '20 at 18:07
  • Ok checked now, and it gives EXACTLY the same error as when I use the SQL statement without the single quotes wrapped around the corresponding parameter, namely: Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0. – DevelJoe Jun 04 '20 at 18:19
  • 2
    Oh, wait, I see. JSON likes double quotes, not single quotes. So switch the quotes around on your name-query: `'"'.$name.'"';` – aynber Jun 04 '20 at 18:20
  • YEHBOYYYYY worked, thanks so much man! – DevelJoe Jun 04 '20 at 18:25

0 Answers0