Image Resizer

SQLReader plugin

Allows you to access binary blobs in a SQL database using a URL. Accepts integer, GUID, and string identifiers for images.

Can serve non-image files from SQL, or can be configured in 'untrusted data mode' where all SQL blobs are decoded and re-encoded to prevent any kind of attack.

Example Urls

  • http://mysite.com/databaseimages/38.jpg
  • http://mysite.com/databaseimages/21EC2020-3AEA-1069-A2DD-08002B30309D.jpg
  • http://mysite.com/databaseimages/flower32.jpg

See Samples/SqlReaderSample/ in the download for a comprehensive sample project, complete with uploading, listing, and processing.

Features

  • Can serve non-image data if desired
  • Compatible with CloudFront and DiskCache
  • Supports modified-date comparison between SQL and the filesystem so updated versions of cached files are detected
  • Easy to configure

Installation

Either run Install-Package ImageResizer.Plugins.SqlReader in the NuGet package manager, or:

  1. Add a reference to ImageResizer.Plugins.SqlReader to your project.

  2. Add SqlReader to the <plugins /> section.

     <add name="SqlReader" 
   prefix="~/databaseimages/" 
   connectionString="database" 
   idType="UniqueIdentifier" 
   blobQuery="SELECT Content FROM Images WHERE ImageID=@id"
   modifiedQuery="Select ModifiedDate, CreatedDate From Images WHERE ImageID=@id" 
   existsQuery="Select COUNT(ImageID) From Images WHERE ImageID=@id"
   requireImageExtension="false" 
   cacheUnmodifiedFiles="true"
   extensionPartOfId="false"
   checkForModifiedFiles="true"
   vpp="true"
   untrustedData="false" />

Supported key types

Built-in support is included for the following kinds of keys in the URL. If you have another key type you'd like to request, e-mail [email protected]

  • Int
  • Guid
  • TinyInt
  • SmallInt
  • BigInt
  • VarChar
  • NVarChar
  • NChar
  • Char
  • UniqueIdentifier

Configuration reference

  • prefix - This is the app-relative virtual folder where the BLOBs will be accessible within. Separates the DB items from the rest of the site. Should be in the form "~/folder/", with leading ~/ and trailing /. Defaults to ~/databaseimages/, but ~/sql/ is probably a shorter and kinder alternative.
  • connectionString - Can be the name of a connection string in web.config, or can be an actual connection string
  • idType - One of the SqlDbType values, the type that is used for the ID column on the images table.
  • blobQuery - A SQL query that returns the binary image data based on the ID. Defaults to SELECT Content FROM Images WHERE ImageID=@id
  • modifiedQuery - A query that returns the modified and created date of the image. Defaults to Select ModifiedDate, CreatedDate From Images WHERE ImageID=@id. Of all the dates returned by the query, the first non-empty date is used - this allows fallback if no modified date is available.
  • existsQuery - A query that returns whether an image exists or not. Defaults to Select COUNT(ImageID) From Images WHERE ImageID=@id
  • extensionPartOfId - (defaults false) If you are using a string ID type for the image, and the file extension is part of that ID, set this to true.
  • bool .CheckForModifiedFiles (XML: checkForModifiedFiles="false") - Causes the modified date of the source file to be retreived. If the modified date has changed, the image will be re-downloaded and re-processed. Affects performance, as it causes a roundtrip to occur even when an image is cached. Metadata caching can minimize the request count.
  • bool .LazyExistenceCheck (XML: lazyExistenceCheck="true") - If false (the default), does not check for the existence of a file until .Open() is called. Reduces network roundtrips.
  • bool .CacheMetadata (XML: cacheMetadata="true") - Enables metadata caching to minimize roundtrips (defaults to true).
  • IMetadataCache .MetadataCache - The metadata cache to use. Defaults to StandardMetadataCache, which caches metadata for up to an hour after it was last accessed or used (sliding expiration).
  • bool .ExposeAsVpp (XML: vpp="true") - Exposes the blobs via a VirtualPathProvider, so that they can be served (when unmodified) by StaticFileHandler. Otherwise, unmodified blobs may be inaccessible.
  • string .VirtualFilesystemPrefix (XML: `prefix="~/vpp") - The virtual subfolder within which the blobs will be accessible.
  • bool .RequireImageExtension (XML: requireImageExtension="true") - If false, the blob provider will cause non-image requests to be processed by ImageResizer. Always use a file extension if possible, as otherwise the wrong content-type may served by IIS. If true, prefix may NOT overlap with any other routes or locations, as ImageResizer will be intercepting all requests within the specified virtual path.
  • bool .UntrustedData (XML: untrustedData="false") - If true, all images will be re-encoded before being served to the client. Invalid or malicious images will fail with an error if they cannot be read as images. This should prevent malicious files from being served to the client.
  • bool .CacheUnmodifiedFiles (XML: cacheUnmodifiedFiles="false" ) - If true, unmodified requests will be cached by ImageResizer (if DiskCache is installed). This can be useful if unmodified files are relatively small and the backend storage is latent.

This plugin inherits most of its functionality from BlobProviderBase, which you can use to create your own provider.

This plugin (SQLReader plugin) is part of the Performance Edition

Where is the plugins section?

The <plugins> section is located in Web.config, and is nested inside the <resizer> element, which is nested inside <configuration>. For examples, see this sample Web.config file.

Where can I find the dll?

We prefer that you install via NuGet, but you can also find the plugin DLL files in the /dlls/release folder of your download.

How do I typically install a plugin via Web.Config?

  1. In Visual Studio, right click on your project and choose "Add reference". Browse to the plugin DLL and click "OK".
  2. In the <plugins> section of Web.config, insert <add name="PluginName" />
  3. Look at the plugin documentation to see what configuration options (if any) are available.

How do I typically install a plugin via code?