Using
This post started from a curiosity about how to store image data in SQL. Not for any particular reason, just how. I’m now firmly convinced that the correct method is: don’t. And for those wondering, here’s just one article i came across that outlines arguments for (not many) and against (more than a few) storing image data on SQL. Its on an ASP-related site, but the same principles apply to Coldfusion (and PHP and whatever else for that matter). There are a couple points the site doesn’t mention, but the general idea is that its much more advisable to just store location pointers for the images in SQL, and then store the images themselves on the file system.
So this idea in conjunction with the CFCONTENT tag gives me really all the control I might want, without having to awkwardly shove my images into a database (even though the binary aspect to that is pretty cool). So here’s an example of how you could use these two things in coordination.
First, the basic html page.
<html>
<head></head>
<body><img src="fakeimage.cfm?id=1234" />
</body>
</html>
Note the source of the IMG tag is a .cfm page with an associated url parameter. This parameter is what we will use to fetch and serve the correct image to the requester, allowing us to use this single page to serve all the images on our site. Also interesting, even though the page being called is a .cfm page, the html example shown above could have a regular .htm or .html extension.
Next up is the imageserver.cfm page that, you guessed it, serves the images.
<cfif IsDefined("URL.id") AND IsNumeric(URL.id) AND URL.id GT 0>
<cfquery name="fetch" datasource="yourdatabase">
SELECT imagelocation
FROM imagetable
WHERE id = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.id#">
LIMIT 1 <!--- MSSQL users discard this line --->
</cfquery>
<cfif fetch.recordcount AND trim(fetch.imagelocation) NEQ "">
<cfcontent
type="image/#listlast(fetch.imagelocation,'.')#"
file="#fetch.imagelocation#"
reset="true" />
</cfif>
</cfif>
When you store the image’s full path in the database instead of a relative path, you can even store your images outside of your web root (I’ve tested this on Linux and Windows). This could be a good way to keep spiders or other bots from finding your well-hidden images directory. Also, I know some people are purists with their language/s of choice (and I can understand that), but if you really wanted to get fancy here and fetch the correct mime type instead of hoping based on the extension like I did above, you could do this very simply with some nested Java functions:
<cfcontent
type="#getPageContext().getServletContext().getMimeType(fetch.imagelocation)#"
file="#fetch.imagelocation#"
reset="true" />
And remember, since Coldfusion is translated directly to Java bytecode (since version 6), one could maybe argue that the additional functions are really the same as Coldfusion anyway. OK maybe not. Yeah, not really at all. That one sounded better in my head. Anyway, that’s how you could use Coldfusion to serve your site’s images. Some other cool additions to this functionality:
1. You could keep people from leeching your images for their own use, even when they call your imageserver.cfm with the right url parameters. Do this by checking the referer before serving the images, and if its not you, cfabort. Like so:
<!--- strip the referer down to the domain name --->
<cfset request.refererurl = listfirst(replace(CGI.HTTP_REFERER,"http://","","one"),"/") />
<!--- if the referer is any site but this one --->
<cfif comparenocase(request.refererurl,cgi.SERVER_NAME) NEQ 0>
<cfabort/>
</cfif>
I should caution you that technically, CGI variables can be spoofed, but in practice I would count this as few and far between. And if that kind of thing is happening on your site (which you likely won’t know about without some thorough digging anyway), then you likely have larger issues at hand. But this will definitely put the brakes on your average hotlinker. You could even drive the point home by serving up a less-than-pleasant image that they didn’t ask for (if you get my drift lol), instead of aborting.
2. If, for some reason, you wanted to track various statistics on your images and their number of views or whatever, you could do that by databasing any information you want prior to serving the image. Useful information could be anything found in the URL, REQUEST, SESSION, APPLICATION, SERVER, or CGI scopes.
This was fun. I hope this helps someone!
June 17th, 2008 at 8:30 am
Thanks