Convert database records containing double quotes to JSON

Question

I have PostgreSQL database that contains columns with string values that contain double quotes.

Example value: Exploitation field of hydrocarbons "Environment"

I use PHP to fetch contents of the table that contains this record and store query result to $queryResult variable.

I use json_encode() to convert query result to JSON like this:

$result = pg_query($query);
$queryResult = array();
while ($row = pg_fetch_row($result)) {
    $queryResult[] = $row;
}
$queryResult_json = json_encode($queryResult);

I want to use this as JS object so I send data to JavaScript like this:

<script type="text/javascript">
    var myArray = '<?php echo $queryResult_json ?>';
    myArray_object = JSON.parse(myArray);
</script>

The problem is, I get an error in console: Uncaught SyntaxError: Unexpected token O in JSON at position 9163 and it looks like it points to double quotes inside my string beacause JSON uses double quotes around the values so it cannot parse double quote inside double quote.

The part of string that JSON.parse() is having trouble with looks like this: ["0134","Cadastral plan - Exploitation field of hydrocarbons "Environment" - WFS"]

I cannot use .replace() method because it would replace all double quotes, not just the ones inside the string. Is there a way to work around this?

score 3 · Accepted Answer · answered Mar 06 '18 at 13:10

$queryResult_json will contain JSON.

The problem is that '<?php echo $queryResult_json ?>' will generate a JavaScript string literal and some characters in JSON have special meaning in string literals … but you aren't escaping those.

If you really want to generate a JS string of JSON, then you can do with with json_encode (NB: It will generate the quotation characters for you so don't add those manually).

$queryResult_json = json_encode($queryResult);
$string_of_queryResult_json = json_encode($queryResult_json);

<script type="text/javascript">
var myArray = <?php echo $string_of_queryResult_json ?>;
myArray_object = JSON.parse(myArray);
</script>

… but it is easier and more efficient to just to treat your JSON as a JavaScript literal in the first place.

$queryResult_json = json_encode($queryResult);

<script type="text/javascript">
myArray_object = <?php echo $queryResult_json ?>;
</script>

I would just lead with the second part, and not even offer the double json_encode/JSON.parse as a solution at all ;) — IncredibleHat, Mar 06 '18 at 13:16
@IncredibleHat — The point of the stupid way is to demonstrate the background to the problem, not as a viable approach to a solution :) — Quentin, Mar 06 '18 at 13:18
Ok, I got it. My PHP part is correct, but single quotes around `` are bad. If I leave them out, I don't neeed JSON.parse() and my object is stored in myArray variable. Thanks for help! — Marin Leontenko, Mar 06 '18 at 13:45

Convert database records containing double quotes to JSON

1 Answers1