0

My objective is to create a system using a PLPGSQL function that allows my users to create and run custom reports on their data.

CREATE FUNCTION run_report(query TEXT, filter JSON) RETURNS JSON AS 
$$
  ...run and return report as JSON
$$ LANGUAGE PLPGSQL;

The will have a menu of tables, say foo1,foo2,foo3,foo4 and understand the relations between them.

I want them to be able to upload a query and then apply a filter to it dynamically. So for example:-

WITH CTE AS (
         SELECT baa, baa2, baa3#>'{ids}' as ids FROM foo1 
         LEFT JOIN foo2 USING(foo_id)
         WHERE_CLAUSE_TO_INSERT
) SELECT json_agg(row_to_json(CTE.*)) FROM CTE

My function will substitute WHERE_CLAUSE_TO_INSERT with a where clause constructed from a filter sent as follows:-

{
 "baa2.id"  : "12",
 "baa.name" : "John.*'
}

This would make the WHERE_CLAUSE_TO_INSERT "baa2.id::TEXT ~'12' AND baa.name ~ 'John.*'

I did this by creating an array that looks like:-

["baa2.id::TEXT ~'12'"," baa.name ~ 'John.*'"]

and then creating the string with:-

array_to_string(where_clause, ' AND ')

The question

I am guessing this is open to SQL injection. Normally I would construct a query with $1,$2... placeholders and use EXECUTE 'SELECT ... WHERE foo = $1' USING bar_variable. The problem is that some of these queries are complex and I cannot know how many parameters will be passed.

Is there a better way of doing this?

user1331131
  • 427
  • 6
  • 20
  • You can declare the function to have a variable number of parameters: http://www.postgresql.org/docs/current/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS and http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS –  Jan 12 '15 at 14:18
  • Yes but that does not help as you cannot EXECUTE a placeholder query with variable parameters – user1331131 Jan 12 '15 at 15:41
  • Here are complete plpgsql and sql functions to build queries dynamically, efficiently and safe against SQL injection: http://stackoverflow.com/questions/17353445/test-for-is-null-or-value-in-function/17355733#17355733 – Erwin Brandstetter Jan 13 '15 at 10:06
  • Those queries ahve fixed table names and inflexible where conditions that can only be '='. I can see the principle and it would work if all my report queries were structured in a similar manner but they are not. – user1331131 Jan 13 '15 at 13:39
  • @user1331131: Well, only *one* base table is fixed in the plpgsql variant of [the referenced solution](http://stackoverflow.com/questions/17353445/test-for-is-null-or-value-in-function/17355733#17355733), the rest is dynamic. You can make all of them dynamic ... I fixed dynamic column references. – Erwin Brandstetter Jan 14 '15 at 20:55

0 Answers0