CODEWITHSUNDEEP
c#sql-injectionsqlcommanddmlsqlclient
Paweł Biesiada
Paweł Biesiada

Reputation: 21

Disable DML queries in SQLCommand C#

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?

Upvotes: 2

Views: 800

Answers (2)

BhavO
BhavO

Reputation: 2399

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

Upvotes: 0

Jared Moore
Jared Moore

Reputation: 3795

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.

Upvotes: 3

Related Questions