Solved

PL/SQL Exploit - Lack of Strong Variable Typing in Parameters?

Posted on 2013-06-10
4
313 Views
Last Modified: 2013-06-12
I'm beginning to write PL/SQL code (in the form of packages), and want to start out doing it correctly. I read the following statements in a training manual, but can't find verification of this anywhere else.

"There are known exploits of PL/SQL that use the lack of strong variable typing in parameters to a disadvantage. Therefore, it is recommended that parameters be assigned to local variables inside the code of the procedure using strongly typed data types."

Basically, they're saying that whenever you bring in a parameter, you should redefine it within your package as a local variable. (Example shown below). My question is whether this is still a known exploit or if it is a non-issue in the newer versions of Oracle? We're using Oracle v11g. Renaming parameters makes the package a bit more complicated for someone else to follow, so I'd rather not rename them unless this is a real threat.
                                  Thanks!


CREATE OR REPLACE PROCEDURE my_proc (myNum NUMBER, myText VARCHAR2) IS
   varNum  NUMBER(5);
   varText   VARCHAR2(15);
BEGIN
   varNum := myNum;
   varText := myText;
.....
END
0
Comment
Question by:oneDayAtaTime
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39234705
Unconstrained parameters are still in 11gR2.

So, assigning them to local variables in order to enforce data constraints is still a good idea if you need to constrain.  If not you can use dbms_assert or other conditions that check your inputs before proceeding.

If however, your procedure acts as a "gateway" of sorts, where data simply passes through it, it may be acceptable and preferable to leave it unconstrained.

For instance a procedure that receives data from an externally generated file or webservice may allow unconstrained parameters and then pass that data to other procedures for validation and cleansing.

Also, generic-reusable routines may be intentionally unconstrained because they need to be able to handle input from a variety of calls.

For example, a function that parses a string may accept a VARCHAR2.
If used within a sql statement that value will be limited to 4000 characters or less (as of 11gR2)
but if used within a pl/sql block that value may be up to 32K.

If you constrained your value to 4000 with a local variable you'd be limiting the reusability of that function within pl/sql.
0
 

Author Comment

by:oneDayAtaTime
ID: 39237474
So....you're saying that the only reason I need to redefine as a local variable is if I need to constrain the size of the variable? I guess I read the "known exploit" part as more of a security hazard. Maybe they mean that if the variable is not constrained, someone could hack the system and pass in something that isn't intended?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39238645
It's not just constraining size but also range of values and nullability.

I don't know of a specific "buffer overrun" type exploit; but if your procedures allow for input that you aren't expecting you can subject yourself to errors, bad return values, data corruption and other "hacks".

Depending on what your procedures do those errors could result in the types of security failures I think you're referring to.
0
 

Author Closing Comment

by:oneDayAtaTime
ID: 39240779
Thanks for the clarification!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question