Solved

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

Posted on 2013-06-10
4
309 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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
File generation using utl_file 4 54
Wrap Oraccle SQL*Plus executable Command 4 84
PL/SQL Display based on value 4 27
Oracle - SQL Parse String 5 34
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

773 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