SPUG: SQL syntax Regular Expression

Wed Jun 19 13:59:52 CDT 2002

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