Semantically Identical JSONB Object Passed Into Postgres Function Cannot Be Parsed When Using Placeholders

Question

I have two identical JSON objects.

First Object	Second Object
[{"id": 123,"firstname": "Mike","lastname": "Smith","ani": 123456,"email": "[email protected]"}]	[{"id": 123,"firstname": "Mike","lastname": "Smith","ani": 123456,"email": "[email protected]"}]

However, postgres (or at least PgAmdin 4) isn't treating them the same when it comes to calling a postgres function...

When I call the function directly from pgadmin4 like so:

select * from myfunction(123, abc, '[{"id": 123,"firstname": "Mike","lastname": "Smith","ani": 123456,"email": "[email protected]"}]')

notice the ' marks surrounding the object everything works perfectly.

However, when I call the postgres function from an AWS Lambda function written in node js like so:

let callfunction = `SELECT * FROM myfunction($1, $2, $3)`;
const dataResult = await client.query(callfunction, [123, abc, '[{"id": 123,"firstname": "Mike","lastname": "Smith","ani": 123456,"email": "[email protected]"}]']);

This fails, saying:

invalid input syntax for type json

I my mind, they are identical function calls--the javascript SHOULD build an identical query, anyway.

However, there is something strange when it comes to this placeholder, because not matter what combination of characters, apostrophes etc, it will always fail.

The ONLY way I have been able to get this to work is to just include the object in the actual function call itself like so:

let callfunction = `SELECT * FROM myfunction($1, $2, '[{"id": 123,"firstname": "Mike","lastname": "Smith","ani": 123456,"email": "[email protected]"}]')`;

Again, this "works" but is an obviously a BIG no-no in when it comes to sql injection vulnerability mitigation...

Why does this function call behave like this when parameterized?

Answer 1

I cannot reproduce the issue, this code should indeed work. Notice that the apostrophes have nothing to do with the error message, and they serve different purposes anyway: in SQL code they delimit the SQL literal and would be escaped as '', whereas in JavaScript code they delimit the string literal and would be escaped as \'.

However, two suggestions:

• Use ::json to specify the type of the parameter, to make sure any potential myfunction overload is resolved correctly:

  let callfunction = `SELECT * FROM myfunction($1, $2, $3::json)`;
  

• Use JSON.stringify, not a literal json string, to make sure you're passing valid JSON:

  const value = [
    {"id": 123,"firstname": "Mike","lastname": "Smith","ani": 123456,"email": "[email protected]"}
  ];
  const dataResult = await client.query(
    callfunction,
    [123, abc, JSON.stringify(value)]
  );