.Net. Prevent modifying data when running sql query

Question

Is there a way to execute sql on MS SQL Server from code in C#/VB.Net and guarantee that data wouldn't be changed? I need only loading data, but can't be sure that sql expression wouldn't contain insert/update/delete statement. I've tried SqlDataAdapter and SqlCommand.ExecuteReader(), but they allow modifying data. I understand that program just sends command to sql server and doesn't check it. The one option I see now is to use connection with read-only user. It will require very few changes in code. But it's not completely save, because connection string stored in config file and can be easily changed (and I cannot use hardcoded values). Some more complex way is to parse sql to check if it is actually query. I am not sure if there is some ready and reliable solution for it.

Is there any way to prevent running sql that can modify data from code?

"Is there a way to execute sql on MS SQL Server from code in C#/VB.Net and guarantee that data wouldn't be changed?" - Yes. Permissions! The same way you prevent anything from being changed. — Mitch Wheat
– Mitch Wheat, Commented Jul 16, 2014 at 9:26
@MitchWheat I have user with only db_datareader role now to run queries. Just wondering is it possible to make it even more safe. — alxK
– alxK, Commented Jul 16, 2014 at 9:34

Ian Ringrose · Accepted Answer · 2014-07-16 09:48:19Z

1

Use a transaction.

Start a Transaction
Run the SQL
Rollback the transaction

I have done this in the past for running unit tests, so that the test database is not changed by any of the tests.

Have a Look at SqlTransaction and distributed transactions.

However this will not stop tables being dropped etc, so you still need the SQL user to have limited rights, if you don’t trust the SQL you are running.

Another option to consider is database snapshots, but they are only of use you only have one process accessing SQL server, so good for unit tests, but maybe not for what you are doing.

edited Jul 16, 2014 at 9:48

answered Jul 16, 2014 at 9:40

Ian Ringrose

52.2k58 gold badges218 silver badges325 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sleiman Jneidi · Accepted Answer · 2014-07-16 09:32:54Z

1

You can create a user as a member of db_datareader group, db_datareader users, only have readinging permissions, and now form your C# app you can connect to MSSQL using this user

answered Jul 16, 2014 at 9:32

Sleiman Jneidi

23.4k14 gold badges60 silver badges80 bronze badges

Comments

Meligy · Accepted Answer · 2014-07-16 09:34:23Z

1

How about executing with a SQL user who doesn't have any permissions that allow them to modify the data? I'm talking SQL permissions in here. Then whatever code you pass at it, when it tries to modify the data, it'll get denied and will return an exception.

Having said that, I'd never let others make the query for me. I'd only expose methods for them to call and send their inputs as SQL parameters to the scripts I wrote myself (I, my team, my company, you get what I mean).

answered Jul 16, 2014 at 9:34

Meligy

36.7k11 gold badges90 silver badges114 bronze badges

Comments

mehdi lotfi · Accepted Answer · 2014-07-16 09:33:44Z

0

You can have following method :

have only read permission to your table.
have flag field for logical lock your specific rows of your table (by using instead of insert, update and deleted trigger and control flag column).
have partition table with multiple file group and set some of your file group as read only.

answered Jul 16, 2014 at 9:33

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

Collectives™ on Stack Overflow

.Net. Prevent modifying data when running sql query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related