Avoiding SQL Injections¶
SQL injection is a code injection technique where malicious SQL statements are inserted possibly by an end user in a normal entry field, which may lead to by-pass of security or corruption of data. This is most likely to occur in places where Dynamic SQL is used. For example when the logic calls EXECUTE IMMEDIATE on a SQL Statement and the execution string is built up not using proper bind-variables.
sqlStmt:= 'DELETE FROM user_account WHERE user_id = ''' || user_id_arg_ || '''';
The user can enter a value of the user_id_arg_ parameter that modifies the where clause of the SQL that could end up in a statement executed like this:
DELETE FROM user_account WHERE user_id = '' OR '1'='1';
Dynamic SQL is not recommended to use, instead Conditional Compilation should be considered. Note that the statements in the examples given below are normally not constructed using Dynamic SQL.
If Dynamic SQL is used there are two things to think about to avoid the possibility of SQL injections:
- Use Bind Variables instead of concatenate strings
- Use validation checks on input data used in the dynamic code statement
Use Bind Variables¶
By using bind variables exclusively in your code, you avoid concatenating SQL statements and thereby prevent malicious users from altering or injecting additional statements. Oracle database uses the value of the bind variable exclusively and does not interpret its contents in any way. This technique is the most effective way to prevent SQL injection in PL/SQL programs.
sqlStmt:= 'DELETE FROM user_account WHERE user_id_ = :user_id_arg_'; EXECUTE IMMEDIATE sqlStmt USING user_id_arg_;
Use Validation Checks¶
All data that a user can possibly enter should of course be validated. But it is of extra concern when the input data is used inside a dynamic built-up SQL statement. For example if the input data is supposed to be an Application User, then a check should be done before executing the SQL statement that the input data really is a user.
The Assert_Sys package have some validation functions that can be used for this purpose. For example: Assert_Is_Sysdate_Expression, Assert_Is_View, Assert_Is_Number etc.
Assert_Sys.Assert_Is_User(user_id_arg_); sqlStmt:= 'DELETE FROM user_account WHERE user_id_ = :user_id_arg_; EXECUTE IMMEDIATE sqlStmt USING user_id_arg_;
Approval of Dynamic Code¶
All execution of statements using constructions like
EXECUTE IMMEDIATE require that they are reviewed and approved. Approval of such code construction is marked in the code with the annotation Approve Dynamic Statement .