For an application that I am planning, I have the requirement that input comes from about 30 to 50 SQL server source tables, and needs to be inserted into one target table. The source tables have slightly different structure, but in the end, each target record would come from exactly one record of one of the source tables. There should be a possibility for users to specify for each source table and each target column an expression like e. g. case when col1 like 'a%' then 'ABC' else Left(col5 + col6, 10) end or just 'xxx' or col4.

An easy way to implement this would be to just build a dynamic SQL select clause on a source table from the list of these expressions, and hence let the SQL engine do the expression interpretation. This would be rather flexible, as more or less every valid SQL expression would be available for users. A more complicated implementation would be to define a set of expressions which the application supports, and build an expression parser, which would interpret all the expressions, and output them in the select clause, and thus would of course have full control of the generated SQL. But we would need to anticipate the required set of expressions.

Of course, if naively implemented, the first approach would offer the potential for SQL injection. But if I would create one user ID per source table that has no permission except select on this one table, and execute the dynamic SQL using that user id, I do not see any potential for successful SQL injection. And even error messages should not reveal anything useful for an attacker, as the structures of the source and target tables would be known to the users anyway. Any useful attack that would try to change anything in the database would not be possible due to the limited permissions.

Is there any SQL injection potential with this approach that I did not see?

  • one question is whether you can specify user permissions with sufficient granularity on the database platform that you're using. For example an attacker might be able to get access to more information about your Database system by using built-in functions like @@version in MS SQL Server. – Rоry McCune Mar 10 '17 at 14:36
  • Since the data must be inserted into a target table, the user will also need permission to do that, right? So isn't there a potential to at the very least insert millions of rows of garbage data? Maybe also to delete other rows etc? – Pascal Mar 10 '17 at 16:26
  • Oh, and you wouldn't necessarily need to build an expression parser. If your users interacted via a web page, you could use forms with select boxes for operators such as and, or, like etc and for column names and text input fields for values to let the user "build" an expression. That would then be trivial to safely translate into the required SQL. – Pascal Mar 10 '17 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.