postgres dropping '\' in json when sent with pg_update

Question

I'm using pg_update to update a json field in my postgres database, the json I'm trying to send is structured like this:

[{
    "rt": 3698,
    "responses": "{\"Q0\":\"asdf asdfasd \"}",
    "exp_phase": "questionnaire",
    "question": 1,
    "random_id": "oe7to9bgvkgf",
    "condition": "debug",
    "exp_version": "17.1"
}]

This is valid json. This is exactly what it looks like when I send it via pg_update (it's the output of var_dump($update_field) in code below). But, my database is refusing because it's not valid json. I tried sending to a text field instead and that works, but when I inspect the data in postgres my backslashes are gone. So that solves why I'm getting invalid json errors -- they need to be there to make the "responses" field valid.

Obviously \ is getting dropped on it's way into the database; either by my database or by whatever happens in pg_update. I tried adding another backslash, in hopes that would allow the single backslash to persist, but when I do that, pg_update tells me that it's expecting NULL, string, long or double value for PostgresSQL 'text'. So by add another backslash, I've made it not text anymore.

How can I send the json above via pg_update so it arrives exactly as it is above, with a single backslash in the places it's required to make my json valid?

<?php
  // this path points to our configuration file.
  include('database_config.php');

  // get an array that has key "json_data" and value "the json array from jspsych"
  $data_array = json_decode(file_get_contents('php://input'), true);

  // get the field we want to update
  $update_field =  array("json_data" => $data_array['json_data']);

  // add another backlash to any that exist to help it make it to postgres
  foreach ($update_field as &$str) {
    $str = str_replace("\\", "\\\\", $str);
  };
  unset($str);

  // conditions that need to be met; get the row with the current random_id
  $condition = array('random_id' => $data_array['random_id']);

  // connect to database
  $dbconn = pg_connect( "host=$host port=$port dbname=$dbname user=$user password=$password sslmode=require" )
      or die ("Could not connect to database\n");

  // update the runs table with the json data from the study
  $res = pg_update($dbconn, 'research.runs', $update_field, $condition) or die ("Could not update run with data\n");
  pg_close($dbconn);
?>

score 0 · Answer 1 · answered Oct 30 '20 at 12:02

Ok, I figured this out thanks to this question and Ryan Culpepper's answer.

It is indeed an issue with escape characters. Options including using pg_escape_string(), but as @ryan-culpepper points out, it's not really a good idea to create SQL queries using string interpolation. He suggests using queries with parameters instead.

So, now I'm doing this and it works perfectly.

<?php
  // update the database run with the json data at the end of the study
  // this path points to our configuration file.
  include('database_config.php');

  // get an array that has key "json_data" and value "the json array from jspsych"
  $data_array = json_decode(file_get_contents('php://input'), true);

  // connect to database
  $dbconn = pg_connect( "host=$host port=$port dbname=$dbname user=$user password=$password sslmode=require" )
      or die ("Could not connect to database\n");

  var_dump($data_array);

  // update the runs table with the json data from the study
  $res = pg_query_params($dbconn, 'UPDATE research.runs SET data = $1 WHERE random_id = $2',
    array($data_array['json_data'], $data_array['random_id']))
    or die ("Could not update run with data\n");

  pg_close($dbconn);
?>

postgres dropping '\' in json when sent with pg_update

1 Answers1