Home

Database queries should not be vulnerable to injection attacks

Rule description

  • Code Injection is an attack injecting malicious code that will be interpreted/executed by the application. In particular, SQL code injection allows performing illegal operations in your database (access to sensitive data, server takeover or shutdown…).
  • By nature, PowerBuilder applications are database-intensive and mission critical.
  • They handle large volumes of important data, which make them premium targets for hackers.
  • For instance, Visual Expert will search for string concatenations, used for building SQLs, and are not properly validated or escaped. They may create major loopholes for the SQL injection. Identifying and refactoring such queries will strengthen your database protection.
  • Learn more about How to Protect PowerBuilder Apps against Code 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; 
Visual Expert 2020
 VEPBRULE7