ASP, SQL, and Images.

Eldaria · Dec 31, 2004

I want to be able to store and retrieve Images from an SQL database.

I searched the forums, but has so far not got a clear answer, I could have missed something, but then again, I will try and ask here.

I have heard pro's and con's against storing them in the database, but one issue I have with storing the link in the database instead of the actual image is permissions.
I need to restrict users from accessing pictures they do not have right to, so storing them in a path would only require the user to figure out where one image is located, and then start guessing the rest.

If I store it in the Database I would be able to restrict it since a user would not be able to create their own queries for the database.

Now, when we are so far, how can this be done?

I want to be able to store the image from an ASP page.
I then want to be able to retreive it and resize it depending on if it is a thumbnail or a full image. Thereby only sending the optimal sized image to the end user.

Now how can this be done? Code examples would be nice.
Also I suppose I need some kind of control to upload, and to do image handling.
I'm on a very low budget since it is a startup project, so does anyone know of a control that can handle the images and the upload?
The Images will only be jpeg.

The SQL server is located differently than the Web Server, does that cause problems?

Lot of questions, hopefully an answer exists.

Regards,
Brian

Tarwn · Jan 1, 2005

Concerning the dynamic resize: you should look into something like ASPIMage or ASPJpeg. There is no built in capability for resizing and writing your own can get kind of nasty since a lot of the necessary commands don't exist in VBScrpt so you have to go through a lot of workarounds when dealing with binary data.

Uploading: Several compoenents out there to handle this also. Try doing a google search for "Pure ASP Upload" or somehitng like that, you should be able to find some scripts that don't require components to handle your upload.

You could still store a link in the database and display the image without exposing the path (as well as manage security). Basically you would need to create a single ASP file that uses fso to BinaryRead the given file, then output the COntentType and BinaryWrite the data from fso. This is similar to how you would have to handle it if the binary data was in the database (at least the binary write part). As far as security goes you would just set it up as you were planning on doing if the image was in the database.

So basically your image display page will be something like:
1) Accept passed authentication info from wherever (or have it stored in a cookie/session var)
2) Select from your pictures table crossed with your PicturesAccess table for the given picture id and user information
3) if it comes back EOF then they don't have access, so display a default no access pic using a FileSystemObject
4) if your storing only the path to the image in the db (or need to display the no access image) then:
use a FileSystemObject to open a stream on the image file
5) Set the Response.ContentType based on the type of image your outputting
6) Response.BinaryWrite the binary data that came from either your file stream or the database
7) clean up all your objects

Now in your original page you need to specify a few things. At it's smallest it would look somehting like this:

Code:

<html>
<body>
<img src="MyImageLoader.asp?pic_id=1234&username=bob">
</body>
</html>

Of course you would probably want to use Request.Querystring (or Request.Form) values to populate the picture id and username fields. The MyImageLoader.asp file would contain the code that follows the 7 steps above.

Hope this helps,
-T

Eldaria · Jan 3, 2005

This looks like a very intereting solution,

I have never worked with This technique, so would you be so kind to give the example of the MyImageLoader.asp file also.

I will probably be using a session var to store the userID
So the only parameter I would be sending accross is the pic_id or would I still need to send the User ID?.

Anyway I can see that from your example probably.

Thanks.
Brian

Tarwn · Jan 5, 2005

Sorry, I started to reply to this this morning, had to go to work, emailed myself the first half of the post, and it never got here

I'll have a detailed answer for you later,

-T

Eldaria · Jan 5, 2005

LOL,

NO Worries, no hurry.

Besides for me it is evening now. ;-)

Tarwn · Jan 5, 2005

You would only be sending across a pic_id if you didn't want to mess with authentication. If you wanted to mess with authentication then you would need username or user_id also. The want or lack of security also causes more complecity in your database design.

Assuming we want authentication you would need a minimum of 3 tables:
User, UserPic, Pictures

- User would hold any user information (like user_id, user_name, user_pass, etc)
- Pictures would hold individual picture information: pic_id, pic_path, pic_name, pic_type (gif, jpg, etc)
- UserPic would hold access for Users and Pictures: user_id, pic_id

At this point we can either assume that all UserPic records are allowed access, assume all records are denied access, or we can add a user_access field to the UserPic table (though we will still need to know whether to assume access or denial for non-existant pairings). The logic only changes a little later on but could be important from an administration and editing standpoint.

Moving on. Now we want to build a MyImageLoader.asp page that loads an image based on the picture id and user id, I'll try to comment heavily (I'm writing this on the fly):

Code:

<%
Option Explicit

'first define a constant for the path to the "Access Denied" image
'	this isn't completely necessary but makes it easier to alter later on
Const IMAGE_AD = "AccessDenied.gif"
Const IMAGE_AD_TYPE = "gif"

'next determine if the variables were all passed correctly
Dim userId, picId
If Request.QueryString("user_id") <> "" And Request.QueryString("pic_id") <> "" Then
   userId = Request.QueryStrng("user_id")
   picId = Request.QueryString("pic_id")
Else
   'somethng wasn't passed and we require both to show an image
   '	so lets show the default "Access Denied" image
   '	using the future OutputImage() function we will build
   OutputImage IMAGE_AD, IMAGE_AD_TYPE
End If

'next we need to pull back the database info to get the access and path info for the picture
'	this is the part that depends on whether you want to deny access by default or grant access
'	by default

'do your database stuff
Dim conn_db, str_sql, rs_result
Set conn_db = Server.CreateObject("ADODB.Connection")
conn_db.Open "your connection string to your db"

'sql string if we want to assume lack of record is denial of access
str_sql = "SELECT pic_path, pic_name, pic_type " & _
		"FROM (Pictures INNER JOIN UserPic ON Pictures.pic_id = UserPic.pic_id) " & _
		"WHERE Pictures.pic_id = " & picId & _
		" AND UserPic.user_id = " & userId

'pull back to recordset
Set rs_result = conn_db.Execute(str_sql)

'if recordset is empty (EOF) then show "Access Denied" pic, otherwise show returned pic
If rs_result.EOF Then
	OutputImage IMAGE_AD, IMAGE_AD_TYPE
Else
   OutputImage rs_result("pic_path"), rs_result("pic_type")
End If

Set rs_result = Nothing
conn_db.Close
Set conn_db = Nothing

'--- OutputImage Routine
'---	Takes 3 arguments:
'---	imagePath - A Relative Path for an image on the local system
'---	imageType - GIF, JPG, or other valid choices to define the image type
Sub OutputImage(imagePath,imageType)
	'define our FileSystemObject, File, and Filename variables
	Dim fso, fil, fil_name, fil_stream
	
	'turn the relative path into a physical path
	fil_name = Server.MapPath(imagePath)

	'instantiate the fso object
	Set fso = Server.CreateObject("Scripting.FileSystemObject")

	'check that the file exists
	If Not fso.FileExists(fil_name) Then
		'if we weren't trying to display the error pic then do so now
		If imagePath <> IMAGE_AD Then
			'output the error image
			OutputImage IMAGE_AD, IMAGE_AD_TYPE
			'end the output here to make the code simpler below
			Set fso = Nothing
			Exit Sub
		Else
			'otherwise there isn't anything we can do because we were
			'	trying to output the error image and it wasn't at the 
			'	location we supplied to the constant above
			Set fso = Nothing
			Exit Sub
		End If
	End If

	'We can only get to this point if the file path appears to be valid
	'	so now we will write the ContentType meta tag and output the image
	Dim contentType
	Select Case UCase(imageType)
		Case "GIF"
			'GIF Mime type
			contentType = "image/gif"
		Case "JPG"
			'JPG Mime Type
			contentType = "image/jpg"
		Case Else
			'an unrecognized filetype, lets try to strip the extension off the
			'	image and if it is differant we will re-call this sub and
			'	stop the current execution at this point
			Dim t_file_ext
			'*** this may need correction since I wrote it on the fly
			t_file_ext = Right(imagePath,Len(imagePath) - InStrRev(imagePath,"."))
			If t_file_ext <> imageType Then
				OutputImage imagePath, t_file_ext
			ElseIf imagePath <> IMAGE_AD Then	'if this isn't the error image, show the error message
				OutputImage IMAGE_AD, IMAGE_AD_TYPE
			End If
			
			'we either handled the problem or ran out of options, in either case we don't need to continue
			Set fso = Nothing
			Exit Sub
	End Select

	'content type must have been recognized, file must exist, on with the show
	
	'get a handle on the file and use an ADODB.Stream to open a binary stream
	Set fil = fso.GetFile(fil_name)
	Set fil_stream = Server.CreateObject("ADODB.Stream")

	'open the stream object, set it to binary, load the file
	fil_stream.Open
	fil_stream.Type = 1
	fil_stream.LoadFromFile fil_name

	'output the headers necessary for the browser to treat the raw binary data as an image
	Response.AddHeader "Content-Disposition", "filename=""" & imagePath & """"
	Response.AddHeader "Content-Length", fil.Size
	Response.Charset = "UTF-8"
	Response.ContentType = ContentType

	'use the BinaryWrite to write the contents of our binary file stream
	Response.BinaryWrite fil_stream.Read

	'all done, just cleaning up
	fil_stream.Close
	Set fil_stream = Nothing
	Set fil = Nothing
	Set fso = Nothing
End Sub

%>

Hopefully that will work for you on the first try or only have minor problems. The easiest way to test the OutputImage function would be to copy that into a file by itself and write a hardcoded call to the function with thepath of an image you know exists. Let me know if you run into any major problems,

-T

Eldaria · Jan 5, 2005

Thanks, This looks great, I can just from scanning through it see that I will learn a bunch of usefull stuff from this.

I will try this sometime this weekend, I'm of to bed now, and tomorow I got no time.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP, SQL, and Images.

Eldaria

Programmer

Tarwn

Programmer

Eldaria

Programmer

Tarwn

Programmer

Eldaria

Programmer

Tarwn

Programmer

Eldaria

Programmer

Similar threads

Part and Inventory Search

Sponsor