Building SQL manually considered harmful

I just got done fixing a painful bug. The long and short of it is that I had code like this (simplified a lot for brevity):
Public Sub StoreSyncDate()
Dim sql As String = "insert into SyncTimes (ObjectID, SyncDate) values ({0}, '{1}')"
sql = String.Format(sql, Me.ID, DateTime.Now)
Me.Executequery(sql)
End Sub
On my machine, sql gets passed as a string like "insert into SyncTimes (ObjectID, SyncDate) values (42, '11/22/2005 11:34:45 AM')" . Life is good (ignore the SQL injection security problem for the moment).

Then a co-worker in Ireland emails me to ask why my application has broken. Things were working fine earlier in the month, but now he's getting an unhandled exception. What's going on here? Head over to your Regional settings in the Control Panel and change your region to English (Ireland) for some local flavor: "insert into SyncTimes (ObjectID, SyncDate) values (42, '22/11/2005 11:34:15' )" . There's no month 22, SQL complains, life is not good. The even trickier part is before the 13th of the month, dd/mm/yyyy strings turn in to valid mm/dd/yyyy strings. This is probably worse since it's subtle data corruption rather than an obvious error.

So the moral of the story is, be smarter than me. You never know what will go wrong when you're not using parameterized queries.

Ryan Cavanaugh

Comments

• Anonymous
  November 22, 2005
  A common mistake. You either need to use the documented formats (YYYMMDD) or use CONVERT with a specific style.
  
  Or, use parameterised queries. ;-)
• Anonymous
  November 26, 2005
  Hi Ryan,
  
  All is not lost. I'd suggest you need to read: http://www.karaszi.com/SQLServer/info_datetime.asp
  
  Most of us outside the U.S. are painfully aware of date/time issues...
  
  Regards,
  
  Greg
• Anonymous
  June 01, 2009
  PingBack from http://paidsurveyshub.info/story.php?id=75439