Databases Reference
In-Depth Information
create or replace procedure findUsers(pEMail in varchar2) as
TYPE NameList IS TABLE OF VARCHAR2(256);
UserNameList NameList;
begin
execute immediate 'select username from my users where email = ''' || pEMail || ''''
bulk collect into UserNameList;
for i in 1..UserNameList.count loop
-- htp.p is equivalent to dbms output.put line
-- htp.p output can be viewed in a web browser
-- and in SQL Developer by enabling OWA Output
htp.p(UserNameList(i));
end loop;
end;
Both the pseudo code and the PL/SQL dynamically build a SQL statement, execute that statement,
store the results and print them out. Given an input of
pEMail = john.doe@mycompany.com
, the results are
as expected (see Figure 8-15).
Figure 8-15.
Output of findUsers
The problem arises when a user inputs an unexpected string, for example
execute
findUsers(pEMail => 'abc'' union select table name username from all tables --');.
This input provides a very different result (Figure 8-16).
