-3

I'm looking for a C# regex to find all SQL Update and Delete statements without Where clause Let's say I have this SQL code

UPDATE tblTest
SET vchTest = 'a'
 select * from tblTest  where ID=1
UPDATE tblTest
SET vchTest = 'b'
where ID=1
select * from tblTest  where ID=1
UPDATE tblTest
SET vchTest = 'c'
Delete from tblTest
where ID=1
Delete from tblTest
select * from tblTest  where ID=1

How do I find all the Update and Delete statements without Where clause?

rene
  • 39,748
  • 78
  • 111
  • 142
comXyz
  • 3
  • 2

1 Answers1

2

Lacking any delimiter between the statements I think I would just split the string on UPDATE or DELETE and just ask for those lacking a WHERE

str.ToLower().Split(new[]{"update","delete","select"}).Where(s => !s.Contains("where"));

and then fine tune for edge cases from there. Presumably you're using in this to detect problematic statements that affect an entire table so it doesn't matter that we have lowercased the whole thing, but Regex.Split will offer you an ignore case option.

Regex could also give you the entire statement with something like

(?<sql>(select|update|delete).*?)(?=(select|update|delete|$))

(and IgnoreCase|SingleLine|ExplicitCapture flags, then inspect all the captured matches in the MatchCollection for those containing a "where") - let me know if you need this expanding on.

Note that these cover the cases presented but if you have other things like "delete" being present inside a string, you're probably heading well into parsing territory and you might be better off looking for something that can properly parse the SQL and rummage through the result

Caius Jard
  • 69,583
  • 5
  • 45
  • 72
  • it's kind of brittle, but works for the sample and is a good approach to start. Though, OP is only looking to determine on UPDATE and DELETE that don't have where clause. Having said that, edge cases will be sub queries. so probably have to handle parenthesis. – Brett Caswell Jun 04 '22 at 09:31
  • The select is in there because splitting in update/delete alone would mean that `Delete from tblTest select * from tblTest where ID=1` would be detected as having a where clause. If OP wants to cover more edge cases they need to state what they are; pre-empting every unstated requirement leads to enterprise fizzbuzz. Note that even slightly more complex (like you hinted) or perhaps simply "where 1=1" is, for me, stepping outside of "can do this with a quick string contains" and into "needs parsing properly by an sql interpreter" - it's "parse html with regex" all over again! :) – Caius Jard Jun 04 '22 at 15:29