Thursday, April 21, 2011

Danger of explicit conversions

At the last conference (Collab'11) I've had a discussion with one of the speakers about the danger of explicit conversions in Oracle (like TO_CHAR(SYSDATE) without a format mask or direct concatenations). Strange enough, from his experience still too many people are trying to save a couple of extra key-strokes - and eventually get themselves into major troubles.

IMHO, in the environment where the process of code review is reasonably common, any code with such conversions should not pass evaluation at all! There is a number of good reasons (and here I could've typed 2-3 pages)... My favorite one is that explicit conversions open an additional hole for SQL injections. Here is a sample:
SQL> alter session set  NLS_Date_Format = '"'''' or 1=1--';
Session altered.
SQL> declare
  2      v_dt date:=sysdate;
  3      v_count_nr number;
  4
  5      v_sql_tx varchar2(4000);
  6  begin
  7      v_sql_tx:='select count(*) from scott.emp where hiredate>'||v_dt;
  8
  9      dbms_output.put_line('Query:'||v_sql_tx);
 10      execute immediate v_sql_tx into v_count_nr;
 11
 12      dbms_output.put_line('Count:'||v_count_nr);
 13  end;
 14  /
Query:select count(*) from scott.emp where hiredate>'' or 1=1--
Count:14
PL/SQL procedure successfully completed.
SQL>

You may notice a very strange date format - and that's exactly how the SQL injection happens! Concatenation of a DATE attribute (line 7) to a string under the hood fires TO_CHAR(V_DT) with the default format mask that was just overridden by ALTER SESSION. As a result, the query shows the total count of rows in the EMP table (14) when it should return 0! 

Good enough reason? I would think so.

No comments: