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):
<![CDATA[ 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
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
