2

Problem

I'm writing a GUI application that allows users to generate an Excel file based on SELECT SQL query user enters in TextBox. It will connect to SQL server, run select over database, fill DataTable object and push that data to an Exel file. The way I have developed application is vulnerable for SQL injections and user may be able to pass any DML query such as DELETE OR UPDATE.

Question

Is there a way in SQLCLient library to prevent user from entering DML queries and executing them? Can I somehow enforce SQLCommand object to throw an exception when DELETE command is passed?

Paweł Biesiada
  • 21
  • 3
  • 8
    Create a database user with only select grants, and use this user for the connection, and then handle database SqlException when executing the command – BhavO Jul 01 '15 at 17:10
  • 1
    @BhavO is good approach but it also can get you system exposed (if you persist in it auth data for example) depending of your needs you can also create views and just allow user to select in that views or use a dynamic parametic queries – jean Jul 01 '15 at 17:13
  • 1
    Definitely, many ways to do it, but I think its least change required, depends on other variables too. – BhavO Jul 01 '15 at 17:16
  • Thanks BhavO, but I was more interested in preventing such situation in C# code. – Paweł Biesiada Jul 06 '15 at 09:51

2 Answers2

3

The correct way to do this is to create a database user with only select grants to the specified tables or views as described by BhavO and jean in comments.

Why is this the correct way to limit the T-SQL commands?

  1. Doing it client-side is significantly more complex. There is a T-SQL parser library that is provided by Microsoft, but do you really want to spend your time writing and testing tree visitor code that ensures you only have SELECT commands that only query some certain tables? Also now you have to worry about keeping this parser library component up-to-date with SQL Server releases which might have new SELECT query syntax that is not understood by the older parser library and causes errors in your app. Why not delegate the T-SQL parsing to the component in your system that is already designed to do that, which is SQL Server?
  2. Doing it client-side provides no actual security. Security of a server needs to be implemented by the server, not by its client code. The client code is running on the user's machine, so the user has total control over what is being executed. This means a malicious user can potentially (1) decompile and edit out the "DML disable" check component and then run the edited binaries, therefore skipping the check, or more practically (2) use network inspection tools to determine how your client app is connecting to the service (i.e. the connection string) and then just directly connect using that connection string in SSMS or SQLCMD or whatever and own your server. So all of the complicated parsing logic really hasn't slowed down an attacker at all.

These reasons are (among others) why GRANT, DENY and so on exist in SQL Server in the first place. They are good (mature, well-tested, easy-to-use) tools that are implemented in the correct place in the stack.

Jared Moore
  • 3,765
  • 26
  • 31
0

Create a database user with only select grants, and use this user for the connection, and then handle database SqlException when executing the command.

BhavO
  • 2,406
  • 1
  • 11
  • 13