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?
