[rt-devel] let DBI handle it

[Dylan Vanderhoof]

I had completely forgotten about quote and bind_param... This has not been a
good week for brainpower.  
The Schema thing was my biggest concern however.  I'm not sure of a good way
to address that one.
The only thing I can think of offhand is to address the MySQL calls with the
schema 'mysql.' prepended, which would make it ugly, but could work.

Any suggestions out there for a way to approach that?

Dylan C. Vanderhoof
Internal Software Developer
Semaphore Corporation


-----Original Message-----
From: Aryeh "Cody" Sherr [mailto:asherr at cs.unm.edu]
Sent: Wednesday, April 12, 2000 5:02 PM
To: Dylan Vanderhoof
Cc: 'rt-devel at lists.fsck.com'
Subject: Re: [rt-devel] let DBI handle it



DBI has something that directly addresses this quoting problem:

$dbh->quote($string) and
$dbh->quote($string, $data_type)

These will use the DBD to quote the data as appropriate for the database
and the data type. This works for Oracle. It's almost a must for quoting
between different databases: some need ' and \ quoted, %, etc.

Another really great solution that also markably improves efficiency is to
use bind parameters. You don't need to quote things at all:

my $sql = "SELECT foo FROM table WHERE col=?"; #? is a placeholder
my $sth = $dbh->prepare($sql);
$sth->bind_param( 1, $col_data"); #col_data can contain anything
$sth->execute;

Oracle supports bind parameters with ":names" instead of ?, but it is not
portable. ODBC also uses ?s.


Another easy fix for cross database portability is $DBI::err and
$DBI::errstr. $DBI::err will get set if there is an error, $DBI::errstr
will contain the error. You don't have to use $Mysql::db_errstr.

just my .02 upon looking at some of the code too.

$::cody