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... 4 1720
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/ er**@cc.ensoft-software.com [remove the first "CC."]
"Chuck Haeberle" <ch******@contractor.nocienaspam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... 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...
"Eric Newton" <er**@cc.ensoft-software.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl... 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.
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...
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).Va lue = "Eric's sample" cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1
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.
"Chuck Haeberle" <ch******@contractor.nocienaspam.com> wrote in message
news:e7**************@tk2msftngp13.phx.gbl... "Eric Newton" <er**@cc.ensoft-software.com> wrote in message news:%2****************@tk2msftngp13.phx.gbl... 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.
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...
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).Va lue = "Eric's sample" cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1 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. :)
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/ er**@cc.ensoft-software.com [remove the first "CC."]
"Chuck Haeberle" <ch******@contractor.nocienaspam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... 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" <ch******@contractor.nocienaspam.com> wrote in message news:e7**************@tk2msftngp13.phx.gbl... "Eric Newton" <er**@cc.ensoft-software.com> wrote in message news:%2****************@tk2msftngp13.phx.gbl... 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.
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...
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).Va lue = "Eric's
sample" cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1
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. :)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Justin Koivisto |
last post by:
OK, I found a thread that help out from a while back (Oct 9, 2002) to
give me this pattern:
`(((f|ht)tp://)((+)(+)?@)?()+(:\d+)?(\/+)?)`i
OK, all is well and good with this until the URL is...
|
by: Erik Blas |
last post by:
Hello everyone,
Basically what I'm trying to do is write an app that will read the
lines from a text file, parse through each lean with a regex (if there
is a better way please point me) for a...
|
by: Andrew Baker |
last post by:
I am trying to write a function which provides my users with a file
filter. The filter used to work just using the VB "Like" comparision,
but I can't find the equivilant in C#. I looked at...
|
by: Joe |
last post by:
Hi,
I have been using a regular expression that I don’t uite understand to
filter the valid email address. My regular expression is as follows:
<asp:RegularExpressionValidator...
|
by: Michael R. Pierotti |
last post by:
Dim reg As New Regex("^\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}$")
Dim m As Match = reg.Match(txtIPAddress.Text)
If m.Success Then
'No need to do anything here
Else
MessageBox.Show("You need to enter a...
|
by: erikcw |
last post by:
Hi all,
I'm trying to write a regex pattern to use in preg_replace. Basically
I want to put around every line (\n) in this variable.
However, I need to exclude lines that already have brackets...
|
by: konrad Krupa |
last post by:
This message is a continuation of my previous post "Pattern Match"
Doug - Thank you for your help.
Doug Semler was able to solve my problem to some point but I still need some
help.
Doug's...
|
by: XtinaS |
last post by:
I'm trying to write a script for Greasemonkey that will, in LiveJournal, replace a placeholdered embedded YouTube thing with a link to the video. In LiveJournal, you can set an option to have a...
|
by: Danny Ni |
last post by:
Hi,
The following code snippet is causing CPU to max out on my local machine and
production servers. It looks fine on Expresso though.
Regex rgxVideo = new...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |