Writing secure and flexible SQL

Motivation

The main motivation for this post is to show how security and flexibility in SQL can not be compromised when writing complex queries.

Some challenges which we face are mainly the SQL Injection that makes us give up the flexibility, like just doing select * from table where {where_condition} and keeping it secure is difficult as the query has an arbitrary condition and if we missed it somewhere in the code where we are replacing this where_condition it is a security vulnerability.

To mitigate it, we have specific parameterized queries, like follows.

select * from table where field1 = $1 and field2 = $2;

This is great for small tables, but when table has more number of fields and there’s a need to have filtering based on some fields and not others, like conditionally add and remove where conditions.

That’s difficult!

Solution - simpler fields

Doing this is actually fairly simple, when and I learned about it when I came across this problem.

It is little bit repetitive but it’s worth it.

select * from table
where (@field1 == "" OR field1 = @field1)
    and (@field2 == "" OR field2 = @field2);

So to give a little bit of explanation in this it checks for the arguments if they are empty ignore the condition. i.e. if field1 is passed but is empty string it makes the first where condition true, and same goes to the second condition. But when the field1 is passed like “John” it will be checked and filtered.

This can work well with text where we assume all the values of the field are not empty (""). But in case if it isn’t the possible solution is to add another argument, like @is_field1 as boolean.

select * from table
where (not @is_field1::boolean OR field1 = @field1)
    and (not @is_field2::boolean OR field2 = @field2);

This will work for others as well.

Solution - multivalue filtering

In case when you want to filter more than one value for a field in table which is an actual valid scenario, it cannot use = as condition. (which is the main reason I wrote this post)

To use that we make use of IN operator, like field1 IN ($1, $2, ...) which is difficult as it may need arbitrary number of arguments, which makes securing it diffcult.

To avoid that, PostgreSQL has ANY which allows searching in Array.

One of the example is shown as follow.

select * from table
where (not @is_field1::boolean OR field1 = ANY(@field1::text[]))
    and (not @is_field2::boolean OR field2 = ANY(@field2::text[]));

I have tested this in PostgreSQL not sure if it is in ANSI SQL.

To put it simply, we search in the array and is essentially the operator IN but it doesn’t need arguments that are arbitrary but one argument that is of array datatype.

References: