7

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.

Community
  • 1
  • 1
Mr Shoubs
  • 14,629
  • 17
  • 68
  • 107
  • Why do you often need to add or remove columns? What good reason is there for such a need? – ypercubeᵀᴹ Apr 07 '11 at 17:02
  • @ypercube, I don't need to do this often. I just didn't want to maintain the sql in my filter object for every change in my database and at the same time provide a flexible object for filtering data. – Mr Shoubs Apr 07 '11 at 17:08

3 Answers3

23

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'
Kevin Burke
  • 61,194
  • 76
  • 188
  • 305
Eelke
  • 20,897
  • 4
  • 50
  • 76
  • 1
    +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 Apr 07 '11 at 17:49
  • 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 – Mr Shoubs Apr 07 '11 at 18:00
1

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();
            }
        }
Oberdan
  • 304
  • 1
  • 4
  • 9
  • 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? – Stavros Koureas Jun 29 '22 at 07:58
1

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.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • 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 Apr 07 '11 at 17:50
  • 1
    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. – Thomas Mueller Apr 07 '11 at 19:13
  • The client is the client application. +1's for correct answers, though @Eelke has provided the best idea to answer my question. – Mr Shoubs Apr 07 '11 at 23:05