SQL query sanitation (black list)

Question

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.

Thanks

A

Answer 1

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.)

Answer 2

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?

Answer 3

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.

Answer 4

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.