0

How can I validate SELECT statements, without executing using .Net and C#?

If the sql is not valid or if the sql is other operation than SELECT (example: alter, insert, delete, ...) I want to return error rows.

This question is very much like: Code to validate SQL Scripts. But I'm not want to accept any sql script. I want to accept only SELECT statements.

Community
  • 1
  • 1
Jonny Piazzi
  • 3,684
  • 4
  • 34
  • 81

2 Answers2

2

Begin the statement with SET NOEXEC ON

http://msdn.microsoft.com/en-us/library/ms188394.aspx

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Thanks for help but this solve the SQL script validation. How to solve my question about SELECT? I want to validate ONLY select statements. – Jonny Piazzi Aug 06 '14 at 12:24
  • You could check it as per SverreN's answer, but that could be pretty unsafe. Better would be to check it as per this answer, and only ever run it under a very limited SQL user account which only had SELECT permissions. – chrisb Aug 06 '14 at 12:41
  • Thinking outside the box, you could ensure that the user is a read only user. Then they can never update data. Don't rely in manual parsing to exclude data changes. They could run a stored procedure that performs updates/inserts and you wouldn't know. – Nick.Mc Aug 06 '14 at 22:55
0

Use a Regular Expression to check it:
regex = "SELECT [*] FROM [*]"

SverreN
  • 177
  • 1
  • 9
  • Good idea, but would have to be really careful that the regex used didn't allow injection vectors like comments and so on. – chrisb Aug 06 '14 at 12:38