0

I have a JSON containment query as below. The query looks for matching objects inside arrays. This works.

The values of Name and DataType will be passed in to a .Net function, and the query built in NpgSql. I want to parameterize the values (city, string) to avoid SQL injection. How to achieve this?

As per this GitHub issue, I've tried building the json using jsonb_build_object, but I need to build arrays of objects. I'm told aggregate functions can't be used in WHERE, so that doesn't work.

So, can I build an array of objects to add the parameter values to, or is there a better way of avoiding SQL injection in this query?

We are currently using Postgres 10.18, so ideally the solution should work in that. However, we will soon upgrade to 14, so better solutions for 14 would also be of interest.

SELECT name FROM (SELECT name, fields -> 'dimensions' as dimensions
from data) x
WHERE dimensions @> '[{"Name": "city"}, {"DataType": "string"}]'

1 Answer 1

0

you can build both JSON arrays and objects without using aggregates as long as the size of each is fixed:

WHERE dimensions @> jsonb_build_array(jsonb_build_object(?,?),jsonb_build_object(?,?));

But maybe you should be using some C++ library to build the JSON and pass it already formed down to PostgreSQL. If you don't want to be dealing with JSON on the client side, you likely should not be using it on the db side either.

3
  • Thanks, that works (I think I must have tried everything else but that). Regarding building the JSON on the server first, this is what I was doing, but the values came from user input, so surely that JSON would be susceptible to SQL injection without parameterization.
    – Paul Guz
    Sep 9, 2022 at 13:08
  • For clarity, the npgsql would look like jsonb_build_array(jsonb_build_object('Name', @name, 'DataType', @datatype))
    – Paul Guz
    Sep 9, 2022 at 13:11
  • @PaulGuz Presumably a client JSON library would already offer whatever escaping is needed to generate valid JSON of the desired structure. In addition if the resulting attempted JSON were sent to PostgreSQL as a single bound parameter (not interpolated) it would simply reject any that somehow were not valid. I certainly would not suggest your roll the JSON by hand and then insert it into the SQL command by hand.
    – jjanes
    Sep 9, 2022 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.