RE: Interesting Exploit in PL/SQL

  • From: "Richard J. Goulet" <rgoulet@xxxxxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 29 Nov 2006 10:43:58 -0500

I have no problem with the "when others then" clause.  It's the NULL
thereafter that irks me & yes I know Oracle does things like that in
their own code.  Doesn't make it right though.  What I prefer, and yes I
do use this in my own code, is :

        when others then msg := msg ||':'|| sqlerrm;
<client>_mail('HelpDesk<helpdesk@[client].com>','Error in Plsql
Procedure <name>',msg);

Where <client>_mail is a plsql wrapper around smtp_mail that sends the
error message & a pointer into the procedure where the error occurred.
At least this way the code runs, but the error is trapped for later
diagnosis & repair.

Dick Goulet, Senior Oracle DBA
45 Bartlett St  Marlborough, Ma 01752, USA
Tel.: 508.573.1978 |Fax:  508.229.2019 | Cell:508.742.5795 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nuno Souto
Sent: Wednesday, November 29, 2006 3:26 AM
Cc: Oracle-L Freelists
Subject: Re: Interesting Exploit in PL/SQL

Tim Gorman wrote,on my timestamp of 29/11/2006 1:35 AM:
> Please read it more carefully.  "WHEN OTHERS THEN NULL" is *never* 
> acceptable;  he's saying to use WHEN OTHERS to close any cursors 
> opened by DBMS_SQL before leaving the program unit.
> In fact, WHEN OTHERS THEN NULL will still leave cursors open and 
> exploitable, while neatly concealing the nature of any encountered 
> exception, in addition to concealing the fact that any error occurred 
> at all, to boot.
> It is a piece of code that should be flagged as a compilation error by

> the PL/SQL compiler.
> For coding stupidity, "WHEN OTHERS THEN NULL" is closely rivaled by 
> occurred')", which brought a large consumer retail e-commerce website 
> to its knees during Christmas season a few years ago.  Talk about a 
> million-dollar piece of code!

Well yes, that's all very nice and according to the latest "rules".
However, let's never forget:

1- I wish I had $1 for every block of PL/SQL code out there who has WHEN
OTHERS THEN NULL coded explicitly or implicitly.
It ain't gonna change overnight!  Or does anyone believe it will?

2- Oracle's OWN code has it.  I know, that is not a recommendation.  But
it's a fact.  And nothing will make it change into oblivion.

3- It IS a perfectly valid syntax and is NOT flagged as an error.

4- If you don't know what error can be generated but you MUST run the
code nevertheless, then you MUST use WHEN OTHERS THEN NULL.  Or pay the
price of a temp table to keep all possible return codes.  Otherwise your
code won't run.  No, I will NOT recommend coding exceptions for 20000
possible error returns!

So, rather than embark in yet another crusade of calling anyone who
doesn't follow the fad an incompetent by nature, can we just for once
address the problem at the root?

And ask Oracle to fix it first rather than blame everyone else for it?

Nuno Souto
in windy Sydney, Australia


Other related posts: