Trying again: Need help with RegEx for SQL

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...

re: Trying again: Need help with RegEx for SQL

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).Va lue = "Eric's sample"
cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1


--
Eric Newton
C#/ASP Application Developer
http://ensoft-software.com/
eric@cc.ensoft-software.com [remove the first "CC."]

"Chuck Haeberle" <chaeberl@contractor.nocienaspam.com> wrote in message
news:%23Eh98ursDHA.2520@TK2MSFTNGP10.phx.gbl...[color=blue]
> 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[/color]
',[color=blue]
> and I can't even fathom how to handle the potential for multiple[/color]
matches...[color=blue]
>
> 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...
>
>[/color]

re: Trying again: Need help with RegEx for SQL

"Eric Newton" <eric@cc.ensoft-software.com> wrote in message
news:%23EkEu0rsDHA.2408@tk2msftngp13.phx.gbl...[color=blue]
> 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[/color]
figure[color=blue]
> out whats going on.[/color]

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.
[color=blue]
> if you REALLY want to parse after the fact, I dont believe regex's will[/color]
work[color=blue]
> well, you'll have to actually parse the text but if you're inconsistent[/color]
then[color=blue]
> good luck trying to get that to work either...[/color]

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...
[color=blue]
> 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).Va lue = "Eric's sample"
> cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1[/color]

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. :)

re: Trying again: Need help with RegEx for SQL

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.

"Chuck Haeberle" <chaeberl@contractor.nocienaspam.com> wrote in message
news:e77834rsDHA.2304@tk2msftngp13.phx.gbl...[color=blue]
> "Eric Newton" <eric@cc.ensoft-software.com> wrote in message
> news:%23EkEu0rsDHA.2408@tk2msftngp13.phx.gbl...[color=green]
> > 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[/color][/color]
single[color=blue][color=green]
> > quotes with DOUBLED single quotes, dont wait til afterwards to try to[/color]
> figure[color=green]
> > out whats going on.[/color]
>
> 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.
>[color=green]
> > if you REALLY want to parse after the fact, I dont believe regex's will[/color]
> work[color=green]
> > well, you'll have to actually parse the text but if you're inconsistent[/color]
> then[color=green]
> > good luck trying to get that to work either...[/color]
>
> Thats my fear - especially considering how many variations there are to[/color]
the[color=blue]
> rules when you consider the syntax of insert clauses vs. the syntax of[/color]
where[color=blue]
> clauses...
>[color=green]
> > The key is to fix it at the start, before it even has a chance to become[/color][/color]
a[color=blue][color=green]
> > problem, OR use named parameters:
> > cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE[/color][/color]
FieldID=@FieldID"[color=blue][color=green]
> > cmd.Parameters.Add("@Field1",DbType.VarChar,50).Va lue = "Eric's sample"
> > cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1[/color]
>
> I forgot to mention the back end is a MySQL database, didn't I? I wonder[/color]
if[color=blue]
> named parms will work in that case. :)
>
> Anyway if it's really not feasible I'll make do - but I'm hoping SOMEONE[/color]
out[color=blue]
> there is really gifted with these cryptic (to me) regexs and is intrigued[/color]
by[color=blue]
> the challenge. :)
>
>[/color]

re: Trying again: Need help with RegEx for SQL

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

--
Eric Newton
C#/ASP Application Developer
http://ensoft-software.com/
eric@cc.ensoft-software.com [remove the first "CC."]

"Chuck Haeberle" <chaeberl@contractor.nocienaspam.com> wrote in message
news:%23MQV6UssDHA.1884@TK2MSFTNGP10.phx.gbl...[color=blue]
> 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[/color]
write[color=blue]
> their queries that way. :)
>
> Thanks for the nudge Eric - I'd never have tested them if you hadn't[/color]
brought[color=blue]
> it up.
>
> "Chuck Haeberle" <chaeberl@contractor.nocienaspam.com> wrote in message
> news:e77834rsDHA.2304@tk2msftngp13.phx.gbl...[color=green]
> > "Eric Newton" <eric@cc.ensoft-software.com> wrote in message
> > news:%23EkEu0rsDHA.2408@tk2msftngp13.phx.gbl...[color=darkred]
> > > 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[/color][/color]
> single[color=green][color=darkred]
> > > quotes with DOUBLED single quotes, dont wait til afterwards to try to[/color]
> > figure[color=darkred]
> > > out whats going on.[/color]
> >
> > Generally I agree - but the dev's on the project I'm doing this for has[/color][/color]
a[color=blue][color=green]
> > history of failing to do just that and my thinking is, if we could[/color][/color]
remove[color=blue][color=green]
> > the need at a lower level then when someone who's less dilligent than[/color][/color]
they[color=blue][color=green]
> > should be writes sql which will blow up on the nested ', we can nip it[/color][/color]
in[color=blue][color=green]
> > the bud.
> >[color=darkred]
> > > if you REALLY want to parse after the fact, I dont believe regex's[/color][/color][/color]
will[color=blue][color=green]
> > work[color=darkred]
> > > well, you'll have to actually parse the text but if you're[/color][/color][/color]
inconsistent[color=blue][color=green]
> > then[color=darkred]
> > > good luck trying to get that to work either...[/color]
> >
> > Thats my fear - especially considering how many variations there are to[/color]
> the[color=green]
> > rules when you consider the syntax of insert clauses vs. the syntax of[/color]
> where[color=green]
> > clauses...
> >[color=darkred]
> > > The key is to fix it at the start, before it even has a chance to[/color][/color][/color]
become[color=blue]
> a[color=green][color=darkred]
> > > problem, OR use named parameters:
> > > cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE[/color][/color]
> FieldID=@FieldID"[color=green][color=darkred]
> > > cmd.Parameters.Add("@Field1",DbType.VarChar,50).Va lue = "Eric's[/color][/color][/color]
sample"[color=blue][color=green][color=darkred]
> > > cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1[/color]
> >
> > I forgot to mention the back end is a MySQL database, didn't I? I[/color][/color]
wonder[color=blue]
> if[color=green]
> > named parms will work in that case. :)
> >
> > Anyway if it's really not feasible I'll make do - but I'm hoping SOMEONE[/color]
> out[color=green]
> > there is really gifted with these cryptic (to me) regexs and is[/color][/color]
intrigued[color=blue]
> by[color=green]
> > the challenge. :)
> >
> >[/color]
>
>[/color]