9

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

Improve this question
3
  • so add those inside yours regex \@([^=<>')(\s]+)(|(?=\s)|$) Commented Jan 22, 2014 at 15:14
  • what do you expect (|(?=\s)|$) to do? or more simply, what would you expect (|$) to do? Commented Jan 22, 2014 at 15:15
  • Silimar to this question: stackoverflow.com/questions/18000874/… Commented Jan 22, 2014 at 15:20

4 Answers 4

Reset to default
6

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.

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

2 Comments

you are right, the (|(?=\s)|$) was an unfortunate typo on my part...in trying different variations I left the bar | in which made it invalid, sorry for the confusion.
Ah fair enough, it's not the first time I've seen it posted in a regex on SO before so I assumed the worst ;)
4

If you add an escaped apostrophe to the list of ignores, it works for your test case. 

\@([^=<>\s\']+)
Improve this answer

3 Comments

That worked! But just curious, on the look behind, it seemed like a matter or precedence. If I used \@([^=<>\s]+)((?=\')|(?=\s)|$) it would favor the "space" as the ending delimiter instead of the single quote. Is there a way to set precedence to that if it finds the single quote first it will use that as the delimiter?
The second half of this regex does literally nothing, as I was trying to imply in my comment above
He has a point. I revised the regexp in my post to remove the redundancy, but there definitely are more elegant solutions on this page.
2

Not sure what SQL you are using, but from the MySQL documentation:

User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @my-var).

So there two possible things to check for:

  1. Only alphanumeric, underscore, period and dollar sign: \@[\w\.\$]+
  2. Anything that is quoted: \@["'`].*["'`]

The second check will return some false positives since it will match strings that don't start and end in the same quote-type, but it is simpler and your SQL editor will complain about mismatching quote-types anyway.

Improve this answer

2 Comments

Sorry, I should have said I am replacing the parameters in the SQL before I send it to the SQL Server engine.
@Chris, it seems to be the case that you don't care about quoted parameters so you can literally use Hunter's \@[\w\.\$]+ from point 1
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.