Solutions to limit SQL injection

In the previous section, you learned about SQL injection creation. We showed what it can cause and what the consequences are. This section will drive you through the available solutions by introducing bind variables and explicit conversions. We will also discuss the DBMS_ASSERT package, which allows you to sanitize input values.

Using bind variables

Bind variables provide the relevant solution to limit SQL injection. Instead of concatenating the character strings forming the statement, the input value is applied using a bind variable, as shown in the following code block, which defines a GET_EMPLOYEES procedure. The condition in the where clause is related to the Date_from attribute and procedure parameter (p_date). This is not concatenated, but rather treated as a bind variable:

create or replace procedure GET_EMPLOYEES(p_date DATE)
is
 v_statement varchar2(10000);
 v_cursor sys_refcursor;
 v_ns varchar(100);
 v_pid varchar(11);
begin
 v_statement...