Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> SQL Injection Question
        
(Message started by: Pete Finnigan on Apr 8^th, 2008, 12:37pm)

Title: SQL Injection Question
Post by Pete Finnigan on Apr 8^th, 2008, 12:37pm

Hi,

Is this code secure from SQL injection? If not, how would I exploit it and what would be a better (more secure) way of coding it?

Thanks

Code:

...
FOR row IN (
SELECT EMPNO, ENAME
FROM SCOTT.DEPT
WHERE ENAME LIKE '%'||pName||'%'
) LOOP
...

Title: Re: SQL Injection Question
Post by Pete Finnigan on Apr 10^th, 2008, 12:37am

Assuming pName is a character variable there is no injection vulnerability here as the expression is a simple concatenation of literal values and variable value. There's no opportunity for any unexpected code to be executed.
I'd guess that the p prefix signifies a parameter. If so, I might look at either assigning it to a local variable with a defined length or at least validating its length. If it was an insert or update, I'd definately do some validation on the input value (length, non-printing characters etc).
For a LIKE check, you may (depending on requirements) check whether the parameter string already contains wildcard characters. If the end user isn't supposed to supply wildcards, then "instr(ename,pName) > 0 " may be a better filter.

Finally, if "eName" is a valid column name, I'd be concerned about allowing a variable called pName. If a column "pName" was added to the table, then the SQL would compare the two column values, not one column value against the value of the PL/SQL variable and so have unexpected side-effects.
It is best to have strict naming standards so that variable names and column names are easily distinguishable.

Title: Re: SQL Injection Question
Post by Pete Finnigan on Apr 10^th, 2008, 3:15pm

Hi,

If the input for pNAME is : '' --
the that would be resolved to: '%' ||'' --||'%'
So all the content of the table will be displayed.
Some other thing to try would be try to call a function like utl_http maybe?

regards,

Ivan

Title: Re: SQL Injection Question
Post by Pete Finnigan on Apr 10^th, 2008, 3:43pm

Hi Guys,

Thank you both for your responses.

Gamyers...
You are indeed correct in assuming that the p indicates a parameter, we're checking that we've copied the all the values into local variables now. And we are usually pretty careful with our column names (certainly more-so than SCOTT)

Isaez...
I still can't reproduce a succesful attack using your string...:

Code:

--
-- Prove that the code works
--
SCOTT @ test >
SCOTT @ test > SET SERVEROUTPUT ON
SCOTT @ test >
SCOTT @ test > accept Name prompt "name "
name R
SCOTT @ test > DECLARE
2 lName VARCHAR2(20) := '&Name';
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('lName: '||lName);
5
6 FOR user IN (SELECT ENAME, SAL
7 FROM EMP
8 WHERE ENAME LIKE '%'||lName||'%') LOOP
9 DBMS_OUTPUT.PUT_LINE(user.ename||', '||user.sal);
10 END LOOP;
11 END;
12 /
old 2: lName VARCHAR2(20) := '&Name';
new 2: lName VARCHAR2(20) := 'R';
lName: R
WARD, 1250
MARTIN, 1250
CLARK, 2450
TURNER, 1500
FORD, 3000
MILLER, 1300

PL/SQL procedure successfully completed.

--
-- Now try SQL injection
--
SCOTT @ test > SET SERVEROUTPUT ON
SCOTT @ test >
SCOTT @ test > accept Name prompt "name "
name '''' --
SCOTT @ test > DECLARE
2 lName VARCHAR2(20) := '&Name';
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('lName: '||lName);
5
6 FOR user IN (SELECT ENAME, SAL
7 FROM EMP
8 WHERE ENAME LIKE '%'||lName||'%') LOOP
9 DBMS_OUTPUT.PUT_LINE(user.ename||', '||user.sal);
10 END LOOP;
11 END;
12 /
old 2: lName VARCHAR2(20) := '&Name';
new 2: lName VARCHAR2(20) := ''''' --';
lName: '' --

PL/SQL procedure successfully completed.

.... is it possible that you are mistaken? Or are you able to craft an exploit string for the code above?

:-)

RT

Title: Re: SQL Injection Question
Post by Pete Finnigan on Apr 10^th, 2008, 6:16pm

RT,

You are right ''-- as input doesn't work but if you give an empty string then all data will be shown. Search in google for: oracel sql injection "like clause" and you will see possible attacks.
Also take a look at bind variables!

regards,

Ivan