Putting/retriving files into a database

[Eric Kincl]

Hello, its been a while since I posted/looked here... my normal email client
doesn't handle newsgroups :( (ximian evolution)

I was wondering how you stick a file into a database, and then retrive it
again for the user with PHP/MySQL. I tried the following which apparently
didnt work...

Very quick overview of what I did...

html
------
<input type="file" name="file"><input type="submit>
PHP
------
$SQL = "INSERT INTO table (file) VALUES (" + $_REQUEST['file'] + ");";

I didn't even bother running the SQL querry, I just echoed it and I got the
location of the file (ie: /home/eric/blah...)

How do I get the file into the database, and once its there, how do i get it
back out?
Thanks,
-Eric Kincl

[Tom Thackrey]

On 9-Nov-2003, Eric Kincl <Er**@Kincl.net_NO_SPAM_> wrote:

I was wondering how you stick a file into a database, and then retrive it
again for the user with PHP/MySQL. I tried the following which apparently
didnt work...

Very quick overview of what I did...

html
------
<input type="file" name="file"><input type="submit>
PHP
------
$SQL = "INSERT INTO table (file) VALUES (" + $_REQUEST['file'] + ");";

I didn't even bother running the SQL querry, I just echoed it and I got
the
location of the file (ie: /home/eric/blah...)

How do I get the file into the database, and once its there, how do i get
it
back out?

1) Open the uploaded file with fopen() if it's binary, specify 'rb' instead
of 'r'
2) Read the contents of the file into a variable with fread()
3) Close the file with fclose()
4) Use mysql_escape_string() to make the contents of variable work in in the
insert statement
5) insert the contents of variable into the table with an insert sql
statement, be sure the column is a blob type.

to get it out
1) select the db row containing the data you want
2) use fopen() to create a file
3) use fwrite() to write the contents of the appropriate column to the file
4) use fclose() to close the new file.

see
http://www.php.net
http://www.mysql.com
--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)

[Michael Fuhr]

Eric Kincl <Er**@Kincl.net_NO_SPAM_> writes:

I was wondering how you stick a file into a database, and then retrive it
again for the user with PHP/MySQL. I tried the following which apparently
didnt work...
Have you looked at the chapter in the PHP manual entitled "Handling
File Uploads"?

http://www.php.net/manual/en/features.file-upload.php
Very quick overview of what I did...

html
------
<input type="file" name="file"><input type="submit>
What does your <FORM> tag look like? Does it have
ENCTYPE="multipart/form-data"?
PHP
------
$SQL = "INSERT INTO table (file) VALUES (" + $_REQUEST['file'] + ");";

I didn't even bother running the SQL querry, I just echoed it and I got the
location of the file (ie: /home/eric/blah...)
I suspect that you didn't specify ENCTYPE correctly or at all in
your <FORM> tag. If you had, then $_REQUEST['file'] shouldn't be
set all; instead, $_FILES['file'] should have the info you're looking
for. See the aforementioned chapter on handling file uploads for
details.

Also, *never* put user-supplied input (e.g., form data) in an SQL
statement without first making sure it's sanitized. See the Security
chapter in the PHP manual for more information, and pay particular
attention to what it says about SQL Injection in the "Database
Security" section. Even on a private server that the Bad Guys can't
get to, it's a good idea to use good programming habits so they'll
be familiar if you ever have to work on a public-facing application.

http://www.php.net/manual/en/security.index.php
How do I get the file into the database, and once its there, how do i get it
back out?

You have to get the file's contents before you can insert them into
the database. Study the "Handling File Uploads" chapter in the PHP
manual and post a follow up if it doesn't answer your questions.

Once you learn how to get the file's contents, you can store them
in a database with an INSERT statement (making sure to sanitize the
data) and retrieve them with a SELECT query. If you continue to
have problems, then please post a small but complete sample of your
code so we can see what you're doing.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

[Martin Meredith]

You need to make sure you get the content of the file properly first,
see the other replies on detials of how to do this.

I will normally base64_encode a file before putting it into the database
just to make sure that it doesn't corrupt other bits of the database.

to output, just grab the file from the database, decode (if needed) and
output to browser.

Note that it's normally pretty useful to store the incoming Mime-Type of
the data in the database if your accepting more than one type of file so
that you can output the correct headers.

On another note, make sure you watch when uploading files, Mozilla etc
have a few quirks when uploading files.

Here's an extract from one of my recent scripts that handle file uploads.

-- BEGIN --
$get_magic_quotes=get_magic_quotes_gpc();
$input=parse_incoming();

if ($input['act']=='do')
{

if (is_array($HTTP_POST_FILES['config']))
{
$CONFIG_NAME = $HTTP_POST_FILES['config']['name'];
$CONFIG_SIZE = $HTTP_POST_FILES['config']['size'];
$CONFIG_TYPE = $HTTP_POST_FILES['config']['type'];
$CONFIG_TYPE = preg_replace( "/^(.+?);.*$/", "\\1", $CONFIG_TYPE );
$CONFIG_FILE = $HTTP_POST_FILES['config']['tmp_name'];
if (is_array($HTTP_POST_FILES['file'])) {
$FILE_TYPE = $HTTP_POST_FILES['file']['type'];
$FILE_TYPE = preg_replace( "/^(.+?);.*$/", "\\1", $FILE_TYPE );
}

if ($HTTP_POST_FILES['file']['name'] == "" or
!$HTTP_POST_FILES['file']['name'] or $HTTP_POST_FILES['file']['name'] ==
"none" or $HTTP_POST_FILES['config']['name'] == "" or
!$HTTP_POST_FILES['config']['name'] or
$HTTP_POST_FILES['config']['name'] == "none" ) {
die("you must include files in both upload fields");
}
}
else
{
die("you must include files in both upload fields");
}

if ($FILE_TYPE=="" || !$FILE_TYPE)
{
die ("Your file must be less than 2Mb in size!");
}

/*if ($FILE_TYPE=="application/octet-stream") {
die("Your browser did not provide a suitable mime-type for this type
of file.<br /><br />However, your conf_mime_types.php should hold a
default entry that will allow you to upload this file.");
}*/
if (!preg_match("/^conf_mime_types\.php/si",$CONFIG_NAME)) {
die("Your config file MUST be named conf_mime_types.php");
}
$allow_upload=$input['allow_upload']? 1 : 0;
$allow_avatar=$input['allow_avatar']? ",1": "";
if ((!$input['image']) || (!$input['desc'])) die("You forgot to enter
an image name or a description");
$line=" \"$FILE_TYPE\" => array ( $allow_upload,
'{$input['image']}', '{$input['desc']}' $allow_avatar) ,";
$file=join(file($CONFIG_FILE));

--END--

Though the way I handle the file here ( $file=join(file($CONFIG_FILE)) )
is a crude way of doing things, but was best for the job at hand.

Eric Kincl wrote:

Hello, its been a while since I posted/looked here... my normal email client
doesn't handle newsgroups :( (ximian evolution)

I was wondering how you stick a file into a database, and then retrive it
again for the user with PHP/MySQL. I tried the following which apparently
didnt work...

Very quick overview of what I did...

html
------
<input type="file" name="file"><input type="submit>
PHP
------
$SQL = "INSERT INTO table (file) VALUES (" + $_REQUEST['file'] + ");";

I didn't even bother running the SQL querry, I just echoed it and I got the
location of the file (ie: /home/eric/blah...)

How do I get the file into the database, and once its there, how do i get it
back out?
Thanks,
-Eric Kincl

[Eric Ellsworth]

Hi Eric,
Might I recommend that you skip putting the binary data in the database
altogether?

If you'd like the files to be easily searchable, put the location of the
file and any metadata (upload time, uploader, author, etc) into the
database, and store the file in a plain old filesystem. Since the data is
binary, it can't be usefully searched in the database anyway. You can store
the file using move_uploaded_file, using some naming scheme to store the
file, and then write that filename into the database.

This will save you a lot of heartache dealing with addslashes and
stripslashes in PHP as well as making for one less field to verify against
SQL injection Attacks.

Hope that's helpful,

Eric

"Eric Kincl" <Er**@Kincl.net_NO_SPAM_> wrote in message
news:3f******@news.gvsu.edu...

Hello, its been a while since I posted/looked here... my normal email client doesn't handle newsgroups :( (ximian evolution)

I was wondering how you stick a file into a database, and then retrive it
again for the user with PHP/MySQL. I tried the following which apparently
didnt work...

Very quick overview of what I did...

html
------
<input type="file" name="file"><input type="submit>
PHP
------
$SQL = "INSERT INTO table (file) VALUES (" + $_REQUEST['file'] + ");";

I didn't even bother running the SQL querry, I just echoed it and I got the location of the file (ie: /home/eric/blah...)

How do I get the file into the database, and once its there, how do i get it back out?
Thanks,
-Eric Kincl