[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-06-10
4
Medium Priority
?
316 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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

649 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