Home

Rule description

• Database queries should not be vulnerable to injection attacks

Non-compliant Code Example

string query = "DELETE FROM employee WHERE emp_id = '" + Emp_id_var + "'" ; //Non compliant code (Possible sql injection)
EXECUTE IMMEDIATE: query;

string query = "INSERT INTO department VALUES ('" + Dept_id_var + "', '" + Dept_name_var + "', '" + Mgr_id_var + "')" ; //Non compliant code (Possible sql injection)
EXECUTE IMMEDIATE :query

string query = "Update employee set departmentId = '" + Dept_id_var + "' where  emp_id = '" + Emp_id_var + "'" ; //Non compliant code (Possible sql injection)
EXECUTE IMMEDIATE :query

string query = "SELECT emp_id FROM employee "&
+"WHERE state = '" + Emp_state_var + "'" //Non compliant code (Possible sql injection)
EXECUTE IMMEDIATE :query

string query = "execute emp_select @stateparm='" + Emp_state_var + "'" ; //Non compliant code (Possible sql injection)
EXECUTE IMMEDIATE :query;

string query = "execute @rc = myproc @parm1='" + var1 + "', @parm2='" + var2 + "' OUTPUT "; //Non compliant code (Possible sql injection)
EXECUTE IMMEDIATE :query;

Compliant Code Example

INT Emp_id_var = 56

PREPARE SQLSA
FROM "DELETE FROM employee WHERE emp_id =? " ;//Compliant code
EXECUTE SQLSA USING :Emp_id_var;

INT Dept_id_var = 156

INT Mgr_id_var
String Dept_name_var
Dept_name_var = "Department"
SetNull(Mgr_id_var)

PREPARE SQLSA
FROM "INSERT INTO department VALUES (?,?,?)" ;//Compliant code
EXECUTE SQLSA
USING :Dept_id_var, :Dept_name_var, :Mgr_id_var; 

INT Emp_id_var = 56
INT Dept_id_var = 2

PREPARE SQLSA
FROM "Update employee set departmentId=? where  emp_id=?" ;//Compliant code
EXECUTE SQLSA USING :Dept_id_var, :Emp_id_var;

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;

integer Emp_id_var

string Emp_state_var = "MA"
string sqlstatement
sqlstatement = "SELECT emp_id FROM employee " &
+"WHERE state = ?"//Compliant code
PREPARE SQLSA FROM :sqlstatement ;
OPEN DYNAMIC my_cursor using :Emp_state_var;
FETCH my_cursor INTO :Emp_id_var;
CLOSE my_cursor;

DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ;
integer Emp_id_var
string Emp_state_var
PREPARE SQLSA FROM "execute emp_select @stateparm =? " ;//Compliant code
Emp_state_var = "MA"
EXECUTE DYNAMIC my_proc USING :Emp_state_var ;
FETCH my_proc INTO :Emp_id_var;
CLOSE my_proc; 

string var1,var2
PREPARE SQLSA FROM "execute @rc = myproc @parm1 =?, @parm2 =? OUTPUT ";//Compliant code
                                
DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ;
EXECUTE DYNAMIC my_proc USING :var1, :var2;
//fetch result set
//...
//fetch return value and output parameter
FETCH my_proc INTO :var1, :var2;
CLOSE my_proc;