SPUG: SQL syntax Regular Expression
Dan Sanderson
lists at dansanderson.com
Wed Jun 19 19:21:17 CDT 2002
This may or may not be worth mentioning in this particular case, but since
I learned this lesson the hard way, I feel like mentioning it every time I
see $` and $' (and $&) mentioned. From camel, 24.2.1:
Avoid $& and its two buddies, $` and $'. Any occurrence in your program
causes all matches to save the searched string for possible future
reference. (However, once you've blown it, it doesn't hurt to have more
of them.)
Tools are there to be used, but knowing what they do is important,
especially in non-intuitive cases. I once used $` and $' while parsing
through a stream of data, and didn't notice their drawbacks on small sets
of data. When larger sets of real world data came in, the script got
jaw-droppingly slow. I don't think I could ever use them again, even when
appropriate.
-- Dan
On Wed, 19 Jun 2002, Meryll Larkin wrote:
> 6/19/02
>
> Hi Josh,
>
> Here's some Regex that might be helpful:
> $` (dollarsign backtick) captures the string that matches before the
> Regex
> $' captures after the Regex
> \Q escapes special characters and makes the Regex treat them literally
> until it reaches a \E
>
> print "testing comments\n";
>
> my $string = qq|
> <html>
> ...
> <!-- <a href="voo">voo</a> -->
> Some stuff here
> <!-- <a href="roo">roo</a> -->
> Other stuff here
> <!-- <a href="she">she</a> -->
> <a href="and_a_link_here">Link</a>
> <!-- <a href="moo">moo</a> -->
>
> ...
> </html>
>
> |;
>
> my $start= "<!--";
> my $end = "-->";
>
> if ( $string =~ /$start/ ) {
>
> print "string $string contains $start\n";
> $string =~ s/\Q$start\E.*?\Q$end\E//g;
> print "new string is $string\n";
>
> } else {
>
> print "$start not found\n";
>
> }
>
> I hope this puts you on the right track.
>
> Meryll Larkin
>
>
>
>
>
> --- Josh Lanza <joshlanza at hotmail.com> wrote:
> > It's actually the sql WHERE clause that comes in to this process and
> > I need
> > to do some validation on that string. So the string would come in
> > like this:
> >
> > $sql = "title = 'fred's title' ";
> >
> > I need handle that string, and escape the tick in "fred's".
> >
> > What I want to get at here is how to handle delimiters with regular
> > expressions. The same thing applies with markup parsing - I know
> > there are
> > modules written to do this kind of work, but I'd like to know how,
> > for
> > instance, to parse an HTML document and determine if a block of HTML
> > is
> > commented out. I can see how this is done with byte by byte parsing,
> > but how
> > is it done with regular expressions?
> >
> > Example html doc:
> >
> > <html>
> > ...
> >
> > <!-- <a href="foo">foo</a> -->
> >
> > ...
> > </html>
> >
> >
> > When parsing this document, I could find the "a" tag with something
> > like
> > (but more sophisticated than)
> >
> > /<a href=[^>]+>/ig
> >
> > However, I wouldn't know if the link were commented out. Is there a
> > way to
> > do this with regular expressions?
> >
> >
> >
> > >From: Creede Lambard <creede at penguinsinthenight.com>
> > >Reply-To: creede at penguinsinthenight.com
> > >To: Josh Lanza <joshlanza at hotmail.com>
> > >CC: spug-list at pm.org
> > >Subject: Re: SPUG: SQL syntax Regular Expression
> > >Date: Tue, 18 Jun 2002 21:35:31 -0700 (PDT)
> > >
> > >You might be able to get around this by using DBI:
> > >
> > > use DBI;
> > > my $dbh = DBI->connect('my_db', $username, $auth);
> > > my $sth = $dbh->prepare("select * from tbl_1 where field_1 = ?");
> > > $sth->execute("bob's value");
> > >
> > >and so on.
> > >
> > >If you're in a position where you CAN'T use DBI you might be able to
> > do
> > >something
> > >like
> > >
> > > my $name = "bob's value";
> > > $name = normalize($name);
> > > my $sql = "select * from tbl_1 where field_1 = '$name'";
> > >
> > > sub normalize { my $word = shift; $word =~ s/'/''/g; return
> > $word; }
> > >
> > >You want the 'g' in there just in case your parameter has more than
> > one
> > >apostrophe ("O'Brien's Deli"). Others more wise in the way of SQL
> > might be
> > >able to say whether there are other ramifications you need to be
> > aware of.
> > >
> > >On Wed, 19 Jun 2002, Josh Lanza wrote:
> > >
> > > > Hi,
> > > >
> > > > I need to create a regexp to validate and correct apostrophe
> > characters
> > > > in sql statements, and I left my Friedl book at home :(
> > > >
> > > > Example:
> > > >
> > > > $sql = "select * from tbl_1 where field_1 = 'bob's value'";
> > > >
> > > > There is a tick (bob's) that is not escaped (should be bob''s).
> > > >
> > > > Is this something I can handle with regular expressions? The idea
> > of
> > > > crawling this string byte by byte seems a little distasteful ...
> > > >
> > > > TIA, Josh
> > > >
> > > > _________________________________________________________________
> > > > Join the world’s largest e-mail service with MSN Hotmail.
> > > > http://www.hotmail.com
> > > >
> > > >
> > > > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> > - - - -
> > >-
> > > > POST TO: spug-list at pm.org PROBLEMS:
> > owner-spug-list at pm.org
> > > > Subscriptions; Email to majordomo at pm.org: ACTION LIST
> > EMAIL
> > > > Replace ACTION by subscribe or unsubscribe, EMAIL by your
> > >Email-address
> > > > For daily traffic, use spug-list for LIST ; for weekly,
> > >spug-list-digest
> > > > Seattle Perl Users Group (SPUG) Home Page:
> > http://seattleperl.org
> > > >
> > > >
> > >
> > >--
> > > * .~. `(
> > ---------------------------------------------------------
> > >` / V \ . Creede Lambard : Never rush a miracle man.
> > > /( )\ creede at penguinsinthenight.com : You get rotten
> > miracles.
> > > ^^-^^
> > ---------------------------------------------------------
> > >
> > >Perl Programmer and Linux Sysadmin, reasonable rates. Inquire
> > within.
> > >
> >
> >
> >
> >
> > _________________________________________________________________
> > Send and receive Hotmail on your mobile device: http://mobile.msn.com
> >
> >
> > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> > - - -
> > POST TO: spug-list at pm.org PROBLEMS: owner-spug-list at pm.org
> > Subscriptions; Email to majordomo at pm.org: ACTION LIST EMAIL
> > Replace ACTION by subscribe or unsubscribe, EMAIL by your
> > Email-address
> > For daily traffic, use spug-list for LIST ; for weekly,
> > spug-list-digest
> > Seattle Perl Users Group (SPUG) Home Page:
> > http://seattleperl.org
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> POST TO: spug-list at pm.org PROBLEMS: owner-spug-list at pm.org
> Subscriptions; Email to majordomo at pm.org: ACTION LIST EMAIL
> Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
> For daily traffic, use spug-list for LIST ; for weekly, spug-list-digest
> Seattle Perl Users Group (SPUG) Home Page: http://seattleperl.org
>
>
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
POST TO: spug-list at pm.org PROBLEMS: owner-spug-list at pm.org
Subscriptions; Email to majordomo at pm.org: ACTION LIST EMAIL
Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
For daily traffic, use spug-list for LIST ; for weekly, spug-list-digest
Seattle Perl Users Group (SPUG) Home Page: http://seattleperl.org
More information about the spug-list
mailing list