Solved

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

Posted on 2013-06-10
4
310 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
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

856 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