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.

Solution 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'

Solution 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.

Comments

[Mr Shoubs]

+1 for the idea about getting valid column names. note - as this is a web service, the client software provides the filtering information, and although security/authentication is implemented, it might still be possible to call the webservice with data other than that from the client.

[Mr Shoubs]

Yes, on the fly is an easy solution, but as this is a web service with the client passing in the filtering criteria, including column information it may still be open to attack.

[Mr Shoubs]

I'm going to use your idea to store the (allowed) column names server side in a dictionary of column names (inside a dictionary of tablenames) at service startup. The client can the refer to the key and not the actual names of the table or column(s) to use in the filter object

[Thomas Mueller]

You are right, if the column names are provided by the user then it's dangerous. One solution (not perfect) is to only allow characters (A-Z, a-z) and the underscore (_). And possibly digits (0-9), but not at the beginning.

[Mr Shoubs]

The client is the client application. +1's for correct answers, though @Eelke has provided the best idea to answer my question.

[Stavros Koureas]

This solution is working for dynamic queries (expressions), but i am unable to get precision and scale from numeric types or length form varchars, any ideas?