checking for file extensions

Question

Hi, I have created a tables named Media in a database and I want it to accepts only image files. It has two column : ImageID and Image. Data type of the image column is varbinary. Now I want to create a check constraint to check the files extension to make the column to accept correct and special type of files. For example I want the image column to accept only files which have the extensions like .jpg and .bmp. Also I don’t have a extension column in my table to keep file’s extension. Is it possible to check it without having a column for keeping the file name and extension? thanks ( i am using sql server 2008)

Answer 1

MSSQL has no way to know what the binary data in a varbinary column represents, so you cannot do any useful checking directly on the Image column. In fact, because two different applications could interpret the same sequence of bytes in two completely different - and valid - ways, it's not even theoretically possible.

You can obviously add a filename column and/or a file extension column with CHECK constraints, but that's only a check on the filename, not on the binary content. The application that uploads the file needs to ensure that it really is a JPEG or not.

By the way, since you're on 2008, you should probably look into FILESTREAM to see if it's helpful for your application.