If I have a query such as SELECT * from authors where name = @name_param, is there a regex to parse out the parameter names (specifically the "name_param")?
Thanks
Asked
Active
Viewed 6,488 times
7
bluish
- 26,356
- 27
- 122
- 180
-
Not sure exactly what you're asking. Can you clarify, or give an example? Do you want to pass in a list of names and have it convert the WHERE to name in (name_a, name_b, ...) ? and you need to specify the RDBMS, Regex support is different between them. – Nov 21 '08 at 05:17
2 Answers
9
This is tricky because params can also occur inside quoted strings.
SELECT * FROM authors WHERE name = @name_param
AND string = 'don\'t use @name_param';
How would the regular expression know to use the first @name_param but not the second?
It's a problem that can be solved, but it's not practical to do it in a single regular expression. I had to handle this in Zend_Db, and what I did was first strip out all quoted strings and delimited identifiers, and then you can use regular expressions on the remainder.
You can see the code, because it's open-source.
See functions _stripQuoted() and _parseParameters().
https://github.com/zendframework/zf1/blob/136735e776f520b081cd374012852cb88cef9a88/library/Zend/Db/Statement.php#L200 https://github.com/zendframework/zf1/blob/136735e776f520b081cd374012852cb88cef9a88/library/Zend/Db/Statement.php#L140
Bill Karwin
- 538,548
- 86
- 673
- 828
-
3Not to mention parameter names appearing in SQL comments. Regexes are great, but not necessarily great for parsing jobs. – Jonathan Leffler Nov 21 '08 at 07:26
-
Too bad link wasn't working. I was also looking for a way to exclude quoted string and comments. – Shinjo Oct 03 '19 at 03:06
-
1@Shinjo I have updated the link to the current location of the code I described. – Bill Karwin Oct 03 '19 at 04:22
4
Given you have no quoted strings or comments with parameters in them, the required regex would be quite trivial:
@([_a-zA-Z]+) /* match group 1 contains the name only */
I go with Bill Karwin's recommendation to be cautious, knowing that the naïve approach has its pitfalls. But if you kow the data you deal with, this regex would be all you need.
-
Small remark : numbers are also valid in parameter names so the regex should be @([_a-zA-Z0-9]+) – Samuel Sep 21 '11 at 07:33
-
3@gissolved it's true, but I think parameter can't start with digits, and maybe not even with an underscore. So I'd use `@([a-zA-Z][a-zA-Z0-9_]*)`. – bluish Nov 11 '11 at 13:46