0

Suppose the folowing SQL command:

update [TABLE] set [value] = 'UserName' where key1 = :param1 and key2 = :param2

I would like to extract :param1 and :param2 from the SQL. So, I´m using the following regex to match the SQL String:

:([\w.$]+|"[^"]+"|'[^']+')

This is working fine, except when the SQL String contains a colon (:) between quotes (") or single quotes (').

Eg, I would like that the regex matcher returns me only :param1 and :param2 to the following queries as well:

 update [TABLE] set [value] = ':UserName' where key1 = :param1 and key2 = :param2
 update [TABLE] set [value] = 'User=:UserName' where key1 = :param1 and key2 = :param2
 update [TABLE] set [value] = '2015-04-26 21:59:24' where key1 = :param1 and key2 = :param2

because the values :UserName, User=:UserName and 2015-04-26 21:59:24 are between Single Quotes...

I tried to modify the regex expression, but nothing seems to work. What should I do?

regisxp
  • 956
  • 2
  • 10
  • 31
  • See http://stackoverflow.com/questions/249791/regex-for-quoted-string-with-escaping-quotes for examples of how to use a regex to match (and therefore also to exclude from matching) quoted text with escape sequences. – BadZen Apr 29 '15 at 02:36
  • @BadZen, I tried some aproaches with my expression, but none of them worked... I´m not proficient on regex so, I know that I´m making some mistake, but I don´t know where... Thanks. – regisxp Apr 29 '15 at 02:45

1 Answers1

2
/'.*?'|(:[^\b]+?\b)/g

Your results will be in group #1 (the paren match).

Demo of this regex.

EDIT: As per weirdness discussed below: /'.*?'|(:\B+?\b)/g

BadZen
  • 4,083
  • 2
  • 25
  • 48
  • That said, why are you parsing raw SQL yourself? Normally that's a "code smell" and there's a better way to go... – BadZen Apr 29 '15 at 02:57
  • Hello @BadZen, thanks for your comment. I tried to use your expression on Java (using Java Pattern.compile) but it´s trowing as exception: java.util.regex.PatternSyntaxException: Illegal/unsupported escape sequence near index 11 '.*?'|(:[^\b]+?\b). Is there another approach that I could use? You also asked me why Am I parsing the query by my own. It´s due to some restrictions on the Android platform that doesn´t allows me to use "named parameters" and another issues. So, the only viable solution that fits our project e to create a "layer" that will handle named parameters just as we need. – regisxp Apr 29 '15 at 19:10
  • That's gonna be tough specially with the example of `'User=:UserName' ` in the second query. I'll suggest using a differnt syntax for named parameter that is less common than a semi-colon. let's say something like `[:namedparameter:]` for example. – Garis M Suero Apr 29 '15 at 20:07
  • I guess I can't convince you to use indexed parameters then huh? =) This stuff is def. gonna break at some point. Meh. – BadZen Apr 29 '15 at 20:46
  • \b *is* supported in Java. Are you sure you're escaping the string correctly? Should look like `String REGEX_STR = "/'.*?'|(:[^\\b]+?\\b)/g";` – BadZen Apr 29 '15 at 20:47
  • Otherwise you'll have literal \b in your regex. No bueno. – BadZen Apr 29 '15 at 20:48
  • @GarisMSuero - Named parameter syntax is not something OP invented, it's standard JDBC. He can't "change it". And look at my demo link, the regex I gave handles the situation. – BadZen Apr 29 '15 at 20:49
  • WEIRD. Java is actually picky about \b in character classes. I think it's a bug. Use \B instead of [^\b]. Edited above. weird,weird. – BadZen Apr 29 '15 at 20:59
  • 1
    Yes, It´s really weird! By the way, using `"/'.*?'|(:[^\B]+?\b)/g` (The escaped java string is `"'.*?'|(:[^\\B]+?\b)"` worked as a charm!! – regisxp Apr 29 '15 at 21:10
  • did i misread then? I thought he said he is writing a layer in order to support named parameters. @BadZen – Garis M Suero Apr 29 '15 at 21:48
  • JDBC has them, but various JDBC drivers say "nope". It's a sad state of affairs. Esp. since some JPA drivers say "nope" to /positional parameters/ and want you to use named. Headaches ensue. – BadZen Apr 29 '15 at 21:51