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?

Could you share the piece of SQL that is sent to the database? The one that fails. Check the PostgreSQL log or your own error log — Frank Heikens
– Frank Heikens, Commented Mar 22, 2024 at 18:56
@FrankHeikens yes I'd be happy to. Just to be clear, you're looking for the SQL query itself or the function code itself — Ethan
– Ethan, Commented Mar 22, 2024 at 18:59
No, just the query that is created by your code, and the parameters sent by your code, to the database. It should be in the logs. — Frank Heikens
– Frank Heikens, Commented Mar 22, 2024 at 21:49
What's up with const sanitizedBody = event.body.replace(/'/g, "''");? Are you actually using that somewhere? This looks wrong. — Bergi
– Bergi, Commented Mar 23, 2024 at 0:13
@Bergi apologies, I've removed that unused constant in the edited question. — Ethan
– Ethan, Commented Mar 24, 2024 at 16:12

Bergi · Accepted Answer · 2024-03-24 16:21:02Z

0

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)]
);

answered Mar 24, 2024 at 16:21

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related