I have a following problem/challenge:

Web application (ASP.NET 3.5) installed on corporate LAN and operates on SQL Server DB needs to provide ability to generate custom reports. These reports can be basically anything from underline DB, include complicated joints, unions and whatever you can think of. (Just selects, no Insert/delete/drop/update)

The easiest way to do it – allow to the system to execute SQL queries. System admin will add custom queries to the system and “regular” users will be able to execute them. If they need new query, they will ask Admin to create a query for them and then they will be able to run it by Query ID.

White List approach is not going to work here (at least I can’t see how).

What about black list? I was thinking about something like that:

blackList={"--", ";", "/*", "*/", "@@", "@",
                  "char", "nchar", "varchar", "nvarchar",
                  "alter", "begin", "cast", "create", "cursor",
                  "declare", "delete", "drop", "end", "exec",
                  "execute", "fetch", "insert", "kill", "open",
                   "sys", "sysobjects", "syscolumns",
                  "table", "update"};

Once again – the only person that can create such Custom Query is admin (and he most likely has full control over DB in any case).

Any help would be welcome.



  • 1
    Why do you need sanitation if only trusted users can create queries and those queries are sent from a trusted web server? Just defense in depth, or did I miss an attack? Jan 18, 2012 at 11:01
  • People do mistakes
    – AaronS
    Jan 18, 2012 at 11:11
  • Data theft is the biggest concern. A select or union is the most dangerous thing an attacker is going to do.
    – rook
    Jan 18, 2012 at 17:16
  • 1
    There are a number of tokens on that list (notably, CAST and datatypes) which should be allowed, for a variety of reasons. Also, how were you planning on checking the statement for these tokens? Depending on your strategy, the check can either be bypassed, or return a false positive (like if someone has a column named begin that's a timestamp?). You've also eliminated the use of cursors, which are usually used to read data in blocks (a strategy for decreasing application access counts), which feels wrong. Jan 18, 2012 at 19:05

4 Answers 4


You are providing a list to sanitize, which includes drop, create, exec,...

But if you only need "SELECT" access, it would be easier to just take away the rights the user who's executing the queries doesn't need. (least-privileged rather than all privileged )

If I were you I would define stored procedures because they are quite injection foolproof. Should this be too hard in your web application, then you can try Parameterized SQL where you use parameters.

Never go sanitizing yourself and then directly query on the database. Parameterized SQL is quite flexible and results in safer environment normally than you would have with Dynamic SQL.

Also bear in mind to not disclose any errors ! (I know it is security through obscurity, but that extra time you gain with it might enable you to detect it.)

  • 1
    These SQLs doesn't get any parameters. And I don't want that admin will have to create stored procedures. Least-privileged access sounds promising, but I would prefer to try to solve it on application level. Thanks
    – AaronS
    Jan 18, 2012 at 11:02
  • If you do not need to enter any parameters, how can they inject then ? Jan 18, 2012 at 11:08
  • Admin can write SQL query "Delete * from Table1" and application should protect from this.
    – AaronS
    Jan 18, 2012 at 11:13
  • 1
    aaah I see, well yea least privileged should do it then :) Jan 18, 2012 at 11:18
  • 15
    You can't solve it in application, there's ton of ways to obfuscate malicious query. Use db/table level access control and allow selecting only from needed tables. Jan 18, 2012 at 12:16

Read only account for starters, deny update/delete, etc. from the users.

Personally I like using stored procedures for such things. And if you can't trust your admins, who could do anything they probably want on the backend outside if your application, who can you trust?

  • 1
    Sometimes it is not about trust, but rather about protection from mistakes. eg. rm -rf * .txt = mistake. Jan 18, 2012 at 19:19

Best solution would be to execute all queries using an unprivileged database account with no write permissions for all data, read-only permission of some data, and no read permissions for secure data such as password hashes of other users (esp users that are admins).

If the end-user is providing any input that goes into the SQL query, make sure you are using bound parameters rather than string formatting/concatenation, which potentially gives the end user the ability to fundamentally change the type of query in SQL injection attacks.

Note that blacklists can often be bypassed very subtly. E.g., if you blacklist update make sure that UpDaTe is blacklisted as well as a string containing unicode characters (like úpdãtÊ) that your database may map to ascii characters after passing the blacklist.

Also be aware of the various other threats a malicious end-user may do besides altering your data; ranging from stealing your data (e.g., maybe force in a LIMIT 100) or denial of service from a very time-consuming query.

  • Because SQL tokens are case-insensitive, it's better to cast everything (including search strings, often) to a particular case, then check against the token in that case. If the database is going to convert things to a specific encoding, I'd attempt to do it at the application layer too, before casing/blacklisting, just in case. Jan 18, 2012 at 18:58

Put your users in the db_datareader database role. Use VIEWS to limit what they can query (be sure and specify SELECT column list, not a SELECT * in the CREATE VIEW statement). Publish the VIEWs and what they provide. Your users SELECT from the VIEW and get the data they want.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .