check select statement is valid or not in c#.net

Question

i want to check select statement(string) is valid or not in c#.net, if select statement is right then retrieve data and fill dropdown list box else drop down should be empty

Answer 1

How often would the select statement be invalid? Seems like a simple try/catch block around the execution of the SQL might be sufficient.

As an aside, I hope you aren't making an app that would allow someone to type in arbitrary SQL into a box which you would then execute...

Answer 2

One approach which covers most scenarios is to execute the SQL with SET FMTONLY ON

e.g.

SET FMTONLY ON;
SELECT SomeField FROM ExampleQuery

From BOL, SET FMTONLY :

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.query.

That will error if the query is invalid. You can also check the result to determine what the schema of the resultset that is returned would be (i.e. no schema = not a SELECT statement).

Update: In general terms when dealing with SQL that you want to protect against SQL injection there are other things you should be thinking about:

1. Avoid dynamic sql (concatenating user-entered values into an SQL string to be executed). Use parameterised SQL instead.

2. Encapsulate the query as a nested query. e.g.

   SELECT * FROM (SELECT Something FROM ADynamicQueryThatsBeenGenerated) x

So if the query contains multiple commands, this would result in an error. i.e. this would result in an invalid query when encapsulated as a nested query:

SELECT SomethingFrom FROM MyTable;TRUNCATE TABLE MyTable