Problem inserting BLOB into PostgreSQL db
Moderator: General Moderators
Problem inserting BLOB into PostgreSQL db
Hi all. I'm having a problem uploading images and inserting them as BLOBs into a PostgreSQL db.
The file is uploading okay, and I can verify its existence with file_exists, but whenever the query is executed to insert it as a BLOB, I get the following error:
Warning: pg_query() query failed: ERROR: lo_import: can't open unix file "/var/tmp/phpTTaOif": No such file or directory in /u1/trng/gateway/_upload/input_file.php on line 21
This is the query:
INSERT INTO pic_db (name, picoid) VALUES ('circle-bottom.gif', lo_import('/var/tmp/phpTTaOif'))
Why would it give me an error telling me the file doesn't exist, when I can run file_exists() right before the query is executed and it tells me it exists.
Thanks in advance,
Pablo
The file is uploading okay, and I can verify its existence with file_exists, but whenever the query is executed to insert it as a BLOB, I get the following error:
Warning: pg_query() query failed: ERROR: lo_import: can't open unix file "/var/tmp/phpTTaOif": No such file or directory in /u1/trng/gateway/_upload/input_file.php on line 21
This is the query:
INSERT INTO pic_db (name, picoid) VALUES ('circle-bottom.gif', lo_import('/var/tmp/phpTTaOif'))
Why would it give me an error telling me the file doesn't exist, when I can run file_exists() right before the query is executed and it tells me it exists.
Thanks in advance,
Pablo
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
Sorry for the cross post, but I've been trying for a while to get a positive response to my problem in a number of different forums with no luck.
The reason I'm storing images in the database is that the server my site is running on is set with stringent security levels, so PHP cannot actually create files itself (or move files, etc.) so when i try to use the move_uploaded_file() function it doesn't have enough rights to do so.
I'm developing a CMS for the site, and due to the security issues I'm forced to store all images and PDF files as BLOBs in the PostgreSQL db.
Here's the code that's handling the upload:
so as you can see, all that's happening is it's uploaded, and then I'm attempting to use lo_import to import the file into the db. I get the same error when I execute the query from the command line.
Any ideas?
Thanks in advance,
Pablo
The reason I'm storing images in the database is that the server my site is running on is set with stringent security levels, so PHP cannot actually create files itself (or move files, etc.) so when i try to use the move_uploaded_file() function it doesn't have enough rights to do so.
I'm developing a CMS for the site, and due to the security issues I'm forced to store all images and PDF files as BLOBs in the PostgreSQL db.
Here's the code that's handling the upload:
Code: Select all
<?php
if (is_uploaded_file($testfile)) {
chmod($testfile,0777);
$sql = "INSERT INTO pic_db (name, picoid) VALUES ";
$sql .= "('$testfile_name', lo_import('$testfile'))";
$stat = pg_query($dbh, $sql);
if (!$stat) {
echo "the picture cannot be added<br>";
exit;
} else {
echo "the picture has been added successfully<br>";
}
} else {
echo "no file uploaded";
}
?>Any ideas?
Thanks in advance,
Pablo
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
sounds like a filesystem problem to me. might have something to do with those security measures. not to familiar with PostGres so im not to sure what the lo_import function does (could you point me to a reference page?). the rest of the SQL looks pretty standard though. the chmod command looks unneeded to me. have you tried taking it out?
I can't seem to find the reference page for lo_import but I know I have it at work so I'll post it tomorrow.
What the lo_import function does, basically, is it reads a file on the filesystem (in this case the file I've uploaded) and stores its contents as a BLOB in the database, for future retrieval and use of the file.
I removed the chmod command and got the same result. I actually took this code from the book "PHP and PostgreSQL Advanced Web Programming" and the only reason I could see the chmod command being there would be to set the permissions such that the file could be read by the user postgres is running as. But as I said taking it out didn't seem to make a difference.
P.
What the lo_import function does, basically, is it reads a file on the filesystem (in this case the file I've uploaded) and stores its contents as a BLOB in the database, for future retrieval and use of the file.
I removed the chmod command and got the same result. I actually took this code from the book "PHP and PostgreSQL Advanced Web Programming" and the only reason I could see the chmod command being there would be to set the permissions such that the file could be read by the user postgres is running as. But as I said taking it out didn't seem to make a difference.
P.
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
it might be that the security measures on the server are so tight that file can only be read by their owners, perhaps only their group and the chmod command just has no effect. if iremember correctly the web server usually runs as user "nobody" (since im not on unix much i could very well be wrong) and as such would not have proper permissions to read a file. i know it sounds weird but when it come to unix permissions things do tend to get weird.
could some other forum users chime in on this?
could some other forum users chime in on this?
I would agree that it is a filesystem level problem. I've used code identical in the past to store images into a PostGreSQL database.
Typically speaking the webserver (and therefore PHP) runs as either user "nobody"(RH and many other distros) or "www-data"(Debian).
The PostGreSQL backend usually runs as user "postgres" for lo_import functions to work you need to be able to read the file as "postgres".
Your error message is coming from PostGreSQL, not from PHP so its a problem with the permissions at that level and not PHP. (Which is why file_exists can find the file.)
Typically speaking the webserver (and therefore PHP) runs as either user "nobody"(RH and many other distros) or "www-data"(Debian).
The PostGreSQL backend usually runs as user "postgres" for lo_import functions to work you need to be able to read the file as "postgres".
Your error message is coming from PostGreSQL, not from PHP so its a problem with the permissions at that level and not PHP. (Which is why file_exists can find the file.)
Thank you all very much for your feedback! Very helpful! One thing which may be worthy of note here is that the server we are running is the Oracle 9IAS, and the user PHP is running as is "Oracle". I'm not sure which user Postgres is running as but chances are it's the "Oracle" user.
LIGHT BULB!!!!!
Okay, this will probably have MAJOR implications on my problem. I just realized that the PostgreSQL db I'm hitting isn't located on the same physical server as the scripts I'm running from. My sysadmin will be making changes to the kernel tonight so as of tomorrow it will be, but until now the file I've been uploading is stored in /var/tmp on one machine, and the database into which I'm attempting to import the file is on another.
Could this be the cause? It would seem logical to me.
Pablo
LIGHT BULB!!!!!
Okay, this will probably have MAJOR implications on my problem. I just realized that the PostgreSQL db I'm hitting isn't located on the same physical server as the scripts I'm running from. My sysadmin will be making changes to the kernel tonight so as of tomorrow it will be, but until now the file I've been uploading is stored in /var/tmp on one machine, and the database into which I'm attempting to import the file is on another.
Could this be the cause? It would seem logical to me.
Pablo
You may want to ask a question on the postgresql email lists. I'm not sure what the behavoir of the psql client is when connecting over a tcp connection. You'll need to find out if filenames specified in lo_import are searched for on the client or server end.
I've always gotten a good response from the folks on the list, provided I ask intelligently. If you rush/scream for help they tend to ignore you.
I've always gotten a good response from the folks on the list, provided I ask intelligently. If you rush/scream for help they tend to ignore you.