SPUG: SQL syntax Regular Expression

[Creede Lambard]

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.


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