Looking for help with odd DBD::Oracle::db prepare behavior

I've been using DBD::Oracle::db from my Perl scripts for some time and have
had no problems. Today, I've started to get some odd behavior. I create a
delete string and attempt to prepare it. I get a ORA-00936 error meesage. I
don't know why. I create a delete string with multiple items in the "where
... in" clause:

new_delete = delete from damping.search_criteria where
rdb_search_criteria_id in ( 56554 , 56558 )

I then call the prepare statement with the new_delete string and get the
following:

DBD::Oracle::db prepare failed: ORA-00936:
missing expression (DBD: error possibly near <*> indicator at char
79 in
'delete from damping.search_criteria where rdb_search_criteria_id in
( 56554 , <*>') at
/pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.

I then decided to try breaking up the statement into individual calls:

new_delete = delete from damping.search_criteria where
rdb_search_criteria_id = 56554

The first statement prepared and executed fine.

new_delete = delete from damping.search_criteria where
rdb_search_criteria_id = 56558

The second statement failed to prepare:

DBD::Oracle::db prepare failed: ORA-00936:
missing expression (DBD: error possibly near <*> indicator at char
68 in
'delete from damping.search_criteria where rdb_search_criteria_id =
<*>' at
/pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.

Can anyone explain what the problem is and how to get around it?

------------------------------------------------------------
J. Robert Nelli

Re: Looking for help with odd DBD::Oracle::db prepare behavior

On 4/2/07, Nelli, Joseph R. Jr. wrote:
> I've been using DBD::Oracle::db from my Perl scripts for some time and have
> had no problems. Today, I've started to get some odd behavior. I create a
> delete string and attempt to prepare it. I get a ORA-00936 error meesage. I
> don't know why. I create a delete string with multiple items in the "where
> .. in" clause:
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id in ( 56554 , 56558 )
>
> I then call the prepare statement with the new_delete string and get the
> following:
>
> DBD::Oracle::db prepare failed: ORA-00936:
> missing expression (DBD: error possibly near <*> indicator at char
> 79 in
> 'delete from damping.search_criteria where rdb_search_criteria_id in
> ( 56554 , <*>') at
> /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.
>
> I then decided to try breaking up the statement into individual calls:
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id = 56554
>
> The first statement prepared and executed fine.
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id = 56558
>
> The second statement failed to prepare:
>
> DBD::Oracle::db prepare failed: ORA-00936:
> missing expression (DBD: error possibly near <*> indicator at char
> 68 in
> 'delete from damping.search_criteria where rdb_search_criteria_id =
> <*>' at
> /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.
>
> Can anyone explain what the problem is and how to get around it?

Just a wild guess: are you sure there's nothing strange in between
your SQL? Something like non-printing characters and the like?

Build your SQL string and take a look at it with a dumper. For instance,

use Data::Dump 'dump';
print dump $sql;

# or

use YAML 'Dump';
print Dump $sql;

# or

use Data::Dumper 'Dumper';
$Data::Dumper::Useqq = 1;
print Dumper $sql;

RE: Looking for help with odd DBD::Oracle::db prepare behavior

Thanks for the pointer. I get very interesting results using Dumper. My
print statement shows:

new_delete = delete from damping.search_criteria where
rdb_search_criteria_id in ( 56554 , 56558 , 56559 )

But the Dumper print statement shows:

$VAR1 = "delete from damping.search_criteria where rdb_search_criteria_id in
( 56554 , \00056558 , \00056559 )";

Any ideas on how this may be happening or how to get around it? I'm
basically passing the following string through a cgi argument:
"56554-56558-56559-" . I then convert the dashes to commas so I can place
the string into the delete statement.

-----Original Message-----
From: Adriano Ferreira [mailto:a.r.ferreira@gmail.com]
Sent: Monday, April 02, 2007 4:22 PM
To: Nelli, Joseph R. Jr.; dbi-users@perl.org
Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior

On 4/2/07, Nelli, Joseph R. Jr. wrote:
> I've been using DBD::Oracle::db from my Perl scripts for some time and
have
> had no problems. Today, I've started to get some odd behavior. I create a
> delete string and attempt to prepare it. I get a ORA-00936 error meesage.
I
> don't know why. I create a delete string with multiple items in the
"where
> .. in" clause:
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id in ( 56554 , 56558 )
>
> I then call the prepare statement with the new_delete string and get the
> following:
>
> DBD::Oracle::db prepare failed: ORA-00936:
> missing expression (DBD: error possibly near <*> indicator at char
> 79 in
> 'delete from damping.search_criteria where rdb_search_criteria_id
in
> ( 56554 , <*>') at
> /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.
>
> I then decided to try breaking up the statement into individual calls:
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id = 56554
>
> The first statement prepared and executed fine.
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id = 56558
>
> The second statement failed to prepare:
>
> DBD::Oracle::db prepare failed: ORA-00936:
> missing expression (DBD: error possibly near <*> indicator at char
> 68 in
> 'delete from damping.search_criteria where rdb_search_criteria_id
=
> <*>' at
> /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.
>
> Can anyone explain what the problem is and how to get around it?

Just a wild guess: are you sure there's nothing strange in between
your SQL? Something like non-printing characters and the like?

Build your SQL string and take a look at it with a dumper. For instance,

use Data::Dump 'dump';
print dump $sql;

# or

use YAML 'Dump';
print Dump $sql;

# or

use Data::Dumper 'Dumper';
$Data::Dumper::Useqq = 1;
print Dumper $sql;

Re: Looking for help with odd DBD::Oracle::db prepare behavior

On 4/3/07, Nelli, Joseph R. Jr. wrote:
> Thanks for the pointer. I get very interesting results using Dumper. My
> print statement shows:
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id in ( 56554 , 56558 , 56559 )
>
> But the Dumper print statement shows:
>
> $VAR1 = "delete from damping.search_criteria where rdb_search_criteria_id in
> ( 56554 , \00056558 , \00056559 )";

"\00056558" means a nul byte "\0" followed by "56558".

> Any ideas on how this may be happening or how to get around it? I'm
> basically passing the following string through a cgi argument:
> "56554-56558-56559-" . I then convert the dashes to commas so I can place
> the string into the delete statement.

There is something weird happening. These \0 bytes are included
somewhere along the processing chain, maybe in the HTML itself, maybe
in your conversion routine.

If you do

$cgi_arg =~ s/[^\d-]/?/;

you'll have a chance to see if the CGI argument already carries other
bytes than digits and the dash. They will be converted to question
marks ('?').

You may want to massage your CGI arguments before sticking them into
SQL (as this is a good chance for an injection attack). For instance,
you may split the argument and then sanitize the parts to make sure
they contain only digits:

my @args = split '-', $arg;
s/[^\d]// for @args;

>
> -----Original Message-----
> From: Adriano Ferreira [mailto:a.r.ferreira@gmail.com]
> Sent: Monday, April 02, 2007 4:22 PM
> To: Nelli, Joseph R. Jr.; dbi-users@perl.org
> Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior
>
> On 4/2/07, Nelli, Joseph R. Jr. wrote:
> > I've been using DBD::Oracle::db from my Perl scripts for some time and
> have
> > had no problems. Today, I've started to get some odd behavior. I create a
> > delete string and attempt to prepare it. I get a ORA-00936 error meesage.
> I
> > don't know why. I create a delete string with multiple items in the
> "where
> > .. in" clause:
> >
> > new_delete = delete from damping.search_criteria where
> > rdb_search_criteria_id in ( 56554 , 56558 )
> >
> > I then call the prepare statement with the new_delete string and get the
> > following:
> >
> > DBD::Oracle::db prepare failed: ORA-00936:
> > missing expression (DBD: error possibly near <*> indicator at char
> > 79 in
> > 'delete from damping.search_criteria where rdb_search_criteria_id
> in
> > ( 56554 , <*>') at
> > /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.
> >
> > I then decided to try breaking up the statement into individual calls:
> >
> > new_delete = delete from damping.search_criteria where
> > rdb_search_criteria_id = 56554
> >
> > The first statement prepared and executed fine.
> >
> > new_delete = delete from damping.search_criteria where
> > rdb_search_criteria_id = 56558
> >
> > The second statement failed to prepare:
> >
> > DBD::Oracle::db prepare failed: ORA-00936:
> > missing expression (DBD: error possibly near <*> indicator at char
> > 68 in
> > 'delete from damping.search_criteria where rdb_search_criteria_id
> =
> > <*>' at
> > /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 10887.
> >
> > Can anyone explain what the problem is and how to get around it?
>
> Just a wild guess: are you sure there's nothing strange in between
> your SQL? Something like non-printing characters and the like?
>
> Build your SQL string and take a look at it with a dumper. For instance,
>
> use Data::Dump 'dump';
> print dump $sql;
>
> # or
>
> use YAML 'Dump';
> print Dump $sql;
>
> # or
>
> use Data::Dumper 'Dumper';
> $Data::Dumper::Useqq = 1;
> print Dumper $sql;
>

Re: Looking for help with odd DBD::Oracle::db prepare behavior

Hard to say whitout seeing your origninal Perl code that strips out the '-'

Just a quick suggestion is to use scalar() (should force them to be a
number) around each befor you add the ','.

Getting in staments like this working in DBD::Oracle is one of my little pet
projects if I ever get any time again.

cheers


----- Original Message -----
From: "Nelli, Joseph R. Jr."
To: "Adriano Ferreira" ; "Nelli, Joseph R. Jr."
;
Sent: Tuesday, April 03, 2007 8:27 AM
Subject: RE: Looking for help with odd DBD::Oracle::db prepare behavior


> Thanks for the pointer. I get very interesting results using Dumper. My
> print statement shows:
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id in ( 56554 , 56558 , 56559 )
>
> But the Dumper print statement shows:
>
> $VAR1 = "delete from damping.search_criteria where rdb_search_criteria_id
> in
> ( 56554 , \00056558 , \00056559 )";
>
> Any ideas on how this may be happening or how to get around it? I'm
> basically passing the following string through a cgi argument:
> "56554-56558-56559-" . I then convert the dashes to commas so I can place
> the string into the delete statement.
>
> -----Original Message-----
> From: Adriano Ferreira [mailto:a.r.ferreira@gmail.com]
> Sent: Monday, April 02, 2007 4:22 PM
> To: Nelli, Joseph R. Jr.; dbi-users@perl.org
> Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior
>
> On 4/2/07, Nelli, Joseph R. Jr. wrote:
>> I've been using DBD::Oracle::db from my Perl scripts for some time and
> have
>> had no problems. Today, I've started to get some odd behavior. I create a
>> delete string and attempt to prepare it. I get a ORA-00936 error meesage.
> I
>> don't know why. I create a delete string with multiple items in the
> "where
>> .. in" clause:
>>
>> new_delete = delete from damping.search_criteria where
>> rdb_search_criteria_id in ( 56554 , 56558 )
>>
>> I then call the prepare statement with the new_delete string and get the
>> following:
>>
>> DBD::Oracle::db prepare failed: ORA-00936:
>> missing expression (DBD: error possibly near <*> indicator at
>> char
>> 79 in
>> 'delete from damping.search_criteria where rdb_search_criteria_id
> in
>> ( 56554 , <*>') at
>> /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line
>> 10887.
>>
>> I then decided to try breaking up the statement into individual calls:
>>
>> new_delete = delete from damping.search_criteria where
>> rdb_search_criteria_id = 56554
>>
>> The first statement prepared and executed fine.
>>
>> new_delete = delete from damping.search_criteria where
>> rdb_search_criteria_id = 56558
>>
>> The second statement failed to prepare:
>>
>> DBD::Oracle::db prepare failed: ORA-00936:
>> missing expression (DBD: error possibly near <*> indicator at
>> char
>> 68 in
>> 'delete from damping.search_criteria where rdb_search_criteria_id
> =
>> <*>' at
>> /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line
>> 10887.
>>
>> Can anyone explain what the problem is and how to get around it?
>
> Just a wild guess: are you sure there's nothing strange in between
> your SQL? Something like non-printing characters and the like?
>
> Build your SQL string and take a look at it with a dumper. For instance,
>
> use Data::Dump 'dump';
> print dump $sql;
>
> # or
>
> use YAML 'Dump';
> print Dump $sql;
>
> # or
>
> use Data::Dumper 'Dumper';
> $Data::Dumper::Useqq = 1;
> print Dumper $sql;
>

RE: Looking for help with odd DBD::Oracle::db prepare behavior

It appears that the invisible characters are delimiters passed via the HTML
in a Multi-select form item. I was passing the dashes between the items so
that I could parse the string argument into its pieces. I was unaware that
the HTML already was putting a delimiter in for me (that I couldn't see). I
now parse on the \000 character instead of the '-'. Thanks.

-----Original Message-----
From: Adriano Ferreira [mailto:a.r.ferreira@gmail.com]
Sent: Tuesday, April 03, 2007 8:56 AM
To: Nelli, Joseph R. Jr.; dbi-users@perl.org
Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior

On 4/3/07, Nelli, Joseph R. Jr. wrote:
> Thanks for the pointer. I get very interesting results using Dumper. My
> print statement shows:
>
> new_delete = delete from damping.search_criteria where
> rdb_search_criteria_id in ( 56554 , 56558 , 56559 )
>
> But the Dumper print statement shows:
>
> $VAR1 = "delete from damping.search_criteria where rdb_search_criteria_id
in
> ( 56554 , \00056558 , \00056559 )";

"\00056558" means a nul byte "\0" followed by "56558".

> Any ideas on how this may be happening or how to get around it? I'm
> basically passing the following string through a cgi argument:
> "56554-56558-56559-" . I then convert the dashes to commas so I can place
> the string into the delete statement.

There is something weird happening. These \0 bytes are included
somewhere along the processing chain, maybe in the HTML itself, maybe
in your conversion routine.

If you do

$cgi_arg =~ s/[^\d-]/?/;

you'll have a chance to see if the CGI argument already carries other
bytes than digits and the dash. They will be converted to question
marks ('?').

You may want to massage your CGI arguments before sticking them into
SQL (as this is a good chance for an injection attack). For instance,
you may split the argument and then sanitize the parts to make sure
they contain only digits:

my @args = split '-', $arg;
s/[^\d]// for @args;

>
> -----Original Message-----
> From: Adriano Ferreira [mailto:a.r.ferreira@gmail.com]
> Sent: Monday, April 02, 2007 4:22 PM
> To: Nelli, Joseph R. Jr.; dbi-users@perl.org
> Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior
>
> On 4/2/07, Nelli, Joseph R. Jr. wrote:
> > I've been using DBD::Oracle::db from my Perl scripts for some time and
> have
> > had no problems. Today, I've started to get some odd behavior. I create
a
> > delete string and attempt to prepare it. I get a ORA-00936 error
meesage.
> I
> > don't know why. I create a delete string with multiple items in the
> "where
> > .. in" clause:
> >
> > new_delete = delete from damping.search_criteria where
> > rdb_search_criteria_id in ( 56554 , 56558 )
> >
> > I then call the prepare statement with the new_delete string and get the
> > following:
> >
> > DBD::Oracle::db prepare failed: ORA-00936:
> > missing expression (DBD: error possibly near <*> indicator at
char
> > 79 in
> > 'delete from damping.search_criteria where
rdb_search_criteria_id
> in
> > ( 56554 , <*>') at
> > /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line
10887.
> >
> > I then decided to try breaking up the statement into individual calls:
> >
> > new_delete = delete from damping.search_criteria where
> > rdb_search_criteria_id = 56554
> >
> > The first statement prepared and executed fine.
> >
> > new_delete = delete from damping.search_criteria where
> > rdb_search_criteria_id = 56558
> >
> > The second statement failed to prepare:
> >
> > DBD::Oracle::db prepare failed: ORA-00936:
> > missing expression (DBD: error possibly near <*> indicator at
char
> > 68 in
> > 'delete from damping.search_criteria where
rdb_search_criteria_id
> =
> > <*>' at
> > /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line
10887.
> >
> > Can anyone explain what the problem is and how to get around it?
>
> Just a wild guess: are you sure there's nothing strange in between
> your SQL? Something like non-printing characters and the like?
>
> Build your SQL string and take a look at it with a dumper. For instance,
>
> use Data::Dump 'dump';
> print dump $sql;
>
> # or
>
> use YAML 'Dump';
> print Dump $sql;
>
> # or
>
> use Data::Dumper 'Dumper';
> $Data::Dumper::Useqq = 1;
> print Dumper $sql;
>

RE: Looking for help with odd DBD::Oracle::db prepare behavior

Nelli, Joseph R. Jr. wrote:
> It appears that the invisible characters are delimiters passed via
> the HTML in a Multi-select form item. I was passing the dashes
> between the items so that I could parse the string argument into its
> pieces. I was unaware that the HTML already was putting a delimiter
> in for me (that I couldn't see). I now parse on the \000 character
> instead of the '-'. Thanks.=20

Are you using cgi-lib.pl? This is how it separates multi-select and
checkbox group values. CGI.pm's Vars() method does the same thing.

- Philip