Trying again: Need help with RegEx for SQL

  • Thread starter Thread starter Chuck Haeberle
  • Start date Start date
C

Chuck Haeberle

I have an interesting regular expression challenge for someone more
experienced with them than I for a data layer class...

I need an expression to search a SQL statement (any type, SELECT INSERT
UPDATE OR DELETE) and find all single apostrophes which should be replaced
with a double apostrophe without affecting the apostrophes used to delimit
the string values:

Example:
SELECT field FROM table WHERE stringfield = 'This is one of Jerry's worst
examples. Who's going to fix it?' or stringfield2='Where's Wendy's Beef
Hamburger'
or
INSERT INTO table (field1, field2) VALUES ('Wings'n'things', 'Who's Fancy
Restaurant')

I can get a pattern which matches a single ' within two other ', but get
hung up when I start trying to cope with multiple ' within the enclosing ',
and I can't even fathom how to handle the potential for multiple matches...

Anyone feel like giving it a shot? I really would not want to have to
figure this out manually nor do I want to force the users of my data layer
to pre process their strings manually...

This regex stuff makes my head hurt...
 
If you start out right then you'll save yourself a lot of headache.

when you're building up the sql, fix it right then by escaping the single
quotes with DOUBLED single quotes, dont wait til afterwards to try to figure
out whats going on.

if you REALLY want to parse after the fact, I dont believe regex's will work
well, you'll have to actually parse the text but if you're inconsistent then
good luck trying to get that to work either...

The key is to fix it at the start, before it even has a chance to become a
problem, OR use named parameters:
cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE FieldID=@FieldID"
cmd.Parameters.Add("@Field1",DbType.VarChar,50).Value = "Eric's sample"
cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1
 
Eric Newton said:
If you start out right then you'll save yourself a lot of headache.

when you're building up the sql, fix it right then by escaping the single
quotes with DOUBLED single quotes, dont wait til afterwards to try to figure
out whats going on.
Click to expand...

Generally I agree - but the dev's on the project I'm doing this for has a
history of failing to do just that and my thinking is, if we could remove
the need at a lower level then when someone who's less dilligent than they
should be writes sql which will blow up on the nested ', we can nip it in
the bud.
if you REALLY want to parse after the fact, I dont believe regex's will work
well, you'll have to actually parse the text but if you're inconsistent then
good luck trying to get that to work either...
Click to expand...

Thats my fear - especially considering how many variations there are to the
rules when you consider the syntax of insert clauses vs. the syntax of where
clauses...
The key is to fix it at the start, before it even has a chance to become a
problem, OR use named parameters:
cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE FieldID=@FieldID"
cmd.Parameters.Add("@Field1",DbType.VarChar,50).Value = "Eric's sample"
cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1
Click to expand...

I forgot to mention the back end is a MySQL database, didn't I? I wonder if
named parms will work in that case. :)

Anyway if it's really not feasible I'll make do - but I'm hoping SOMEONE out
there is really gifted with these cryptic (to me) regexs and is intrigued by
the challenge. :)
 
Well it turns out that the parameterized query does work with MySQL,
shockingly enough.

I suppose either the CLR or the ODBC provider is doing the
translation...cause I don't think MySQL natively supports it but in any
event, unless someone has a magic RegEx to meet the need, I'm going to
change my DAL to support parameterized sql and tell the other devs to write
their queries that way. :)

Thanks for the nudge Eric - I'd never have tested them if you hadn't brought
it up.
 
You're welcome!

Personally I prefer the named parameters because in SQL the SqlCommand
provider encourages the use of sp_executesql, which affords using a cached
execution plan, basically almost as good as writing a stored procedure!

Glad to be of help
 
Back
Top