This goes in the “here’s something I learned today” bucket.
I was working on my ASP.NET based photo gallery sample/experiment and realized that I needed to ensure that the names of images I stored in the database couldn’t contain illegal filename characters. This is because I allow the user to specify a path like http://imgsample/P51.jpg and I use URL rewriting to translate this to http://imgsample/ViewImage.aspx?name=P51 or http://imgsample/ViewImage.aspx?ID=12&size=med.
Not being a database guy (yet!) as I thought about ensuring only valid names I figured I could use form validation. But then I remembered “hey this is a database app”, can’t the database enforce this? A few minutes browsing MSDN clued me into the Check Constraints capability on columns. All I had to do was add the following expression as a check constraint to my images table.
(left([img_name],1) <> ' '
and right([img_name],1) <> ' '
and charindex('\',ltrim(rtrim([img_name]))) = 0
and charindex('/',ltrim(rtrim([img_name]))) = 0
and charindex('?',ltrim(rtrim([img_name]))) = 0
and charindex('&',ltrim(rtrim([img_name]))) = 0
and charindex('*',ltrim(rtrim([img_name]))) = 0
and charindex('..',[img_name]) = 0)
Now whenever I enter an invalid name I get a SQL error on the insert. Cool. Next I need to figure out how to handle those errors gracefully.
RSS - Posts
Bravo!
Why not just check if that file exists, or something. That is user’s input. That is supposed to be validates the closest to the user as possible. It should not be your table’s job to do that. Your table doesn’t even know if those files exist. It is too complicated. Think about it and then think twice before you go public like this thinking that everybody else is going to go "wow, you’re so smart". In fact, you’re silly.