Storing image in MSSQL database

[sledgeweb]

Hello. I have successfully written a script that allows me to store an image in a MYSQL database. However, I'm now trying to alter the script to work with a MSSQL database. The image seems to get placed into the database OK, but when I try to display it, I get an error message saying

The image could not be displayed because it contains errors

Can someone look at this and offer some suggestions? Like I said, it works for MYSQL. The only difference in the tables is that in MYSQL the image is stored in a field type of BLOB (binary), and in MSSQL, I have the field set to IMAGE (binary).

Code that inserts the image:

if(isset($_POST['submit'])) 
	{	
	if ($_FILES['userfile']['error'] == 0)
		{
		$fn = $_FILES['userfile']['tmp_name'];
		$max_img_width = 200;
		$img_orig_size = getimagesize($_FILES['userfile']['tmp_name']);
		$img_orig_width = $img_orig_size[0];
		$img_orig_height = $img_orig_size[1];
		$mlt = $max_img_width / $img_orig_width;
		$img_new_width = $max_img_width;
		$img_new_height = round($img_orig_height * $mlt);
		$img_resized = imagecreatetruecolor($img_new_width, $img_new_height);
		imagecopyresampled($img_resized, imagecreatefromjpeg($_FILES['userfile']['tmp_name']), 0,0,0,0, $img_new_width, $img_new_height, $img_orig_width, $img_orig_height);
		imagejpeg ($img_resized, $fn);
		$size = filesize($fn);
		$type = $_FILES['userfile']['type'];
		$fp = fopen($fn, "r");
		$contents = ereg_replace("'","''",(fread($fp, $size)));
		$checksum = md5($contents);
		$query = "INSERT INTO pressboxImage (md5, imgsize, image, type) VALUES ('$checksum', '$size', '$contents', '$type')";
		//echo $query;
		$result = mssql_query($query) or die();
		imagedestroy($img_resized);
		fclose($fp);
		}
	}
?>
<form enctype="multipart/form-data" action="<? echo $PHP_SELF; ?>" method="post">
ID: 
<input type="textfield" name="id">
<input type="hidden" name="MAX_FILE_SIZE" value="500000">
Send this file: <input name="userfile" type="file">
<input name="submit" type="submit" id="submit" value="Send File">
</form>

Code to display image:

$checksum = $_GET['imgId'];
$query = "SELECT image FROM pressboxImage WHERE imageId = 1";
$result = mssql_query($query) or die();
$row = mssql_fetch_array($result);
header("Content-type: ".$row['type']); 
print $row['image'];

I know you don't necessarily need to stuff the images into a database, you could just store them in a directory. But, I'd like to see if this works just for my own satisfaction. Whether or not I use it is a different story.

[AstroTeg]

I know you're just doing this for the fun of it. But notice if you hadn't stored the image itself in the database and just the image path, your rewrite to use MS SQL would have gone a lot smoother.

Double check your image header is correct and you may need to look into all the quirkiness of blobs (type "image" is much like a blob in MS SQL) in MS SQL to figure out why its choking.

[ravenlock]

Try to die(); right after you print the contents of the image. There's a chance a blank space or somesuch gets sent (I had this happen to me once).

[sledgeweb]

No luck, although my simplified code now looks like this (for testing):

Upload:

	if ($_FILES['userfile']['error'] == 0)
		{
		$fn = $_FILES['userfile']['tmp_name'];
		$size = filesize($fn);
		$type = $_FILES['userfile']['type'];
		$contents = file_get_contents($fn);
		$data = unpack("H*hex", $contents);
		$checksum = md5($contents);
		$query = "INSERT INTO pressboxImage (md5, imgsize, image, type) VALUES ('$checksum', '$size', 0x".$data['hex'].", '$type')";
		$result = mssql_query($query) or die();
		}

Display:

$checksum = $_GET['imgId'];
$query = "SELECT image FROM pressboxImage WHERE md5 = '$checksum'";
$result = mssql_query($query) or die();
$row = mssql_fetch_assoc($result);
header("Content-type: image/jpeg;"); 
echo $row['image'];

And, still get:

The image “URL TO FILE” cannot be displayed, because it contains errors.

[ravenlock]

I meant changing this:

echo $row['image'];

to this:

echo $row['image'];
die();

Give it a shot.

[AstroTeg]

Comment out the line that does the header. Then load the "image" (aka: your image script) in its own window (sounds like you're doing this already). Then Check the output and see if there's any PHP errors in there. If there is, you'll need to get those taken care of. If not, then I would say there's issues with the image data you have stored in the database.

[sledgeweb]

Originally posted by ravenlock
I meant changing this:

echo $row['image'];

to this:

echo $row['image'];
die();

Give it a shot. [/B]

Yes, I did that, it didn't work, so I took it out.

[sledgeweb]

Originally posted by AstroTeg
Comment out the line that does the header. Then load the "image" (aka: your image script) in its own window (sounds like you're doing this already). Then Check the output and see if there's any PHP errors in there. If there is, you'll need to get those taken care of. If not, then I would say there's issues with the image data you have stored in the database.

If I don't set the content type, I get a screen full of garbage.... the raw image data.

[AstroTeg]

Raw data is the image itself. If you see no PHP errors, then I'd say you have a problem with your image data somewhere.

[sledgeweb]

Another user made these observations (but we can't figure out what is corrupting the data):

I uploaded an image, and compared the original against what your webserver sends. It seems that all 0x5c (which is '\') bytes are stripped from your file and the following byte replaced by something else. This is caused by the string being parsed for escape sequences, f.x. the sequence 0x5c, 0x30 ("\0") is replaced by 0x00, the nul byte.

The file I just uploaded has the id 7d1b66b90af243bc5e608d37bc894fdd. Try looking at that line in Query Analyzer. If the Image fields starts with

0xFFD8FFE00010

then the corruption happens before that data is inserted into the table. If it starts with

0xFFD8FFE05C30

then the data in the table should be correct, and the corruption is happening in the display code.

Of course, this completely corrupts the file. I'm not quite sure where in the process this happens..

I informed him that it was the later case, he responded...

Then the corruption happens in the loading process, the 5C30 is changed
to 00. I can't see anything in the code that could be causing this, but
that doesn't mean there isn't something there :o) Have you tried using
the other mssql_fetch_* functions instead of fetch_assoc?

---

I tried other functions, but got the same results. Can anyone figure out where the data is being corrupted?

You can do a test upload here:
http://www.usef.org/pressbox/upload2.php
Use the resulting links to view results.

Thanks!

[AstroTeg]

I'm going to put my money on this query:

"INSERT INTO pressboxImage (md5, imgsize, image, type) VALUES ('$checksum', '$size', '$contents', '$type')";

The fact that you have double quotes tells PHP to interpret the data in your variables and make changes to it. Such that \0 becomes a null character, \n becomes a carriage return line feed, etc. I'd trying this:

"INSERT INTO pressboxImage (md5, imgsize, image, type) VALUES ('" . $checksum . "', '" . $size . "', '" . $contents . "', '" . $type . "')";

But I'm just wild guessing at this point...

[sledgeweb]

If I use single quotes, it thiinks it is text or a string and fails on the following:

message: Operand type clash: text is incompatible with image (severity 16)

[ravenlock]

Use single quotes around the PHP string, not the SQL string.

[sledgeweb]

Sorry, I don't follow, put single quotes around which php string?

[ravenlock]

Instead of

"INSERT (...) ('image data')"

try 'INSERT (..) ("image data")'

Notice the difference. In the first the PHP string is double-quoted. In the second, the SQL string is double-quoted and the PHP string is single-quoted.

[edit] Never mind. Probably better if I don't strir this soup anymore 😉

[AstroTeg]

You may need to take a look at how you're handling binary data.

$fp = fopen($fn, "r");
        $contents = ereg_replace("'","''",(fread($fp, $size))); 

I never store binary data in a database, but you'll want to make sure you're opening and reading the file in binary mode (I think instead of "r" you need "rb" or something like that). You're ereg_replace() shouldn't be needed IF you are inserting the data in the DB as binary (which you'll need to figure out how to do). Also, (again, a technicallity), you'll find you have better performance with str_replace() then you do with ereg_replace().

[sledgeweb]

The problem with this:

$query = 'INSERT INTO pressboxImage (image) VALUES ("$data")';

is the value contained in the column isn't the data in the variable $data, instead, it is just a string "$data". I tried this, and when I ran the display, it just printed "$data" to the screen.

And, this:
$query = 'INSERT INTO pressboxImage (image) VALUES ("0x' . $data['hex'] . '")';

Results in:
message: Operand type clash: text is incompatible with image (severity 16)

[AstroTeg]

There's a difference between:

'
and
"

Difference: ' = no PHP processing done on the string. The string is a literal string.

" = PHP will process the string looking for $[variable] and swap out $[variable] with the variable's data. This can be helpful and useful, but in my opinion, make things harder to troubleshoot and maintain later on.

This explains why you see $data getting put in as literally and then being swapped out with its data.

I prefer to escape out of the string and then drop my variables in. Its a little more work, but your variables stand out like a sore thumb. Example:

'INSERT INTO DB (field) VALUES (' . $mydata . ')'

As for your insert problem, I'd recommend researching Microsoft's web site for info on how to insert images. Then check out PHP's docs on how to handle and insert binary data into MS SQL.

[sledgeweb]

Originally posted by AstroTeg
You may need to take a look at how you're handling binary data.

$fp = fopen($fn, "r");
        $contents = ereg_replace("'","''",(fread($fp, $size))); 

I never store binary data in a database, but you'll want to make sure you're opening and reading the file in binary mode (I think instead of "r" you need "rb" or something like that). You're ereg_replace() shouldn't be needed IF you are inserting the data in the DB as binary (which you'll need to figure out how to do). Also, (again, a technicallity), you'll find you have better performance with str_replace() then you do with ereg_replace(). [/B]

I switched to str_replace before removing that bit entirely, upload code now looks like this:

$fn = $_FILES['userfile']['tmp_name'];
		$handle = fopen($fn, "rb");
		$contents = fread($handle, filesize($fn));
		fclose($handle);
		$size = filesize($fn);
		$type = $_FILES['userfile']['type'];
		$data = unpack("H*hex", $contents);
		$query = "INSERT INTO pressboxImage (image) VALUES (0x" . $data['hex'] . ")";
		$result = mssql_query($query) or die();

I used "rb", but to no avail.