Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

3 Answers

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
show 1 more comment

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/… –  FreshPhilOfSO Mar 6 '12 at 22:30
add comment

I would like a definitive answer to why they should not do this.

I would say you will not find a definitive answer.

Many of the answers already given here are along the lines of, "If localization settings on the server are changed then this query would give unintended results that may not be identified in a timely manner and Bad Things could happen." Fair enough. But most of the applications I've worked on are not fully localized and in these cases I would imagine the case actually is, "If localization settings on the server are changed then many, many Bad Things will happen very quickly before anyone even has a chance to run this query."

In other words, failing to fully localize may well be an acceptable risk for an application that was designed to run in only one region.

share|improve this answer
2  
It's not "If localization settings on the server are changed.." it's _"If localization settings on the client are different from the server and other clients.." –  Andrew Bickerton Mar 7 '12 at 13:18
    
@AndrewBickerton: OK but does not the same principle apply? I've seen bug reports along these lines: "I'm in South Africa and I keep getting errors around dates," and the response was, "This is a UK application so ensure your regional settings are set to the UK," and not, "We need to re-engineer the application!!" i.e. the risk of losing overseas customers was deemed acceptable. –  onedaywhen Mar 7 '12 at 15:41
    
Date formats might be changed for reasons other than localization. For example, my login script changes the format to include the time portion and displays the month as a number rather than a name. When I run packaged code that does not specify date formats it has problems even though the local is the same. –  Leigh Riffel Mar 7 '12 at 15:58
    
No same principle does not apply, if you have 2 clients both writing dates using different formats you now have wrong data stored in the db with only the first indication when you get reports of errors on the 13th (if you're lucky) of the month... good luck fixing the data in that db! –  Andrew Bickerton Mar 8 '12 at 8:26
1  
My take on the question " I would like a definitive answer to why they should not do this." is asking for reasons to give developers for why they should not rely on implicit conversion (ie: the dev is asking "what's wrong with this sloppy code?"), of course for a particular app (the db is always on the same box as the single user app) these reasons may be happily ignored. –  Andrew Bickerton Mar 8 '12 at 14:08
show 2 more comments

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.