Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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

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
oneDayAtaTime
Asked:
oneDayAtaTime
  • 2
  • 2
1 Solution
 
sdstuberCommented:
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
 
oneDayAtaTimeAuthor Commented:
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
 
sdstuberCommented:
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
 
oneDayAtaTimeAuthor Commented:
Thanks for the clarification!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now