SPUG: SQL syntax Regular Expression

Wed Jun 19 18:58:31 CDT 2002

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