Detecting SQL strings

Question

I have been trying to figure out a regex for matching complete strings in SQL, from a .sql file (including escaped single quotes), and the ones I've come up with either don't capture enough of the string or result in a StackOverflow exception (I suspect from the possible combinations of ''|[^'].

The regex I was trying to build was something along the lines of

(?<!')'(''|[^'])*'(?!')

I want there to be a ' starting and ending the string, with any number of pairs of ' and non single '`' characters between.

One solution I considered is to first replace pairs of ' with a different character and then simply look for anything between single ', but is there way to configure my regex to do this (and actually run).

I just tried your regex on -'This is a string. It''s single quoted!!!'''-, and it returned the correct substring nigh-instantly. — Brilliand
– Brilliand, Commented Jul 31, 2013 at 19:58
Note however that your lookahead/lookbehind have no practical effect, since the search starts at the beginning of the string, and the middle part is greedy. — Brilliand
– Brilliand, Commented Jul 31, 2013 at 20:00
Yes, it works for single instances and test cases, but I'm working with tens/hundreds of thousands of lines of SQL code, some which are huge chunks of dynamic SQL. Using this regex results a StackOverflow error. I wanted to know if there was an alternative that didn't utilize ''|[^'], as that is what I'm suspecting is causing it. — Song Gao
– Song Gao, Commented Jul 31, 2013 at 20:02
That section of the regex is pretty safe - it will never cause any more than a linear amount of backtracking, since there's no character that is valid on both sides. Also, the stack usage won't persist over many records. I would look for some other part of your code to accuse of causing this. — Brilliand
– Brilliand, Commented Jul 31, 2013 at 20:06

Song Gao · Accepted Answer · 2013-08-06 15:15:59Z

1

I figured it out: I used a possessive quantifier. This regex will detect a SQL string (with possible escaped quotes):

'([']{2}|[^'])*+'(?!')

answered Aug 6, 2013 at 15:15

Song Gao

6664 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Detecting SQL strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related