DBD::Oracle - prepare() and the utf-8 flag.

The method $dbh->prepare($stmt) of DBD::Oracle ignores the
state of the utf8 flag in the SV for $stmt. For example,
after

my $a = "select '\xe2' from dual";
my $b = decode_utf8("select '\xc3\xa2' from dual");

$a and $b compare equal with the perl 'eq' operator. However,
their internal representations differ. $a is represented in
iso-8859-1 and its utf8 flag is off. $b is represented in
utf-8 and its utf8 flag is on.

The two equal statements give different results when run
using DBD::Oracle. Which gives the 'correct' result depends
on the client-side database character set (the NLS_LANG
charset). $a gives the correct result for 8-bit charsets.
$b gives the correct result if the charset is utf-8.

This is clearly a bug. It can affect any SQL statement which
contains a non-ASCII character. It can strike whether or not
Unicode is being used in the database. I would like to fix it.
This requires that code be put somewhere which decides how to
process the SV on the basis of its utf8 flag and of the
NLS_LANG charset.

There is a problem.

At the C level, the following code, from the file Oracle.c,
is implicated:

> XS(XS_DBD__Oracle__st__prepare)
> {
> dXSARGS;
> if (items < 2 || items > 3)
> Perl_croak(aTHX_ "Usage: DBD::Oracle::st::_prepare(sth, statement, attribs=Nullsv)");
> {
> SV * sth = ST(0);
> char * statement = (char *)SvPV_nolen(ST(1));
> SV * attribs;
>
> if (items < 3)
> attribs = Nullsv;
> else {
> attribs = ST(2);
> }
> #line 411 "Oracle.xsi"
> {
> D_imp_sth(sth);
> DBD_ATTRIBS_CHECK("_prepare", sth, attribs);
> ST(0) = dbd_st_prepare(sth, imp_sth, statement, attribs) ? &sv_yes : &sv_no;
> }
> #line 623 "Oracle.c"
> }
> XSRETURN(1);
> }

It is the macro SvPV_nolen which ignores the utf8 flag. This line
is the bug. By the time we get into dbd_st_prepare it is too late,
the utf8 flag is lost.

The way I would like to fix the bug is to change the prototype of
dbd_st_prepare so that it accepts the statement as an (SV *) rather
than a (char *), and change DBD::Oracle::st::_prepare so that it
passes the SV on rather than calling SvPV... itself.

It would then be a small job to put the correct handling of the
utf8 bit flag in the SV into dbd_st_prepare.

The problem is that the above code, which is the implementation
of DBD::Oracle::st::_prepare, is not part of the DBD::Oracle
distribution, nor is it derived from this distribution. It is
actually derived from the file Driver.xst, which is part of the
DBI distribution.

This has two consequences.

1. The bug must be present in all drivers which use Driver.xst :=(

2. I can't see at all how to fix it for DBD::Oracle. It seems that
a fix _requires_ an incompatible change to Driver.xst. The
bug cannot be fixed without change to the prototype for
dbd_st_prepare. However, such a change to the DBI seems
out of the question.

Can anyone suggest a way forward?

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

Re: DBD::Oracle - prepare() and the utf-8 flag.

On Fri, Aug 12, 2005 at 04:45:50PM +0100, Charles Jardine wrote:
> The method $dbh->prepare($stmt) of DBD::Oracle ignores the
> state of the utf8 flag in the SV for $stmt.

> At the C level, the following code, from the file Oracle.c,
> is implicated:
>
> >XS(XS_DBD__Oracle__st__prepare)

> The problem is that the above code, which is the implementation
> of DBD::Oracle::st::_prepare, is not part of the DBD::Oracle
> distribution, nor is it derived from this distribution. It is
> actually derived from the file Driver.xst, which is part of the
> DBI distribution.
>
> This has two consequences.
>
> 1. The bug must be present in all drivers which use Driver.xst :=(
>
> 2. I can't see at all how to fix it for DBD::Oracle. It seems that
> a fix _requires_ an incompatible change to Driver.xst. The
> bug cannot be fixed without change to the prototype for
> dbd_st_prepare. However, such a change to the DBI seems
> out of the question.
>
> Can anyone suggest a way forward?

This change to Driver.xst enables drivers to define a dbd_st_prepare_sv
function (via a macro) that can then do what's required:

--- Driver.xst (revision 1000)
+++ Driver.xst (working copy)
@@ -411,13 +411,17 @@
void
_prepare(sth, statement, attribs=Nullsv)
SV * sth
- char * statement
+ SV * statement
SV * attribs
CODE:
{
D_imp_sth(sth);
DBD_ATTRIBS_CHECK("_prepare", sth, attribs);
- ST(0) = dbd_st_prepare(sth, imp_sth, statement, attribs) ? &sv_yes : &sv_no;
+#ifdef dbd_st_prepare_sv
+ ST(0) = dbd_st_prepare_sv(sth, imp_sth, statement, attribs) ? &sv_yes : &sv_no;
+#else
+ ST(0) = dbd_st_prepare(sth, imp_sth, SVPV_nolen(statement), attribs) ? &sv_yes : &sv_no;
+#endif
}

If that seems okay to you then I'll include it in the next DBI release.
Then I'll happily accept a patch that adds an ora_st_prepare_sv function
to DBD::Oracle. (The existing ora_st_prepare function can wrap the
statement param in a mortal SV then call ora_st_prepare_sv).

Thanks Charles!

Tim.

Re: DBD::Oracle - prepare() and the utf-8 flag.

--+QahgC5+KEYLbs62
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2005-08-12 16:45:50 +0100, Charles Jardine wrote:
> The method $dbh->prepare($stmt) of DBD::Oracle ignores the
> state of the utf8 flag in the SV for $stmt. For example,
> after
>=20
> my $a =3D "select '\xe2' from dual";
> my $b =3D decode_utf8("select '\xc3\xa2' from dual");
>=20
> $a and $b compare equal with the perl 'eq' operator. However,
> their internal representations differ. $a is represented in
> iso-8859-1 and its utf8 flag is off. $b is represented in
> utf-8 and its utf8 flag is on.
>=20
> The two equal statements give different results when run
> using DBD::Oracle. Which gives the 'correct' result depends
> on the client-side database character set (the NLS_LANG
> charset). $a gives the correct result for 8-bit charsets.
> $b gives the correct result if the charset is utf-8.
>=20
> This is clearly a bug. It can affect any SQL statement which
> contains a non-ASCII character. It can strike whether or not
> Unicode is being used in the database. I would like to fix it.
> This requires that code be put somewhere which decides how to
> process the SV on the basis of its utf8 flag and of the
> NLS_LANG charset.

I am not sure if it is possible to define the right behaviour.

Theoretically, it's simple. Since "\xe2" and decode_utf8("\xc3\xa2")
compare equal, they must be equal, so "\xe2" always is an "=E2",
regardless of the NLS_LANG setting. DBD::Oracle would then convert all
strings to the client character set or quietly set the client character
set AL32UTF8 (or UTF8 for older Oracle versions).

However, I am sure there exist tons of code which expect that perl
strings are byte arrays in the client character set. This code would
then break.

I propose the following compromise:

If the client character set is AL32UTF8 or UTF8, then all strings passed
to prepare or inserted into or compared to varchar2 or clob fields are
silently upgraded to utf8. All varchar2 and clob values returned by
DBD::Oracle are in utf8 representation.

Otherwise, all strings are treated as byte arrays in the client
character set.=20

The documentation mentions in large, friendly letters that the character
set should be set to AL32UTF8 to get consistent behaviour.

hp

--=20
_ | Peter J. Holzer | In our modern say,learn,know in a day
|_|_) | Sysadmin WSR | world, perhaps being an expert is an
| | | hjp@wsr.ac.at | outdated concept.
__/ | http://www.hjp.at/ | -- Catharine Drozdowski on dbi-users.

--+QahgC5+KEYLbs62
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQv7/jFLjemazOuKpAQGx+wXUCBgs56KMMcluU5SCMBWazo3BWuIT tM3Q
NarKCF0sQxYrv33qduUmIRIloVuAUdvLky/S+6wm0d1iHEA0hWffzB8eFtDO bWGF
oZqhBqFeetYioQsYb4+N+VBCFS4QK0Q4sB7zLFPtkqahSYImxCMLsvPRPpiA Wkfz
cxSqcjHhCvt22QBnP0kG9km296XuOGZ4JWFL1cwIu3R82GgRD+TQe5rIepTr 0PW8
DFlNSGfr4dzUXZToQOERN7vneQ==
=eaFB
-----END PGP SIGNATURE-----

--+QahgC5+KEYLbs62--

Re: DBD::Oracle - prepare() and the utf-8 flag.

Tim Bunce wrote, on 12/08/2005 22:21:
> On Fri, Aug 12, 2005 at 04:45:50PM +0100, Charles Jardine wrote:
>
>>The method $dbh->prepare($stmt) of DBD::Oracle ignores the
>>state of the utf8 flag in the SV for $stmt.

[ details snipped ]

>>
>>Can anyone suggest a way forward?
>
>
> This change to Driver.xst enables drivers to define a dbd_st_prepare_sv
> function (via a macro) that can then do what's required:
>
> --- Driver.xst (revision 1000)
> +++ Driver.xst (working copy)
> @@ -411,13 +411,17 @@
> void
> _prepare(sth, statement, attribs=Nullsv)
> SV * sth
> - char * statement
> + SV * statement
> SV * attribs
> CODE:
> {
> D_imp_sth(sth);
> DBD_ATTRIBS_CHECK("_prepare", sth, attribs);
> - ST(0) = dbd_st_prepare(sth, imp_sth, statement, attribs) ? &sv_yes : &sv_no;
> +#ifdef dbd_st_prepare_sv
> + ST(0) = dbd_st_prepare_sv(sth, imp_sth, statement, attribs) ? &sv_yes : &sv_no;
> +#else
> + ST(0) = dbd_st_prepare(sth, imp_sth, SVPV_nolen(statement), attribs) ? &sv_yes : &sv_no;
> +#endif
> }
>
> If that seems okay to you then I'll include it in the next DBI release.
> Then I'll happily accept a patch that adds an ora_st_prepare_sv function
> to DBD::Oracle.

That looks exactly like what is needed.

> (The existing ora_st_prepare function can wrap the
> statement param in a mortal SV then call ora_st_prepare_sv).

This is the compatibility bridge I could not see for myself.
Thank you Tim. I will proceed on this basis.

BTW. Can you help me with two related queries to do with
$dbh->do()

1. Why is it that, in DBD::Oracle, dbd_db_do is #defined in
dbdimp.h and provided with a prototype in Oracle.h when
there is no implementation? Is this a historical vestige?
I ask because the prototype suggests the same problem
with the utf8 flag.

2. Should you make a similar change to the code after
'#ifdef dbd_db_do4' in Driver.xst? I know that DBD::Oracle
does not use this code, but any driver which does will have
the same problem.

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

Re: DBD::Oracle - prepare() and the utf-8 flag.

Peter J. Holzer wrote, on 14/08/2005 09:23:
> On 2005-08-12 16:45:50 +0100, Charles Jardine wrote:
>
>>The method $dbh->prepare($stmt) of DBD::Oracle ignores the
>>state of the utf8 flag in the SV for $stmt.

[ snip ]

>>This is clearly a bug. It can affect any SQL statement which
>>contains a non-ASCII character. It can strike whether or not
>>Unicode is being used in the database. I would like to fix it.
>>This requires that code be put somewhere which decides how to
>>process the SV on the basis of its utf8 flag and of the
>>NLS_LANG charset.
>
>
> I am not sure if it is possible to define the right behaviour.
>
> Theoretically, it's simple. Since "\xe2" and decode_utf8("\xc3\xa2")
> compare equal, they must be equal, so "\xe2" always is an "â",
> regardless of the NLS_LANG setting. DBD::Oracle would then convert all
> strings to the client character set or quietly set the client character
> set AL32UTF8 (or UTF8 for older Oracle versions).
>
> However, I am sure there exist tons of code which expect that perl
> strings are byte arrays in the client character set. This code would
> then break.
>
> I propose the following compromise:
>
> If the client character set is AL32UTF8 or UTF8, then all strings passed
> to prepare or inserted into or compared to varchar2 or clob fields are
> silently upgraded to utf8. All varchar2 and clob values returned by
> DBD::Oracle are in utf8 representation.

This what I am proposing for prepare. It already happens for values
sent of returned via placeholders and to fetched values.

> Otherwise, all strings are treated as byte arrays in the client
> character set.

The change I am proposing for 8-bit client character sets is that
the perl strings passed to prepare should be downgraded to bytes,
if necessary, before being treated as you say. This will not
affect any legacy perl code, but will fix a problem for code
like mine, which receives SQL over a utf8 channel and runs
it against a non-utf8 database.

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

Re: DBD::Oracle - prepare() and the utf-8 flag.

On Mon, Aug 15, 2005 at 11:47:18AM +0100, Charles Jardine wrote:
>
> BTW. Can you help me with two related queries to do with
> $dbh->do()
>
> 1. Why is it that, in DBD::Oracle, dbd_db_do is #defined in
> dbdimp.h and provided with a prototype in Oracle.h when
> there is no implementation? Is this a historical vestige?
> I ask because the prototype suggests the same problem
> with the utf8 flag.

Historical vestige. Please include removing these lines in your patch:
Oracle.h:int dbd_db_do _((SV *sv, char *statement));
dbdimp.h:#define dbd_db_do ora_db_do

> 2. Should you make a similar change to the code after
> '#ifdef dbd_db_do4' in Driver.xst? I know that DBD::Oracle
> does not use this code, but any driver which does will have
> the same problem.

Yes. Make it #if defined(dbd_db_do4) || defined(dbd_db_do4_sv)
Thanks!

Tim.

Re: DBD::Oracle - prepare() and the utf-8 flag.

On Mon, Aug 15, 2005 at 12:07:03PM +0100, Charles Jardine wrote:
> Peter J. Holzer wrote, on 14/08/2005 09:23:
> >On 2005-08-12 16:45:50 +0100, Charles Jardine wrote:
> >
> >>The method $dbh->prepare($stmt) of DBD::Oracle ignores the
> >>state of the utf8 flag in the SV for $stmt.
>
> [ snip ]

> >I am not sure if it is possible to define the right behaviour.
> >
> >Theoretically, it's simple. Since "\xe2" and decode_utf8("\xc3\xa2")
> >compare equal, they must be equal, so "\xe2" always is an "?",
> >regardless of the NLS_LANG setting. DBD::Oracle would then convert all
> >strings to the client character set or quietly set the client character
> >set AL32UTF8 (or UTF8 for older Oracle versions).
> >
> >However, I am sure there exist tons of code which expect that perl
> >strings are byte arrays in the client character set. This code would
> >then break.
> >
> >I propose the following compromise:
> >
> >If the client character set is AL32UTF8 or UTF8, then all strings passed
> >to prepare or inserted into or compared to varchar2 or clob fields are
> >silently upgraded to utf8. All varchar2 and clob values returned by
> >DBD::Oracle are in utf8 representation.
>
> This what I am proposing for prepare. It already happens for values
> sent of returned via placeholders and to fetched values.

Actually it's not quite what happens. There's no silent upgrading.

> >Otherwise, all strings are treated as byte arrays in the client
> >character set.
>
> The change I am proposing for 8-bit client character sets is that
> the perl strings passed to prepare should be downgraded to bytes,
> if necessary, before being treated as you say. This will not
> affect any legacy perl code, but will fix a problem for code
> like mine, which receives SQL over a utf8 channel and runs
> it against a non-utf8 database.

Basically if the $statement scalar variable is marked as UTF8 then
tell OCI that the charset of the SQL statement is AL32UTF8.
If not then there should be *no change* to the current behaviour.

Tim.

Re: DBD::Oracle - prepare() and the utf-8 flag.

Tim Bunce wrote, on 15/08/2005 14:36:
> On Mon, Aug 15, 2005 at 12:07:03PM +0100, Charles Jardine wrote:
>
>>Peter J. Holzer wrote, on 14/08/2005 09:23:
>>
>>>However, I am sure there exist tons of code which expect that perl
>>>strings are byte arrays in the client character set. This code would
>>>then break.
>>>
>>>I propose the following compromise:
>>>
>>>If the client character set is AL32UTF8 or UTF8, then all strings passed
>>>to prepare or inserted into or compared to varchar2 or clob fields are
>>>silently upgraded to utf8. All varchar2 and clob values returned by
>>>DBD::Oracle are in utf8 representation.
>>
>>This what I am proposing for prepare. It already happens for values
>>sent of returned via placeholders and to fetched values.
>
>
> Actually it's not quite what happens. There's no silent upgrading.

Sorry. I did insufficient research before forming a plan.

I was intending to provide silent upgrading for byte-encoded
statements being prepared when the client side database
charset is utf8.

Now Tim points out that this is not how bound input values
behave in the current implementation, I realise I have to think
again.

So, I won't be submitting a patch soon. When I can find time,
I will post with the intention of starting a discussion
about upgrading and downgrading.

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

Re: DBD::Oracle - prepare() and the utf-8 flag.

On Wed, Aug 17, 2005 at 06:01:03PM +0100, Charles Jardine wrote:
> Tim Bunce wrote, on 15/08/2005 14:36:
> >On Mon, Aug 15, 2005 at 12:07:03PM +0100, Charles Jardine wrote:
> >
> >>Peter J. Holzer wrote, on 14/08/2005 09:23:
> >>
> >>>However, I am sure there exist tons of code which expect that perl
> >>>strings are byte arrays in the client character set. This code would
> >>>then break.
> >>>
> >>>I propose the following compromise:
> >>>
> >>>If the client character set is AL32UTF8 or UTF8, then all strings passed
> >>>to prepare or inserted into or compared to varchar2 or clob fields are
> >>>silently upgraded to utf8. All varchar2 and clob values returned by
> >>>DBD::Oracle are in utf8 representation.
> >>
> >>This what I am proposing for prepare. It already happens for values
> >>sent of returned via placeholders and to fetched values.
> >
> >Actually it's not quite what happens. There's no silent upgrading.
>
> Sorry. I did insufficient research before forming a plan.
>
> I was intending to provide silent upgrading for byte-encoded
> statements being prepared when the client side database
> charset is utf8.
>
> Now Tim points out that this is not how bound input values
> behave in the current implementation, I realise I have to think
> again.
>
> So, I won't be submitting a patch soon. When I can find time,
> I will post with the intention of starting a discussion
> about upgrading and downgrading.

Why? What I proposed requires less work and is in-keeping with
how DBD::Oracle behaves for bind values etc.

(If you want to change how DBD::Oracle behaves for bind values etc.
then you're staring into a can of worms the size of which you can
barely imagine. Trust me, I've been there along with a few brave souls
who all bare the scars.)

If the $statement is UTF8 then simply tell Oracle that it's UTF8.
Simple.

Tim.

Re: DBD::Oracle - prepare() and the utf-8 flag.

Tim Bunce wrote, on 17/08/2005 21:23:
> On Wed, Aug 17, 2005 at 06:01:03PM +0100, Charles Jardine wrote:
>
>>Tim Bunce wrote, on 15/08/2005 14:36:
>>
>>>On Mon, Aug 15, 2005 at 12:07:03PM +0100, Charles Jardine wrote:
>>>
>>>>This what I am proposing for prepare. It already happens for values
>>>>sent of returned via placeholders and to fetched values.
>>>
>>>Actually it's not quite what happens. There's no silent upgrading.
>>
>>Sorry. I did insufficient research before forming a plan.
>>
>>I was intending to provide silent upgrading for byte-encoded
>>statements being prepared when the client side database
>>charset is utf8.
>>
>>Now Tim points out that this is not how bound input values
>>behave in the current implementation, I realise I have to think
>>again.
>>
>>So, I won't be submitting a patch soon. When I can find time,
>>I will post with the intention of starting a discussion
>>about upgrading and downgrading.
>
>
> Why? What I proposed requires less work and is in-keeping with
> how DBD::Oracle behaves for bind values etc.
>
> (If you want to change how DBD::Oracle behaves for bind values etc.
> then you're staring into a can of worms the size of which you can
> barely imagine. Trust me, I've been there along with a few brave souls
> who all bare the scars.)

I am aware that there are worms. This is why I need more time to
think before say anything.

> If the $statement is UTF8 then simply tell Oracle that it's UTF8.
> Simple.

Unfortunately this isn't as easy as it is when binding. OCI
statement handles do not support OCI_ATTR_CHARSET_ID. They
inherit their encoding settings from their environment handle
at create time, and do not expose them for inspection or
change. It seems it will be necessary to create the statement
handle using a different environment handle. As a side effect,
this will change the default charsets for bind and define
handles under this statement handle. This, in turn, may
require change in the code for binding and defining to
preserve the existing behaviour.

This patch is going to take some time. Nevertheless, I
shall attempt it. It would be good to have the behaviour of
prepare() consistent with that of bind_param().

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

Re: DBD::Oracle - prepare() and the utf-8 flag - revisited

On Fri, Aug 19, 2005 at 01:52:54PM +0100, Charles Jardine wrote:
> Tim Bunce wrote, on 17/08/2005 21:23:
>
> >If the $statement is UTF8 then simply tell Oracle that it's UTF8.
> >Simple.
>
> Unfortunately this isn't as easy as it is when binding. OCI
> statement handles do not support OCI_ATTR_CHARSET_ID. They
> inherit their encoding settings from their environment handle
> at create time, and do not expose them for inspection or change.

Ah. I'd forgotten that. Thanks. That puts a different light on it...

> It seems it will be necessary to create the statement
> handle using a different environment handle.

Let's go back to your original message:

On Fri, Aug 12, 2005 at 04:45:50PM +0100, Charles Jardine wrote:
: The method $dbh->prepare($stmt) of DBD::Oracle ignores the
: state of the utf8 flag in the SV for $stmt. For example,
: after
:
: my $a = "select '\xe2' from dual";
: my $b = decode_utf8("select '\xc3\xa2' from dual");
:
: $a and $b compare equal with the perl 'eq' operator. However,
: their internal representations differ. $a is represented in
: iso-8859-1 and its utf8 flag is off. $b is represented in
: utf-8 and its utf8 flag is on.
:
: The two equal statements give different results when run
: using DBD::Oracle. Which gives the 'correct' result depends
: on the client-side database character set (the NLS_LANG
: charset). $a gives the correct result for 8-bit charsets.
: $b gives the correct result if the charset is utf-8.
:
: This is clearly a bug.

Why? (I'm not sure it isn't, but I'd like a stronger explanation,
and one that's explained with reference to the whole of
http://search.cpan.org/~timb/DBD-Oracle/Oracle.pm#Unicode )

The approach DBD::Oracle takes is that the application should
behave in accordance with NLS_LANG. To use UTF8 in your SQL
you should set NLS_LANG to UTF8. If your NLS_LANG is not UTF8 then
don't try to give UTF8 strings to Oracle.

The DBD::Oracle docs explicitly say:

* Sending Data using SQL
*
* Oracle assumes the SQL statement is in the default client character set
* (as specified by NLS_LANG). So Unicode strings containing non-ASCII
* characters should not be used unless the default client character set
* is AL32UTF8.

Tim.

: It can affect any SQL statement which
: contains a non-ASCII character. It can strike whether or not
: Unicode is being used in the database. I would like to fix it.
: This requires that code be put somewhere which decides how to
: process the SV on the basis of its utf8 flag and of the
: NLS_LANG charset.


> As a side effect,
> this will change the default charsets for bind and define
> handles under this statement handle. This, in turn, may
> require change in the code for binding and defining to
> preserve the existing behaviour.
>
> This patch is going to take some time. Nevertheless, I
> shall attempt it. It would be good to have the behaviour of
> prepare() consistent with that of bind_param().