sybperl-l Archive

From: Hrvoje Niksic <hniksic at iskon dot hr>
Subject: Re: Arguments to SQL statements in Ctlib
Date: Nov 16 1999 6:30AM

Don Hosek  writes:

> Hrvoje Niksic wrote:
>  
> > Is it possible to send SQL statements with arguments.  I'd like to
> > write something like:
>  
> >     @results = ct_sql("SELECT foo FROM bar WHERE id=?", $needed_it);
>  
> > The ? syntax is borrowed from Perl DBI and is not required, but it
> > should make my meaning clear.
> 
> I don't understand why you can't write
> 	@results = ct_sql("select foo from bar where id=$needed_it");

Poorly chosen example, sorry.

Obviously, if ID is something numeric, I can safely use Perl's
interpolation to achieve the same effect.  But:

a) In the general case, I would like the library to take care of the
   quoting details.  For instance, if you write "SELECT ... WHERE
   product_name='$name'", you must take into account that $name could
   contain single quotes, and escape them properly.  I know I can do
   this myself, but I was wondering if there was any preexisting
   functionality (apparently there isn't.)

b) If you are repeating the same query over a differing set of values,
   some databases support "preparing" an SQL statement and then
   optimizing the repetition.  For example, you could write something
   like this (the function names are imaginary, I don't have DBI docs
   handy):

   $opaque = $dbh->prepare("INSERT INTO table VALUES(?, ?, ?)");
   while ... {
     # $foo, $bar, and $baz change over many iterations of the loop.
     $opaque->execute($foo, $bar, $baz);
   }

   Of course, if the database doesn't support this gimmick, DBI will
   fall back to Perl's interpolation, but at least it will get the
   quoting right.


Sorry to waste so much space on all this; it's not that important.  It
was just a question.  :-)