Re: Interesting Exploit in PL/SQL
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- Date: Wed, 29 Nov 2006 19:26:08 +1100
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
"WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('A serious error has 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?
--
Cheers
Nuno Souto
in windy Sydney, Australia
dbvision@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
