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@xxxxxxxxx]
Sent: Tuesday, April 03, 2007 8:56 AM
To: Nelli, Joseph R. Jr.; dbi-users@xxxxxxxx
Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior

On 4/3/07, Nelli, Joseph R. Jr. <joseph.nelli@xxxxxxxxxx> 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@xxxxxxxxx]
Sent: Monday, April 02, 2007 4:22 PM
To: Nelli, Joseph R. Jr.; dbi-users@xxxxxxxx
Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior

On 4/2/07, Nelli, Joseph R. Jr. <joseph.nelli@xxxxxxxxxx> 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;

.

Relevant Pages

  • Re: [PHP] generating an html intro text ...
    ... You would have to search out and pull in all closing tags. ... grab 256 characters -- The string. ... html markup should not go towards the string length count, ...
    (php.general)
  • Re: can I know how to write a html parser in C
    ... Are the lines truly limited to 80 characters of text? ... null-terminated character string size of 249 characters. ... Note too that in the general case it is perfectly acceptable in HTML ... much a beginner at C (and possibly a beginner at programming ...
    (comp.lang.c)
  • Re: Trouble reading fields containing special characters
    ... I double any possible single quote characters in the string. ... Any double single quotes in a string (all SQL strings are enclosed by single ...
    (microsoft.public.vb.database.ado)
  • Re: Data Validation
    ... Function NoHTML(str As String) As String ... I am putting a string of text in a cell some of which may need to be HTML ... coded for formatting and which must not exceed 30 characters, ...
    (microsoft.public.excel.misc)
  • Re: Data Validation
    ... Function NoHTML(str As String) As String ... Into a test cell and type this in B1: ... I am putting a string of text in a cell some of which may need to be HTML ... coded for formatting and which must not exceed 30 characters, ...
    (microsoft.public.excel.misc)