Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I see code from developers using implicit date conversion. I would like a definitive answer to why they should not do this.

SELECT * from dba_objects WHERE Created >= '06-MAR-2012';
share|improve this question
1  
Do you have some examples? – FrustratedWithFormsDesigner Mar 6 '12 at 21:39
1  
@Frustrated Added an example. – Leigh Riffel Mar 6 '12 at 21:45
    
I would even go so far that you should not use abbreviated month names either unless NLS_DATE_LANGUAGE parameter is used as well in a to_date() call – a_horse_with_no_name Mar 7 '12 at 8:38
up vote 15 down vote accepted

Because '2012/12/1' in the US is 11 months after the same string date in Europe.

Allowing implicit conversions means you are at the mercy of location settings.

If you can name a business where 11 months is an acceptable margin of error I'll be impressed.

share|improve this answer
6  
Indeed, '01/01/11' could be 10 years off. +1 – Leigh Riffel Mar 6 '12 at 21:46
    
@LeighRiffel: Or even 110 years off... – ypercubeᵀᴹ Mar 6 '12 at 23:12
    
+1, one should never rely on implicit DATE conversion (or any implicit data type casting that is) – a_horse_with_no_name Mar 7 '12 at 8:30
2  
"name a business where 11 months is an acceptable margin of error" -- genealogy engines tend to allow two years either side of the input year when searching registers. – onedaywhen Mar 7 '12 at 11:27
1  
@onedaywhen good answer - as stated, I'm impressed! – JNK Mar 7 '12 at 13:18

There are problems that will occur if a session with a different date format runs the code.

Statement Failure

DROP TABLE t1;
CREATE TABLE t1 AS (SELECT sysdate mydate FROM dual WHERE 1=2);
ALTER SESSION SET NLS_DATE_FORMAT = 'MON-DD-RR';
INSERT INTO t1 VALUES ('01-02-12');
                       *
ERROR at line 1:
ORA-01843: not a valid month

Bad Data

  DROP TABLE t1;
  CREATE TABLE t1 AS (SELECT sysdate mydate FROM dual WHERE 1=2);

  --User 1
  ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-RR';
  INSERT INTO t1 VALUES ('01-02-11');

  --User 2
  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR';
  INSERT INTO t1 VALUES ('01-02-11');

  --User 3
  ALTER SESSION SET NLS_DATE_FORMAT = 'RR-MM-DD';
  INSERT INTO t1 VALUES ('01-02-11');

  SELECT to_char(mydate,'MM/DD/YYYY') FROM t1;

In this situation because each of the alter/insert statements could be done by different users. They would all be running the same statements, but the resulting dates would be completely different. The insert statements might be buried in a package that is only indirectly being called. Because no error was returned the problem might not be found until much later.

SQL Injection

  CLEAR SCREEN;
  DROP TABLE Secrets;
  CREATE TABLE Secrets (RevealDate Date, Secret Varchar2(200));
  INSERT INTO Secrets VALUES (trunc(sysdate),   '*** Common Knowledge. ***');
  INSERT INTO Secrets VALUES (trunc(sysdate+1), '*** Don''t Let Anyone know this. ***');

  CREATE OR REPLACE PROCEDURE ShowRevealedSecrets IS
     vStatement varchar2(200);
     vOutput Varchar2(1000);
     vDate date:=sysdate;
  begin
  vStatement:='SELECT secret FROM Secrets WHERE RevealDate = ''' || vDate || '''';
  execute immediate vStatement INTO vOutput;
  DBMS_Output.Put_Line(vOutput);
  END;
  /

  --Normal Use.     
  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';
  EXEC ShowRevealedSecrets();

  --Explointing SQL Injection
  ALTER SESSION SET NLS_DATE_FORMAT = '"'' OR RevealDate > sysdate--"';
  EXEC ShowRevealedSecrets();

In this situation a malicious individual could alter there sessions date format in such a way as to give them access to data that they would not normally have access to.

share|improve this answer
    
+1 but I think the SQL injection case is pretty narrow. – JNK Mar 6 '12 at 21:43
1  
@JNK I agree, and that makes it all the more likely to be missed in a code review. – Leigh Riffel Mar 6 '12 at 21:59
1  
Off-topic, but this SQL-injection whitepaper is brilliant: accuvant.com/capability/accuvant-labs/security-research/… – Phil Mar 6 '12 at 22:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.