CODEWITHSUNDEEP
c#sql-serversqlconnectionquery-parser
Rachel
Rachel

Reputation: 132548

Parse a SQL statement to see if it is anything other then a SELECT statement?

Is there a good way to parse a SQL statement to ensure it contains only a SELECT statement and nothing else? I am using C#, System.Data.SqlConnection, and MS SQL Server

Upvotes: 1

Views: 2413

Answers (5)

Kell
Kell

Reputation: 3317

I think parsing the sql will be the solution: from another question

Upvotes: 0

Alex
Alex

Reputation: 14618

I guess you can come up with a regular expression, but it is likely that it won't be 100% safe.

The best way to do this is to:
1. Either write stored procedures and views, and limit the user's rights to using only them. (and SELECT statements on certain tables)
2. Build a Data Abstraction Layer. You build the queries, not someone else. Let the others access only some of your methods you expose.
3. Use LINQ to SQL, but conceal the DataContext object, so no changes to the database could be made.

Upvotes: 1

SLaks
SLaks

Reputation: 887225

You should connect to the database as a user that doesn't have permission to do anything other than a SELECT.

This way, any non-SELECT statement will be unable to execute.

This is the most secure solution possible, short of duplicating SQL Server's parser.

Upvotes: 6

Gabriel Magana
Gabriel Magana

Reputation: 4526

Check to make sure the statement begins with a SELECT keyword, and then make sure the statement contains no semicolons (which would begin another SQL statement) that are not parts of literal strings.

Upvotes: -1

GendoIkari
GendoIkari

Reputation: 11914

Since a SELECT statement would need to be at the very beginning of the statement, you can just check the string to see if the first 6 characters are SELECT:

if (stringSql.Substring(0, 6).ToUpper() == "SELECT")
{
    //execute statement
}

Upvotes: -2

Related Questions