Regex for parsing SQL parameters

Question

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

Answer 1

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 here: http://framework.zend.com/code/browse/~raw,r=8064/Zend_Framework/trunk/library/Zend/Db/Statement.php

See functions _stripQuoted() and _parseParameters().

Answer 2

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 it's pitfalls. But if you kow the data you deal with, this regex would be all you need.