SQL injection is a very serious topic and as a matter of fact, it is very easy to prevent. Though, it is overlooked so many times by all developers either due to the lack of knowledge or laziness to implement basic security features in their applications.
What is SQL Injection
Every time you allow someone from outside of your query to input parameters, there is a potential risk of your query being injected. SQL injection does not have to be necessarily intentional but in most cases it is. It is a piece of malicious code inserted into the original unsecured query in order to unauthorizedly retrieve more (generally sensitive) information or to modify data inside the database.
How to inject the code
The only way how users can inject their codes into your query is when you concatenate the query instead of bind it. Let’s check out the example of a few samples of codes to demonstrate how NOT to do it.
SELECT query injection:
DECLARE v_prod_name product.name%TYPE; BEGIN v_query := 'SELECT name FROM product WHERE type = '''||p_value||''''; EXECUTE IMMEDIATE v_query INTO v_prod_name; END;
Cursor injection:
CREATE OR REPLACE PROCEDURE p_vulnerable_proc (p_value VARCHAR2) IS
-- global variables
v_query VARCHAR2(500);
c_emp SYS_REFCURSOR;
-- fetched data
TYPE emps IS RECORD (
p_emp_id VARCHAR2(50)
, p_emp_dpt VARCHAR2(255)
, p_emp_mail VARCHAR2(100)
);
TYPE employee_tt IS TABLE OF emps;
l_employees employee_tt;
BEGIN
v_query := 'SELECT id_employee, department_name, email_address
FROM employee
WHERE name_last = '''||p_value||'''
';
dbms_output.put_line(v_query);
OPEN c_emp FOR v_query;
FETCH c_emp BULK COLLECT INTO l_employees;
CLOSE c_emp;
FOR emp IN 1 .. l_employees.count
LOOP dbms_output.put_line(l_employees(emp).p_emp_id||';'||
l_employees(emp).p_emp_dpt||';'||
l_employees(emp).p_emp_mail );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/*
let's check out the correct value and output vs the injection and its output
*/
-- correct input
DECLARE
v_corrent_input VARCHAR2(50) := 'Doe';
BEGIN
p_vulnerable_proc(p_value => v_corrent_input );
END;
-- output
1;John Doe;john.doe@oracleworld.com
-- injected code
DECLARE
v_injected_input VARCHAR2(50) := 'Doe'' OR ''1''=''1';
BEGIN
p_vulnerable_proc(p_value => v_corrent_input );
END;
-- output (practically all records from the table)
1;John Doe;john.doe@oracleworld.com
2;Marry Smith;marry.smith@oracleworld.com
3;Jimmy Pajamas;jimmy.pajamas@oracleworld.com
4;Bob Desert;bob.desert@oracleworld.com
As you can see the value is directly inserted/concatenated into the query. The “injected” query looks like:
SELECT id_employee, department_name, email_address FROM employee WHERE name_last = 'Doe' OR '1'='1'
Which means it will return all records where name_last = ‘Doe’ or 1=1 (that is TRUE for all records in the database). This is just a simple example of how users can modify your code very easily; if you allow them.
How to prevent the code injection
There are two certain ways how to prevent SQL injections.
- BIND variables
- I highly recommend sticking with this one (not only from the security perspective but also from the performance perspective)
- Read more about them in my article called Bind variables
- DBMS_ASSERT package
- this package allows you to validate input values compared to a certain criteria
BIND variables
Let’s see how the procedure will look like rewritten to use bind variables.
CREATE OR REPLACE PROCEDURE p_vulnerable_proc (p_value VARCHAR2) IS
-- global variables
v_query VARCHAR2(500);
v_limit NUMBER := 5;
c_emp SYS_REFCURSOR;
-- fetched data
TYPE emps IS RECORD (
p_emp_id VARCHAR2(50)
, p_emp_dpt VARCHAR2(255)
, p_emp_mail VARCHAR2(100)
);
TYPE employee_tt IS TABLE OF emps;
l_employees employee_tt;
BEGIN
v_query := 'SELECT id_employee, department_name, email_address
FROM employee
WHERE name_last = :1'; /* BIND VARIABLE !!! */
dbms_output.put_line(v_query);
OPEN c_emp FOR v_query USING p_value; /* HERE IS THE TRICK */
FETCH c_emp BULK COLLECT INTO l_employees LIMIT v_limit;
CLOSE c_emp;
FOR emp IN 1 .. l_employees.count
LOOP
dbms_output.put_line(l_employees(emp).p_emp_id||';'||
l_employees(emp).p_emp_dpt||';'||
l_employees(emp).p_emp_mail );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/*
let's check out the correct value and output vs the injection and its output
*/
-- correct input
DECLARE
v_corrent_input VARCHAR2(50) := 'Doe';
BEGIN
p_vulnerable_proc(p_value => v_corrent_input );
END;
-- output
1;John Doe;john.doe@oracleworld.com
-- injected code
DECLARE
v_injected_input VARCHAR2(50) := 'Doe'' OR ''1''=''1';
BEGIN
p_vulnerable_proc(p_value => v_corrent_input );
END;
-- output is empty
DBMS_ASSERT package
This package is provided by default by Oracle and might come in handy in many times 🙂 Let’s see what is the name of the game here:
- ENQUOTE_LITERAL
- accepts one parameter only => string
- adds a single quote to the beginning and to the end of the string
- makes sure that all single quotes inside of the string are correctly to paired together
- ENQUOTE_NAME
- accepts two parameters
- string
- capitalize – if TRUE (default) all characters are translated to upper case
- encloses a SQL name in double quotes
- accepts two parameters
- NOOP
- does not do any checkings (stands for NO OPeration)
- returns the string as is
- QUALIFIED_SQL_NAME
- qualifies whether the string is a fully qualified SQL name or not
- if it’s not, it will prompt with ORA-44004 error
- SCHEMA_NAME
- checks whether the schema exists in a database
- if it’s not there, it will prompt with ORA-44001 error
- SIMPLE_SQL_NAME
- validates the syntax of the SQL query whether the statement contains only valid characters and has a proper quoting
- checks include:
- the first character must be alphabetic [a-Z]
- it must contain only alphanumeric characters, underscore (_), dollar sign ($), and the number sign (#) as a second and subsequent character
- quoted SQL names are allowed but must be enclosed with double quotes
- leading and trailing white space characters are not validated
- length of the string is not validated
- if it’s not validated, it will prompt with ORA-44003
- SQL_OBJECT_NAME
- checks whether the given parameter is a correct database object or not.
- if it’s not validated, it will prompt with ORA-44002
Sample usage:
SELECT dbms_assert.sql_object_name('hr.employee') as valid_name FROM dual;
valid_name
------------------
hr.employee
SELECT dbms_assert.sql_object_name('hr.employees') /*extra S at the end*/ as valid_name FROM dual;
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316