How can I add file locations to a database after they are uploaded using a Perl CGI script?

Question

I have a CGI program I have written using Perl. One of its functions is to upload pics to the server.

All of it is working well, including adding all kinds of info to a MySQL db. My question is: How can I get the uploaded pic files location and names added to the db?

I would rather that instead of changing the script to actually upload the pics to the db. I have heard horror stories of uploading binary files to databases.

Since I am new to all of this, I am at a loss. Have tried doing some research and web searches for 3 weeks now with no luck. Any suggestions or answers would be greatly appreciated. I would really hate to have to manually add all the locations/names to the db.

I am using: a Perl CGI script, MySQL db, Linux server and the files are being uploaded to the server. I AM NOT looking to add the actual files to the db. Just their location(s).

Answer 1

It sounds like you have your method complete where you take the upload, make it a string and toss it unto mysql similar to reading file in as a string. However since your given a filehandle versus a filename to read by CGI. You are wondering where that file actually is.

If your using CGI.pm, the upload, uploadInfo, the param for the upload, and upload private files will help you deal with the upload file sources. Where they are stashed after the remote client and the CGI are done isn't permanent usually and a minimum is volatile.

Answer 2

You've got a bunch of uploaded files that need to be added to the db? Should be trivial to dash off a one-off script to loop through all the files and insert the details into the DB. If they're all in one spot, then a simple opendir()/readdir() type loop would catch them all, otherwise you can make a list of file paths to loop over and loop over that.

If you've talking about recording new uploads in the server, then it would be something along these lines:

1. user uploads file to server
2. script extracts any wanted/needed info from the file (name, size, mime-type, checksums, etc...)
3. start database transaction
4. insert file info into database
5. retrieve ID of new record
6. move uploaded file to final resting place, using the ID as its filename
7. if everything goes file, commit the transaction

Using the ID as the filename solves the worries of filename collisions and new uploads overwriting previous ones. And if you store the uploads somewhere outside of the site's webroot, then the only access to the files will be via your scripts, providing you with complete control over downloads.