Using regex to find sql parameters in a string

Question

I am using the following regex expression to find SQL parameters, but it is not working under some circumstances.

\@([^=<>\s]+)((?=\s)|$)

If I use this SQL query,

select count(1) from (select * from tblEmailList109 where firstname='@FirstName') t

the value returned is:

@Firstname')

How can I modify the regex to stop stop but do not include a single quote, a space, or the end of the string?

My intention is the replace the parameter before passing it to the SQL server. I know having the single quotes in a normal stored procedure with parameters is not required, but in my case, when I do the replacement, the quotes are needed for string literals when sending to the SQL server.

Thanks

Answer 1

Based heavily on research by Hunter McMillen, the following looks to fulfil all criteria:

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

Working example

Your regex was capturing the trailing ') because both of those characters are included in your character class [^=<>\s]

I'd also like to point out that the second half of your regex does nothing at all

(|(?=\s)|$)
^^

You have 3 alternatives (in the format (a|b|c)) to match here:

The middle one (?=\s) matches where the next character is whitespace, $ matches end of input, but the first alternative (marked with ^^) says match on nothing. So the alternation will always match because nothing will match between every character.

Answer 2

I would use the regex

@(?:[\w#_$]{1,128}|(?:(\[)|").{1,128}?(?(1)]|"))

It will find an @ followed by either of these:

• Up to 128 #, _, $, and alphanumeric characters.
• Up to 128 characters contained within the delimiters [] or "".

You can find an full explanation and demonstration here: http://regex101.com/r/nY1pR0