How to query column names dynamically using Postgres/NpgSQL

Question

I have a filter object to query a table with many columns, and rather than write a condition covering all columns (allowing for optional filtering) like this:

WHERE ((:value0 IS NULL) OR (column_name0 = :value0)) AND ((:value1 IS NULL) OR (column_name1 = :value1)) AND... etc

for every column. Instead, I'd ideally I'd like to be able to pass in the field name as a parameter:

WHERE :column_name0 = :value0 AND column_name1 = :value1 AND... etc

which isn't possible as the columns are required at parse time (similar to this answer given here).

How do you overcome this? - I don't really want to have to maintain the SQL when new columns are added or removed (as you would have to in my first example) and I think it would be dangerous for me to construct the column names into the command string directly as this might allow for sql injection.

Note that this code is behind a web service.

Answer 1

Just make sure end users cannot provide the column names directly and you should be safe when constructing the query manually. If you need to find out what column names are valid on runtime you can use the following query:

SELECT column_name
FROM information_schema.columns
WHERE table_schema='public' AND table_name='yourtablename'

Answer 2

I think the easiest solution is to construct the SQL statement on the fly.

SQL injection is not possible if you use parameters for user provided data.