There are profound content scattered all over the internet which talks about proofing sql injection with bind variable as an option to safeguard the dynamic queries but is bind variables really effective let’s go find out.
I will be using a table cutomer_Acess which contains user_name and password for say any web user of an Application.
Create table customers_Acess(user_name varchar2(50), Password varchar2(100));
Insert some users and passwords quickly
Declare
cursor c1 is
Select FIRST_NAME from Employees;
BEGIN
FOR i in 1 .. 100 LOOP
FOR z in c1 loop
INSERT /*+APPEND*/
INTO customers_Acess
(User_name, Password)
VALUES
(z.first_name, dbms_random.string('A', 10));
END LOOP;
END LOOP;
Commit;
END;
--------------------------------------------------------------
--A good practice to gather statistics after bulk DML
BEGIN
dbms_stats.Gather_table_stats('HR', 'customers_Acess',
method_opt => 'for all columns size skewonly');
--------------------------------------------------------------
coming back to sqlinjection, lets say your manager assigns you write function to validate the password for the users which would be called by that Application, If password is not valid, you would return nothing and application will prompt error to the user.
create or replace function is_password_correct(p_username in varchar2,
p_password in varchar2)
return sys_refcursor IS
l_stmt varchar2(4000);
l_sys sys_refcursor;
begin
l_stmt := 'select user_name,password, ''correct'' from customers_Acess ' ||
'where user_name = ''' || p_username || ''' and password = ''' ||
p_password || '''';
dbms_output.put_line('l_stmt=' || l_stmt);
Begin
OPEN l_sys for l_stmt;
return l_sys;
Exception
When NO_DATA_FOUND THEN
return null;
END;
end;
--------------------------------------------------------------
select is_password_correct('Winston','zSTXpssEwr') from dual;
You ended up writing a dynamic query which the front end developer confirmed is working fine and you end up your day with some time to spare on your favorite game in the office. we are missing those a lot in this pandemic.
You barely thought about the sql injection in your query and very soon you get to know the data has been leaked.
How that hell happened, Lets figure out. this small piece of code can be passed as a parameter and get the details of the user.
select is_password_correct('Peter','wrongpass'' or ''1''=''1') from dual;Now that we know a plain a plain dynamic query can be a potential risk, lets come back to from where started to probe in the " bind variables" and let me rewrite the above code using bind variables.
create or replace function is_password_correct(p_username in varchar2,
p_password in varchar2)
return sys_refcursor IS
l_stmt varchar2(4000);
l_sys sys_refcursor;
begin
l_stmt := 'select user_name,password, ''correct'' from customers_Acess ' ||
'where user_name = ''' || p_username || ''' and password = :l_password';
dbms_output.put_line('l_stmt=' || l_stmt);
Begin
OPEN l_sys for l_stmt using p_password;
return l_sys;
Exception
When NO_DATA_FOUND THEN
return null;
END;
end;
------------------------------------------------------------------
Try running the same injection query(select is_password_correct('Peter','wrongpass'' or ''1''=''1') from dual;)
it wont return values and now our code is safeguarded
lets now move a bit ahead as bind variables requires an extensive change and some people contest that its not full proof.
We have another way to safeguard our code through dbms_assert which is very easy to use and very useful to proof the sql injection
Lets take a glance:
create or replace function is_password_correct(p_username in varchar2,
p_password in varchar2)
return sys_refcursor IS
l_stmt varchar2(4000);
l_sanitized_password varchar2(100);
l_sys sys_refcursor;
begin
l_sanitized_password := sys.dbms_assert.enquote_literal(p_password);
l_stmt := 'select user_name,password, ''correct'' from customers_Acess ' ||
'where user_name = ''' || p_username ||
''' and password = ' || l_sanitized_password || '';
dbms_output.put_line('l_stmt=' || l_stmt);
Exception
When OTHERS THEN
raise_application_error(-20001, 'Possible SQL Injection Attack');
Begin
OPEN l_sys for l_stmt;
return l_sys;
Exception
When NO_DATA_FOUND THEN
return null;
END;
end;Try injecting
select is_password_correct('Peter','wrongpass'' or ''1''=''1') from dual;
It won't let you and would error out.
This a just one way I have shown dbms_assert can be used in multiple ways like
enquote_literal: Puts quotes around a literal value in SQL.
enquote_name: Puts quotes around a SQL name.
noop: Stands for "NO Operation", and it does not perform error checking.
qualified_sql_name: allows a fully-qualified SQL (e.g. scott.emp@ny).
schema_name: Allows you to validate a schema name against the dictionary.
simple_sql_name: Validates the syntax of the SQL to ensure that the SQL statement only contains valid characters and proper quotes around internal values.
sql_object_name: Ensures that the SQL is referencing a valid data dictionary object name
I will come up with those in different post.
Comments