1

My needs are very similar to this question about where to store business logic and this question about managing SQL code in a data table. But I would like more specific tips on how to manage the risks of SQL injection and Sandboxing that are alluded to in the answer to that second question.

My applications deal with data submissions that involve thousands of validation rules which need to be updated frequently. I would like to manage these rules as error conditions (formatted as SQL "WHERE" clauses) stored in a data table (ex: "Total != Column1 + Column2"). I would like analysts skilled in SQL to have access to directly insert and update these business rules, either in Management Studio or through an intranet application. One or more intranet applications or SQL processes would then use the error clauses in that table to validate data stored in other tables in the database.

  • Is it fundamentally a bad approach to save arbitrary code to a database, even if access to that table is restricted to a small number of users?

  • As my applications execute SELECT statements using the retrieved WHERE clauses, is there an effective, reliable way to limit the access rights of any malicious code pulled from the database? (I'm not confident that I can reliably sanitize a retrieved WHERE clause.)

  • Is there a better place to store frequently updated business logic, that is similarly simple to manage, but with better security?

  • Where can I go to find general principles about managing executable code in a database?

Community
  • 1
  • 1
Mac
  • 1,201
  • 2
  • 15
  • 28
  • 5
    I would consider side-stepping all these issues and storing your business rules not as SQL fragments, but as a DSL (Domain Specific Language) that you design. Your DSL parser would turn a business rule into the SQL fragment that you can include in your query, and in a way to keeps any unsafe code from being passed through. – Wayne Conrad Feb 11 '14 at 18:40
  • _"validation rules which change frequently"_ Define frequently. – Alex Howansky Feb 11 '14 at 21:08
  • Really by "frequently" I mean "as needed." Formal policy changes are annual in our case. However, as logic errors are identified, and as unenforced business rules are identified, we desire the ability to quickly roll out small changes, and to allow analysts direct access to audit the existing logic for accuracy. – Mac Feb 11 '14 at 22:22

1 Answers1

1

Given this statement of yours:

I'm not confident that I can reliably sanitize a retrieved WHERE clause.

The answer to the following question is "yes."

Is it fundamentally a bad approach to save arbitrary code to a database, even if access to that table is restricted to a small number of users?

Don't rely on restrictive access privileges. An attacker can cause mischief (at least denial of service) using only SELECT privilege -- or even no privilege at all.

I agree with the comment from @WayneConrad, that you would be more secure if you design your business logic in a DSL, and then write code to interpret your own DSL and convert it into a limited set of WHERE clauses. By parsing and validating your own DSL, you have a greater likelihood of blocking malicious code (unless your DSL becomes too flexible).

You're going to have to write a parser and validator, either for SQL or for a DSL. There's no other way to do this with any kind of security.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I'll strongly consider this. Thank you! (And Wayne!) – Mac Feb 13 '14 at 13:34
  • More generally speaking, it seems like storing code in a database is becoming more common (news sites that retrieve html for example.) Is my use case dangerous, or is it fundamentally dangerous for an application to trust code that is in a database? – Mac Feb 13 '14 at 13:38
  • Depends who put it there. If you have high confidence that the HTML stored in the database (or in a cache, with the same problems) was "clean" and avoids XSS vulnerabilities, then you can do it. But if there's any chance that malicious content was included in the HTML fragment, it's not safe. – Bill Karwin Feb 13 '14 at 17:29