SPUG: SQL syntax Regular Expression

Ben Reser ben at reser.org
Wed Jun 19 00:08:05 CDT 2002

On Tue, Jun 18, 2002 at 09:21:09PM -0700, Parr, Ryan wrote:
> 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.

Basically if you don't use prepare with the place holders Oracle (one I
just happen to know something about) will cache the statement with the
literal data.  If you do use prepare with the place holders it will
cache just the statement with the place holders.  What it cache's is the
"plan" to get the information you requested.  Apparently this
significantly speeds up your queries, though I've never benchmarked it.
Finally it keeps the DBA from getting cranky at you for having a bunch
of garbage in your query cache. :)

On top of all this I wrote a Apache module to do authentication via
Oracle many moons ago.  Last year a bunch of these modules that did
similar things (include some other Oracle authentication modules that
were written by some people in Russia) were found to have horrible
security problems because they weren't properly quoting their
parameters.  Because I used the OCI (Oracle Call Interface, i.e. their C
library) equivalent to prepare with the placeholders in DBI, my
implementation wasn't vulnerable.

So all around it's a great idea to never use quote and always use
prepare with placeholders.

Ben Reser <ben at reser.org>

We tend to see all wars through the lens of the current conflict, and we
mine history for lessons convenient to the present purpose.
- Brian Hayes

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

More information about the spug-list mailing list