How to dynamically query column names using Postgres / NpgSQL

Question

I have a filter object to query a table with many columns and instead of writing a condition that spans all columns (allowing for additional filtering), like this:

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

for each column. Instead, I would ideally want to pass the field name as a parameter:

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

which is not possible since columns are required during parsing (similar to this answer given here ).

How do you overcome this? . I really do not want to support SQL when new columns are added or removed (as in the first example), and I think it would be dangerous for me to build the column names on the command line directly, as this could allow SQL injection.

Please note that this code is behind the web service.

Answer 1

Just make sure that end users cannot directly specify column names, and you should be safe when building the query manually. If you need to find out which column names are valid at run time, 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 create an SQL statement on the fly.

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

Answer 3

Example:

 NpgsqlCommand command = new NpgsqlCommand(SQL, Connection); Npgsql.NpgsqlDataReader Resource = command.ExecuteReader(); while (this.Resource.Read()) { for (int i = 0; i < this.Resource.FieldCount; i++) { string field = this.Resource.GetName(i).ToString(); } }