One of the main Reason using Oracle PL/SQL procedures for controlling data access, One of the main reasons is insecure coding practices. One of the widely used attack techniques on applications is SQL injection. I write before about SQL injection but since it’s big topic and need to be covered in more than one parts.
as reminder what is the SQL Injection : One of Hacking way to manipulate the SQL statements using web applications for access/query database. While run Web Application, the programmer may directly use the user input without hide or even any validation. This opens a new way for the attacker to access and retrieve data . By sending specially crafted user input.
You need to know that Any dynamic SQL query using invalidated user inputs are vulnerable to SQL injection. Some methods that developers use to prevent SQL injection are parameterized queries or stored procedures
the parameterized queries approach is the most secure way against SQL injection than the traditional approach of joining string to build a dynamic SQL string, in the second type usually leads to data format problems, you have to worry about how to encode the parameter and you need each company have it’s own way to do that :
Query_sql = “SELECT * FROM emp where emp_id = :emp_Id”;
A stored procedure is a database object just like table, Group of SQL statement that form a logical unit and perform a particular task to execute it you need to call it using Procedure name mostly is used as container for the code but the question is if i use them in my Code is this make me secure against SQL Injection ?
Answer also is simple Not always because if code not set properly then SQL Injection could be happened again.
CREATE OR REPLACE PROCEDURE Test (Param_1 IN VARCHAR2) AS
sql VARCHAR;
code VARCHAR;
BEGIN
Sql := ‘SELECT emp_id, Emp_Name, Job, Sal WHERE’ +
‘ Emp_Name=”’ || Param_1 || ””;
EXECUTE IMMEDIATE sql INTO code;
END;
the Value Param_1 will taken from user input concatenated with the string,The user input is enclosed in the single quotes and concatenated to a string to form SQL query.so the problem is related to this Instead of the parameter being a search string to the SQL query, the user input has become the part of the query as it is enclosed inside the single quotes. If the user enters the values as 1' or '1'='1'.
Then This Stored Procedure is Not secure as we think.
So How to Write Secure Procedure ?
One Answer : Test Your Code and You should know what you write, If you query working fine that not mean its secure and no hacker will get in.
Validate inputs that comes from users, also like i mention before Use parametrized stored procedure with embedded parameters, don’t forget to Use a low privileged users and give right and correct role/Privileges to application users and finally avoid use of dynamic SQL queriesif you have another way.
so as conclusion is if you are using Stored procedure correctly then you are pretty much safe from SQL Injection and always remember when you attend to do this don’t use :
1 – Dynamic SQL inside the Stored procedure.
2 – try to avoid concatenated string.
Thank you
Osama Mustafa
Technology Geek 