//
you're reading...
Hacking / Counter Intelligence, Source Code

Handling unique images in SQL

I am working on a deep packet analysis problem right now and came back to an oldie but goodie that I thought I would share. A common security vulnerability with most web applications is their handling of images. Inexperienced developers will write uploaded images to disk instead of to a database. This design style allows them a simple solutions with a visible result. It also opens the web application up to attack with malformed images, or uploads that aren’t images altogether.

A much easier to sanitize solution is to store images in an SQL database. To conserve space, or in my case, handling high volume image classification, you might want to only store unique versions of an image and simply log an appearance. For instance, I will be collecting all images during the deep packet analysis. If the image is unique, it will be stored in the database. All occurances of images are tagged to the packet dump in a separate table, and I only need to attach a unique ID to the image appearance in the log.

Lets start with the table and stored procedure for dealing with images. The stored procedure inserts a one way hash for each image. This hash is quicker to check than the complete binary of any given image. If the hash of the image to be inserted is already present in the table, all we need to do is return the ID. If the hash is not present, the image is inserted and the newly created id is returned.

CREATE TABLE Images(
	ID int IDENTITY(1,1) NOT NULL,
	Caption nvarchar(50) NOT NULL,
	Content varbinary(max) NOT NULL,
        Hash    nvarchar(50)
)

CREATE PROC InsertImage
@Caption varchar(50),
@Content varbinary(max)
AS
BEGIN TRAN
SELECT TOP 1 ID FROM Images WHERE HASHBYTES('MD5', @Content) = Images.Hash 
IF (@@ROWCOUNT = 0)
BEGIN
 INSERT INTO Images (Caption, Content, Hash)
 VALUES (@Caption, @Content, HASHBYTES('MD5', @Content));
SELECT LAST_INSERT_ID;
END
COMMIT

LAST_INSERT_ID was used instead of SCOPE_IDENTITY for a reason. Scope has its return set of the last fired command, which WILL BE the results of a trigger if you have one attached to the table.

CREATE PROC GetImageByID
@ID int
AS
SELECT Caption, Content FROM Images WHERE ID = @ID

            FileStream fs = new FileStream("c:\test.jpg", System.IO.FileMode.Open);
            byte[] imageAsBytes = new byte[fs.Length];
            fs.Read(imageAsBytes, 0, imageAsBytes.Length);
            fs.Close();

            SqlConnection Conn = new SqlConnection("");
            SqlCommand Comm = new SqlCommand("InsertImage", Conn);                     
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.Parameters.AddWithValue("@Caption", imageName);
            Comm.Parameters.AddWithValue("@Content", imageAsBytes);
            Conn.Open();
            int ID = Comm.ExecuteScalar();
            Conn.Close();
            Image result = null;
            SqlDataReader reader;
            
            SqlConnection Conn = new SqlConnection(""); 
            SqlCommand Comm = new SqlCommand("GetImageByID", Conn);
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.Parameters.AddWithValue("@ID", ID.ToString());
            Conn.Open();

            reader = selectCommand.ExecuteReader(CommandBehavior.CloseConnection);
            while(reader.Read())
            {
                result = Image.FromStream(new MemoryStream((byte[])reader.GetValue(1)));
            }
            return result;

There are some other factors to consider, images of different formats, with slight cropping, or stripped EXIF data even will appear as distinct images. This can cause a large volume of worthless images, and some preprocessing of the data is definitely needed before hand. In my case, I am filtering out images from common domains. Also, during this filtering, it is important that file names are sanitized if they will be used in the future as not all web image filenames are friendly to a local file system.

Advertisements

About Pythorian

Exploration and Production oriented security consultant for securing IT infrastructures relating to natural resources.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: