Hi all,

Im using php $_FILE with pg_escape_bytea to write files to a postgre db field of type bytea.

My actual php/sql is :

$q = query ("INSERT INTO section_files (sponsor_id, area_id, section_id, txt_title , upload_user_id ,
											upload_timestamp, bytes, mime_type, imagewidth, imageheight ) VALUES ( " . 
						p("sponsor_id") . "," .  $area_id . "," . p("section_id") . ",'" . $txt_title . "'," . 						 
						$upload_user_id . ",  now (), '" .	
						pg_escape_bytea (file_get_contents ($_FILES ['image'] ['tmp_name'])) . "', '" . 
						strtolower($mime_prefix) .   strtolower($phpMimeType) . "'," . $imagewidth . "," . $imageheight . ")");

My problem is that when the file is greater than 5mb, the converstion to bytea is greater than the 20mb PHP query length limit permitted and so fails!

My question is how do i work arround this? A suggestion has been to split the file into 1MB chunks and update use UPDATE however i am not that familiar with postgre and not sure how to do.

Alternatively, someone may have a different solution or suggestion.

Cheers

Steve

If possible, I would store the file in a directory somewhere and name it such that it cannot be overwirtten unintentionally. You can then just store the path to the file in your database.

If that is not possible, the only thing that comes to mind would be to, as you said, split the file into smaller files and then store those as multiple rows. You will need some indication that the file contained in each individual row is not complete.

Hope that helps.

ok, thanks.

I would really like to store in the file system. Or i think i would. The files MUST be secure. Ive inherited this problem from someone else that has processed thefiles in this way. Have you any idea what security measures i can put in place to prevent direct file access. Some of the files uploaded will require a 2nd logon to view. I wont want people being able to access by url.

We have in place a .htaccess file but i am literally sat here pondering how we could use to secure these files.

Sorry to add, it's the HOW TO split files and write im stuck on.

You can protect your files with apache basic auth, so it would ask for login and password when a protected file is requested. If you need to perform authorization within a script you will have to install some lightweight frontend webserver (I use nginx), read a lot of manuals and configure it accordingly. Or you may simply put your files outside of web accessible directory and send it with your script through fread(), but this is strongly not recommended because first you will have to reinvent a php webserver to run inside the binary webserver and second you will always have the non-zero probability of running out of memory on high volume of simultaneous requests.