SPUG: SQL syntax Regular Expression

[Parr, Ryan]

If you're using the DBI package (god I hope you are) then you can do:

my $sql = "SELECT * FROM tbl_1 WHERE field_1 = " . $dbh->quote($bobs_value);

But the better way to do it ($dbh->quote will also quote numbers) is to use
bind variables.

my $sth = $dbh->prepare('SELECT * FROM tbl_1 WHERE field_1 = ?');
$sth->execute($bobs_value);

This will aid you in many ways, least of which is that you no longer need to
waste time with thinking about quoting. It also gives you a performance
increase with a single prepared statement handle to use in loops:

my $sth = $dbh->prepare('INSERT INTO tbl_1 VALUES (NULL,?)');
foreach my $val (@vals) {
	$sth->execute($val);
}

Also, some databases will actually cache the prepared query for use down the
line. I don't know the technical specifics, I just know that this is a very
good thing for your efficiency.

-- Ryan Parr

Common sense is the collection of prejudices acquired by age eighteen.
		-- Albert Einstein


-----Original Message-----
From: Josh Lanza [mailto:joshlanza at hotmail.com] 
Sent: Tuesday, June 18, 2002 7:28 PM
To: spug-list at pm.org
Subject: SPUG: SQL syntax Regular Expression


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

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