Looking for some advice on how to make PL/SQL and SQL code SQL Injection proof.
I have some procs where 10 screen input fields are passed in to the proc as parameters and about 5 of them are varchar2 data type.
All screen fields are passed into the proc whether they are left NULL or are populated by the user.
In the proc, we check which parameters are passed in as NOT NULL and then concatenate strings of them to make a WHERE clause to include all of the NOT NULL parameter values that are passed in.
So we could have only 1 or 2 conditions in the final WHERE clause or up 10 conditions in a large query.
I have seen advice to use bind variables to avoid SQL injection threats, but how does one maintain the dynamic characteristic where we don't know the number of sub clauses in the final WHERE clause until after parameters are passed into the proc?
Any help would be much appreciated.