How do I prevent Linq DataContext.ExecuteQuery from modifying data

Question

I have a custom scripting engine that allows the user to execute queries on a TSQL data set. The query is free form and maps directly to a call to 'System.Data.Linq.DataContext.ExecuteQuery'

I want the user to be able to query data, and return results, but NOT modify the data as this would be a security issue.

Currently I check the string for the following keywords: insert, update, alter, create, delete, drop, truncate, merge and exec.

However, I suspect there is a better way, like setting the context to read only or some other method that doesn't rely on my remembering or parsing all possible sql statements, etc..

Always operate in a transaction and always rollback after executing. — Crowcoder
– Crowcoder, Commented Feb 13, 2015 at 21:02
Now that's outside of the box thinking. :) Certainly sounds like it would work. — NebulaSleuth
– NebulaSleuth, Commented Feb 13, 2015 at 21:29
The inherent issue here is that the DataContext is superseded and ExecuteQuery is directly executed against the database. Any handy-dandy DataContext.ObjectTrackingEnabled flag would be ignored, since DataContext.SubmitChanges() does not need to be called for the query to be executed. As such, you're limited to rolling back transactions, adding new users, or manually examining your Sql Statements for naughty keywords. — David L
– David L, Commented Feb 13, 2015 at 21:46

Bas · Accepted Answer · 2015-02-13 20:33:49Z

4

How about connecting to the database using a user that only has the data reader permission? This would eliminate the problem completely and enables out-of-the-box decent error reporting. Checking a SQL string for words seems sketchy at best, for example a user can do SELECT ... INTO which is not handled by the keywords you mentioned up until now.

answered Feb 13, 2015 at 20:33

Bas

27.2k8 gold badges56 silver badges89 bronze badges

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

1 Comment

NebulaSleuth Over a year ago

I agree the keyword method is 'sketchy'. Using another user would certainly handle it. However you just gave me a new problem. Now I will need to modify the installer to make sure the user is properly created. :)

Collectives™ on Stack Overflow

How do I prevent Linq DataContext.ExecuteQuery from modifying data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related