Preventing SQL injection in a report generator with custom formulas

Question

For my customers, I am building a custom report generator, so they can create their own reports.

The concept is this: In a control table, they fill in the content of report columns. Each column can either consist of data from DIFFERENT DATA SOURCES (=tables), or of a FORMULA.

Here is a reduced sample how this looks:

Column | Source  | Year   | Account | Formula
----------------------------------------------
col1   | TAB1    | 2015   | SALES   | (null)
col2   | TAB2    | 2014   | SALES   | (null)
col3   | FORMULA | (null) | (null)  | ([col2]-[col1])

So col1 and col2 get data from tables tab1 and tab2, and col3 calculates the difference.

A stored procedure then creates a dynamic SQL, and delivers the report data. The resulting SQL query looks like this:

SELECT 
    (SELECT sum(val) from tab1 where Year=2015 and Account='SALES') as col1,
    (SELECT sum(val) from tab2 where Year=2014 and Account='SALES') as col2,
    (
       (SELECT sum(val) from tab1 where Year=2015 and Account='SALES')
       - 
       (SELECT sum(val) from tab2 where Year=2014 and Account='SALES')
    ) as col3 ;

In reality it is far more complex, because there are more parameters, and I'm using coalesce(), etc.

My main headache are the formulas. While they give users a very flexible tool at hand, it is total vulnerable for SQL injections.

Just wanted to know if there is some simple way to check a parameter for a possible SQL injection.

Otherwise I think that I need to limit the flexibility of the system for normal users, and only "super users" get access to the full flexible reports.

Answer 1

not really - many injections involve comments (to comment out the rest of the regulare statment) so you could check for comments (-- and /*) and the ; sign (end of statment).

On the other side if you allow your users to put anything into the filters - why should not someone write a filter as 1 = (select password from users where username = 'admin') to provoke an error message Error converting 'ReallyStrongPassword' to integer'?

Furthermore I guess that performance will be a much bigger problem as injection if I see your queries (it will read tab1 and tab2 twice instead only once if you would write it 'regular').

Edit: You could check for SQL codewords as select, update, delete, exec ... in the filter parameter, to harden your code / queries.