techblog 

It is not unusual to be in a situation where a routine in PL/SQL takes a long series of arguments to use as parameters in a query. For example, suppose we have the following function:

FUNCTION Find_People_By_Address(street_address_in VARCHAR2,
                                locality_in VARCHAR2,
                                city_in VARCHAR2,
                                province_in VARCHAR2,
                                country_in VARCHAR2,
                                postcode_in VARCHAR2) RETURN PERSON_CURSOR;

We pass in parameters and expect to receive back a cursor which will give us people whose address matches those parameters. If we pass NULL, it means that we don’t want to consider that field. One way to do this is to open our cursor with a SQL statement that that looks something like this

SELECT ID, NAME
FROM PEOPLE_TABLE
WHERE (street_address_in IS NULL OR STREET_ADDRESS = street_address_in)
AND (locality_in IS NULL OR LOCALITY = locality_in)
AND (city_in IS NULL OR CITY = city_in)
...

This is fine for the simplest cases, but sometimes the optimizer gets a little confused because you have mentioned every column in the table. It is also a little hard to read and this becomes more and more of a problem as the query gets complicated. It would be better if passing in NULL for everything except postcode produced a query like this

SELECT ID, NAME
FROM PEOPLE_TABLE
WHERE POSTCODE = postcode_in

It is obvious to the optimizer what to do. Assuming postcode is indexed, the query will perform well. The logical way to do this is to build the SQL dynamically, but this can also open the door for the dreaded SQL Injection Attack. This happens if you use concatenation to add the arguments into the query like this

IF city_in IS NOT NULL THEN
   query := query || ' AND CITY = ''' || city_in || '''';
END IF;

Obviously, this is bad practice. So how do we get around this? The answer is of course bind variables, which Oracle supports for dynamic SQL. But it isn’t obvious how we use them here. Surely the number of bind variables we need will depend on the number of non-NULL arguments passed to our routine? Yes and no. You can work around this by keeping the number of bind variables fixed with one per argument, but by effectively throwing away the ones you are not interested in. You build the SQL like this:

query := 'SELECT ID, NAME FROM PEOPLE TABLE WHERE 1=1';

IF street_address IS NOT NULL THEN
   query := query || ' AND STREET_ADDRESS = :1';
ELSE
   query := query || ' AND (1=1 OR :1 IS NULL)';
END IF;

......

and then open the cursor like so

OPEN result_cursor
FOR query
USING street_address_in, locality_in, city_in, province_in, country_in, postcode_in;

So our query will end up with six bind variables in it every time, but the ones which are NULL will effectively be thrown away. Our query may not end up as readable as the postcode example given above, but it should be as fast and doesn’t have a vulnerability to SQL Injection.