Check if T-SQL is only select and does not affect any change

Question

I have a dynamic price policy application in C# that execute T-SQL select statement and fetch value from query. but as you know, user can execute dangerous query like drop, update, delete, insert and etc.

So, how can I check a T-SQL query and ensure that the query is only a select statement?

I don't want to limit this with permission, because my user has a privilege to update or insert in other part of my application.

Really is this a hard question and Hasn't anyone faced this challenge before?

By using a parameterized SELECT - I don't think you'll want users to be able to execute arbitrary SQL statements on your database anyway? — 500 - Internal Server Error, Mar 01 '23 at 23:17
I want to limit users to execute only select statement and prevent other statment in specific part. how can i do that? — Aliasghar Ahmadpour, Mar 01 '23 at 23:20
Oof, I wouldn't even consider this in most scenarios, but if you trust the input as a custom reporting engine, maybe... maybe a read-only replica, or a separate login with read-only permission. — Marc Gravell, Mar 02 '23 at 00:41
Are you saying that the user is able to enter freely any SQL query? — Squirrel, Mar 02 '23 at 01:12
Just use a separate user with readonly rights to run those queries. — siggemannen, Mar 02 '23 at 07:46
`select * from Table1, Table1, Table1, Table1, Table1, Table1, Table1, Table1, Table1` - nothing dangerous, it's just a `select` after all, innit? — Roger Wolf, Mar 02 '23 at 08:04

score 2 · Answer 1 · answered Mar 01 '23 at 23:30

It is not a good security practice to allow any SQL statement to be fed in via inputs as raw SQL from the front end.

You could provide a UI where they can create parameters so that in the back end, these could be fed to parameterised queries. Then you could create a condition builder that essentially allows them to build their query but through UI instead of SQL.

So for example, if one of the queries they might write is SELECT * FROM Products WHERE Id = 1, you could instead provide a UI on which they can create a parameter called Id and give it a value of 1. Then they could have a dropdown to pick from a list of available tables, followed by other UI which allows them to construct their WHERE clause.

It's pretty heavy compared to just passing through the raw SQL, but it would allow you to control what you put in your query in the back end, which would of course be using parameters.

Parameterised queries are standard security practice for any inbound requests to DBs using SQL from the front end. Here's a basic intro to them using SqlCommand in C# and VB.NET

dear @nick-proud Under normal circumstances you are right, but i could not do that I said that my application is dynamic. I have so many complex tsql query and it's not like simple select statement — Aliasghar Ahmadpour, Mar 01 '23 at 23:51
Also, I don't want to have an concrete implementation for each select — Aliasghar Ahmadpour, Mar 01 '23 at 23:52

score 0 · Answer 2 · answered Mar 01 '23 at 23:22

0

Can't you just check the sql query string to see if it conatains those dangerous commands (drop, update, delete, insert)?

Also, this approach sounds dangerous anyway. As has already been mentioned you should be using parametized queries or they can cause damage, even with just a select.

answered Mar 01 '23 at 23:22

iKnowNothing

920
1
10
17

No, I want to use standard way to apply this limitation. checking known dangerous statemnt with Contains is not good approch – Aliasghar Ahmadpour Mar 01 '23 at 23:26
3

Execute ('upd'+'ate ...') – Marc Gravell Mar 02 '23 at 00:40
2

The standard is either security or not allowing this. There's no standard for what you wanna do because it's a bad idea. – siggemannen Mar 02 '23 at 07:45

score 0 · Answer 3 · answered Mar 02 '23 at 04:25

You could use execute to execute the query with an execute as clause that specifies a user account with readonly access to appropriate tables and views. If there are specific columns in a table that should not be accessible then deny access to the table and grant access to a view that provides only the allowable columns.

You are still depending on security settings to protect against hanky panky. A thorough examination of the query using a T SQL parser would be better, but a rigorous analysis of every possible coding trick in a query isn't trivial. You could check entities (tables, columns, functions, ...) against an allow list, restrict expressions, ... .

Check if T-SQL is only select and does not affect any change

3 Answers3