OracleSQL注入攻击

There is an implicit to_char on the date field in order to concatenate it to the query string.  Then, at runtime there is an implicit to_date on the string we concatenated in so we can compare it to a date.  This is a very common thing I see in code all of the time (implicit conversions) - but it is pure evil.  Not only will we discover it is the cause of a SQL Injection issue - but here it is a logic bomb as well.

First of all - by default - that to_date( to_char() ) conversion will have a side effect of effectively truncating the time component from the date field.  That is evil.  If you wanted to truncate the time off - please use TRUNC() on the date - it is much faster, more efficient, and expresses clearly that you intend to truncate the time component.  To_date(to_char()) does none of that.  Secondly - the conversion by default will also lose the century.  If you were trying to look for things created during the war of 1812 - you would lose, you cannot search for 1812 - it would become 2012 (well, right now as I write this it would be 2012 - in 38 years it will become 2112 and you won&＃39;t be able to search for 2012 anymore...).

Also consider that I said "by default".  By default the NLS_DATE_FORMAT is DD-MON-RR (currrently, it has been different in the past!).  What happens to this code when someone decides to change it?  Your application might well start querying up entirely different data!

So, the implicit conversion by itself is bad - but the real issue is the SQL Injection flaw.  If you just run this procedure, by default - it certainly looks OK:

that looks OK - seems pretty safe - until, until someone who has read the documentation comes along.  They might run your code like this:

Now that is surprising,  you might not even know you could do that in an NLS_DATE_FORMAT.  It is really hard to protect against something you don&＃39;t even know you can do - isn&＃39;t it?  I&＃39;ve had people look at that example and scoff at it - saying "so what, they were allowed to see that table".  Ok, take it a step further, I&＃39;d like to know what tables you own - so I can start querying them.  I&＃39;ll just do this:

....

现在你们看到问题的进展。。。在一个存储过程我发现了SQL注入BUG并解锁了整个模式&＃xff01;

Now you can see where this is going...  I find one SQL Injection bug in one procedure and I&＃39;ve unlocked the entire schema.  

问题来了--我们改如何防护&＃xff1f; 做些什么不再受制于SQL注入&＃xff1f;

So, the question now comes up - how do I protect myself from this?  What can I do to ensure I&＃39;m not subject to SQL Injection in this code?

有2种方式--困难的方式和简单方式

There are two ways - the hard way and the easy way.  

困难的方式&＃xff1a;是引入SQL验证代码

The hard way involves writing code to validate everything and having serious code reviews of any code that uses string concatenation to build their SQL statements - any code that takes a parameter as input and concatenates it to a SQL query must be read and reviewed by many people - many people who will be super critical of the code.  In this case, the resulting code would have to be:

where created &＃61; to_date( &＃39;&＃39;&＃39; || to_char(p_date,&＃39;yyyymmddhh24miss&＃39;) ||&＃39;&＃39;&＃39;, &＃39;&＃39;yyyymmddhh24miss&＃39;&＃39;)&＃39;;   

You need to have a coding standard that says:

1. You shall never use implicit conversions ever, as in never.
2. You shall always use an explicit date mask with dates, as in every single time, you will not rely on defaults (because defaults can inject you and because defaults can radically modify your logic unintentionally!)

简单的方式&＃xff1a;使用绑定变量

The easy way however is the way to go.  The easy way is - just use bind variables!  If you use bind variables, you cannot be SQL Injected - this is true for PL/SQL, for Java, for any and all languages.  If you use bind variables you cannot be SQL Injected - period.  It is that simple, really and truly.  If the code was:

there is no way the end user can trick that SQL query into becoming anything other than what it is - in fact, for this example, the code should have been:

as

   cursor c is select * from all_users where created &＃61; p_date;

begin

   open c;

   ...

and nothing more - it shouldn&＃39;t have even been using dynamic SQL.  In Java/C#/C&＃43;&＃43;/etc - you would be using dynamic SQL and you should be using bind variables.   So, that answered all of these questions I received:

Another question was:

• should application layer deal with the SQL injection attacks prevention as that layer understands what the proper data access patterns look like rather than database?

My response to that is - the application layer should definitely be aware of SQL Injection and use secure coding practices which would include:

However - we need to also employ defense in depth - for when the inevitable bug slips through.  When I next write about this - I&＃39;ll be going over the Oracle Database Firewall - a tool that can provide at least one more layer of defense.

The last question on this topic was: 

• What is the dbms_assert PL/SQL package? How does it help prevent SQL injection? Should my organization be using it?

For that - I&＃39;ll just forward you onto an excellent paper on this subject written by Bryn Llewellyn.  You can find that paper here.