We had a security scan on our code base and the following code was flagged as a potential SQL Injection risk. I was under the impression that if I use parameterized queries that Oracle treats them similar to bind variables, and that this is a safe way to write our queries.
function get_school_id (
p_username varchar2
) return number is
l_school_id number;
begin
select school_id
into l_school_id
from tbl_schools
where username = p_username;
return l_school_id;
end;
My assumption is that even if someone where to get access to this function and do:
p_username => '; delete from tbl_schools;'
It would return no rows, since it would take that string and try to find that as a 'username' in the tbl_schools table.
- Is this a sql injection risk? If so, what is the best way to mitigate the above SQL?
- Do parameter variables behave similar to bind variables in PL/SQL, in this regard, like I assumed? If so, is there a link someone can provide that I can show the security team? I am searching the PL/SQL docs and not finding anything, but possible I do not know the correct keyword(s).