Vulnerability

Description

This PowerBuilder code rule states that database queries should be constructed in such a way that they are not vulnerable to injection attacks. An injection attack is a type of malicious attack in which malicious code is inserted into a query in order to gain access to or manipulate data. To prevent such attacks, code should be written to ensure that user input is sanitized and that queries are constructed properly so that malicious code is not able to be injected into them.

Key Benefits

• Prevent Unauthorized Access: By preventing injection attacks, databases queries can help prevent unauthorized access to sensitive data.
• Ensure Data Integrity: By avoiding injection attacks, databases queries can help ensure data integrity.
• Maintain Security: By preventing injection attacks, databases queries can help maintain the security of the system.
• Reduce Risk: By avoiding injection attacks, databases queries can help reduce the risk of data loss or theft.

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;