Saving result to file for download

Hi all!

I made a PHP script that formats and saves the output of a query to a=20
file and provides a link to the file for download. I've never done this=20
before and would appreciate any feedback. Here are things I'd like to=20
improve or am concerned about.

I've looked through Sklar & Trachtenberg's 'PHP Cookbook', googled=20
('PHP postgresql saving result file download' gives you a wide=20
assortment of links!), and attempted to search the archives of=20
pgsql-php, but I keep timing out after 60 seconds. Has anyone else been=20
experiencing problems searching the archives?

1. Right now the file is permanently saved in a directory (used only=20
for saving these results files, unimaginatively named /temp) in the web=20
root of the server (in my case /Library/Webserver/Documents on Mac OS X=20
10.2). I'd rather it be a temporary file so I wouldn't have to worry=20
about clearing out the files if a lot of people generate results files.=20
I'm not concerned that people won't be able to come back to the results=20
file at a later date=97they can just generate a new one. Perhaps I should=
=20
make a cron job to clear out the folder every once in a while?

2. Security. I've changed the owner on /temp to www (the webserver) so=20
that PHP can write to the directory. Here are the permissions.
drwxr-xr-x 23 www admin 782 Oct 27 00:05 temp
I'm guessing I should change the permissions to drwxr--r-- (or even=20
drw-r--r--) as there's no reason there should be execute permissions on=20
the directory.

If anyone's curious, here's the file handling part of the code. Truly=20
nothing special. If anyone would like to see anything else, I'd be=20
happy to oblige.

$docroot =3D '/Library/Webserver/Documents/';
$dir =3D 'temp/';
$path =3D $docroot.$dir;
$id_string =3D uniqid('',1);
$filename =3D 'apps-'.$id_string.'.txt';
$fh =3Dfopen($path.$filename,'w') or die($php_errormsg);
fputs($fh,$app_string);
fclose($fh) or die($php_errormsg);
echo 'Here\'s your file! Download now!
';
echo '';

As this is the first time of done anything like this, I'd appreciate=20
any comments.

Thanks!

Michael

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Saving result to file for download

Just a quick follow up.
On Monday, Oct 27, 2003, at 00:43 Asia/Tokyo, Michael Glaesemann (me!)
wrote:

> I'm guessing I should change the permissions to drwxr--r-- (or even
> drw-r--r--) as there's no reason there should be execute permissions
> on the directory.

I changed the permissions to drw-r--r-- and found out that execute
permissions is *definitely* necessary. Otherwise no one can open the
directory! Learn something new everyday.

Michael


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Saving result to file for download

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello!

Michael Glaesemann (nie 26. październik 2003 16:43):
> Hi all!
>
> I made a PHP script that formats and saves the output of a query to a
> file and provides a link to the file for download. I've never done this
> before and would appreciate any feedback. Here are things I'd like to
> improve or am concerned about.
> [...]
> 1. Right now the file is permanently saved in a directory [...]
> [...]
> I'd rather it be a temporary file so I wouldn't have to worry
> about clearing out the files if a lot of people generate results files.
> I'm not concerned that people won't be able to come back to the results
> file at a later dateâ€=94they can just generate a new one.

Why have you decided to store results in a file? Is that file to be really=
=20
big? or takes long to prepare?

Maybe generating the file 'on the fly' would be easier:

1) Provide a link to a script that generates the file=20
(eg:

2) The script "file.php" sets the content type header to text/plain and jus=
t=20
outputs the result of a query. I am not sure how to set HTTP header on Your=
=20
webserver (with apache there is a PHP function header(string) )

You may find two HTTP headers interesting (examples from my script):

header("Content-type: text/plain; charset=3DISO-8859-2");
// See RFC 2183 [49] (which updates RFC 1806) for details. Content-Disposit=
ion=20
is not part of HTTP standard, but is widely used.
header("Content-Disposition: attachment; filename=3Danka.txt");

After this you just output your data.
That should work. You would then have no troubles with filenames-conflicts,=
=20
disk space wasting and so on.
However some browsers may ignore the content-disposition header and suggest=
=20
the filename 'file.php' when saving. I suppose that's not a big problem...

Bye,
M.P.

- --=20
[http://skoot.qi.pl for GPG keys]
"A computer programmer is someone who, when told to "Go to Hell", sees
the "Go to", rather than the destination, as harmful."
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE/nXhmvkWo15WV1rkRAjJ+AKCTZt4M6iu63kFRBgxeE7kjAvuZZgCf ZI3b
QlXY7b+UpgvSqsojFoLY1UI=3D
=3D1CVb
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Saving result to file for download

Hi M.P.

On Tuesday, Oct 28, 2003, at 04:56 Asia/Tokyo, Mariusz Pekala wrote:
>
> Why have you decided to store results in a file? Is that file to be
> really
> big? or takes long to prepare?

Actually the file is pretty small (less than 8K) and is pretty quick to
make (less than 2 seconds). I just didn't know how to do it any other
way.

> Maybe generating the file 'on the fly' would be easier:
>
> 1) Provide a link to a script that generates the file
> (eg:
>
> 2) The script "file.php" sets the content type header to text/plain
> and just
> outputs the result of a query. I am not sure how to set HTTP header on
> Your
> webserver (with apache there is a PHP function header(string) )

This sounds like *exactly* what I am after. I'll give it a try!

Thanks for taking the time to respond. I knew there should be a better
way but didn't know where to even start looking. I really appreciate
your help.

Michael


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Saving result to file for download

This script initially displays a form for you to enter connection details, a
query, and a return type.
The form then POSTs these details to itself.

When the script detects that form data is being POSTed to it, it connects to
the PG databasew specified in the POST data, and runs the query. The query
is then rendered into the desired output format, and the correct headers are
sent to the browser to save the file.

Note - I don't seem to be getting consistent behaviour with the fle name
disposition header - not sure if this is IIS or IE6 fouling up.

############################################################ #######



if ($_POST){

$connection = pg_connect("host='". $_POST['db']['host'] . "' port='".
$_POST['db']['port'] . "' dbname='". $_POST['db']['db'] . "' user='".
$_POST['db']['user'] . "' password='". $_POST['db']['password'] . "'");

if ($connection) {

$result = pg_query($connection,$_POST['query']);

$output_filename =
($_POST['output_filename']?$_POST['output_filename']:"Postgr eSQL_results");

switch($_POST['output_format']){
case "xml":
header("Content-type: text/xml");
header("Content-Disposition: attachment; " . $output_filename .
".xml");
$output .= ("\n\n");
for($i=0;$i $output .= "\t\n";
foreach(pg_fetch_assoc($result,$i) AS $field=>$value) $output .=
"\t\t$value\n";
$output .= "\t\n";
}
$output .= "\n";
break;
case "csv":
header("Content-type: text/csv");
header("Content-Disposition: attachment; " . $output_filename .
".csv");
foreach(pg_fetch_assoc($result,0) AS $field=>$value) $output .=
"\"$field\",";
$output = rtrim($output,",") . "\n";
for($i=0;$i foreach(pg_fetch_assoc($result,$i) AS $field=>$value) $output .=
"$value,";
$output = rtrim($output,",") . "\n";
}
break;
default:
header("Content-type: text/plain");
header("Content-Disposition: attachment; " . $output_filename .
".txt");
foreach(pg_fetch_assoc($result,0) AS $field=>$value) $output .=
"\"$field\"\t\t";
$output = rtrim($output,"\t") . "\n";
for($i=0;$i foreach(pg_fetch_assoc($result,$i) AS $field=>$value) $output .=
"$value\t\t";
$output = rtrim($output,"\t") . "\n";
}
break;
}
print($output);
die();
}
}

?>