Named placeholders in DBD::Oracle - bug report

From: Charles Jardine (cj10_at_cam.ac.uk)
Date: 10/17/03 

Date: Fri, 17 Oct 2003 14:07:46 +0100
To:  dbi-users@perl.org

The problem:
-----------

When Oracle matches placeholder names passed to OCIBindByName()
with the actual placeholders in the SQL or PL/SQL, it uses
a case-insensitive comparison. This means that Oracle regards
the placeholders :a and :A as the same.

On the other hand, DBD::Oracle uses case-sensitive comparison
when matching placeholder names, and regards :a and :A as
different.

This has at least two undesirable effects.

 1. If the statement contains two named placeholders which
    differ only in letter_case, they will each contribute
    to the count in $sth->{NUM_OF_PARAMS}, despite the fact
    that this is meant to be a count of _distinct_
    placeholders.

 2. The name passed to $sth->bind_param... must match in
    letter case at least one of the occurences of the
    placeholder in the statement. This is not what an
    OCI programmer would expect. Many of the examples in
    the OCI manual have the names passed to OCIBindByName()
    in upper case, and those in the PL/SQL in lower case.

Below is a tiny perl program which demonstrates this.

The proposed solution:
---------------------

I suggest that DBD::Oracle's handling of placeholder name
comparisons should be changed to be case-insensitive.

Below is a patch to dbdimp.c (version 1.14) which achieves
this by lower-casing the name both at preparse time and at
bind time.

============================
#!/bin/perl -w
use strict;
use DBI;

my $dbh = DBI->connect('dbi:Oracle:', 'foo', 'bah',
   {RaiseError=>1, PrintError=>0, AutoCommit=>0});
my $sth = $dbh->prepare(
  "SELECT decode(:a, :A, 'same', 'different') from dual");

 # The following prints 2, indicating that DBD::Oracle
 # thinks that the above has two distinct placeholders

print $sth->{NUM_OF_PARAMS}, "\n";

$sth->bind_param(':a', 'lower');
$sth->bind_param(':A', 'upper');
$sth->execute;

 # The following prints 'same', indicating that Oracle
 # can't see the difference between the placeholders

print +($sth->fetch)->[0], "\n";

$sth->finish;
$dbh->disconnect;
=============================
--- dbdimp.c.orig Fri Oct 17 13:08:17 2003
+++ dbdimp.c Fri Oct 17 13:08:49 2003
@@ -812,7 +812,7 @@
 
         } else if (isALNUM(*src)) { /* ':foo' */
             while(isALNUM(*src)) /* includes '_' */
- *dest++ = *src++;
+ *dest++ = toLOWER(*src), src++;
             style = ":foo";
         } else { /* perhaps ':=' PL/SQL construct */
             /* if (src == ':') *dest++ = *src++; XXX? move past '::'? */
@@ -1321,7 +1321,7 @@
     SV **phs_svp;
     STRLEN name_len;
     char *name = Nullch;
- char namebuf[30];
+ char namebuf[32]; /* ':' + 30 char name + '\0' */
     phs_t *phs;
 
     /* check if placeholder was passed as a number */
@@ -1329,7 +1329,13 @@
     if (SvGMAGICAL(ph_namesv)) /* eg if from tainted expression */
         mg_get(ph_namesv);
     if (!SvNIOKp(ph_namesv)) {
+ int i;
         name = SvPV(ph_namesv, name_len);
+ if (name_len>31)
+ croak("Placeholder name too long (%s)", neatsvpv(ph_namesv,0));
+ for (i=0; i<name_len; i++) namebuf[i] = toLOWER(name[i]);
+ namebuf[i] = '\0';
+ name = namebuf;
     }
     if (SvNIOKp(ph_namesv) || (name && isDIGIT(name[0]))) {
         sprintf(namebuf, ":p%d", (int)SvIV(ph_namesv));

=================================================== 

-- 
Charles Jardine - Computing Service, University of Cambridge
cj10@cam.ac.uk    Tel: +44 1223 334506, Fax: +44 1223 334679

Relevant Pages