how to secure documents in server

[Paul Lautman]

Re: how to secure documents in server

The Natural Philosopher wrote:

J.O. Aho wrote:

>>
>The idea of storing binary files in a database is quite good, but it
>will affect the sql server in a negative way, specially the larger
>the binary files are.
>>

Ok, why should it take longer to pull a large file out of one locatin
in a database than one location in a filesssytem?
>
IME the things that slow databases down are not getting data out of
them, its performing complex relational queries.

I have tested this and I have found it slightly slower to get files from a
database table than from the file system. Then again, it is slightly slower
building pages dynamically with php/MySQL than it is to serve fixed html
pages. So basically, when I find that storing files in a database is the
best way to handle the application I am writing, that's the way I do it.

[Jerry Stuckle]

Re: how to secure documents in server

Bart Van der Donck wrote:

Captain Paralytic wrote:
>

>Actually another way to do it is to store the files in a BLOB field in
>a database and delivering them from there. Here is a tutorial for that
>and you could adapt it for the file system version:
>http://www.php-mysql-tutorial.com/php-mysql-upload.php

>
I'm surprised this document doesn't mention how disastrous it can be
for the performance of a database. Only use for tiny binary data and a
limited amount of records, I'ld say... I would even vote to dismiss
LONGBLOB; it often creates more problems than it solves.
>
--
Bart
>

You're just using the database for what it's made for - storing and
accessing data. It's not at all disastrous - in fact, if you get enough
files in the database, performance may actually improve over that file
system's.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: how to secure documents in server

J.O. Aho wrote:

RAZZ wrote:

>Hello, Can anyone suggest me solution?

>
There been those who already mentioned to store the files outside the
web servers "document root", this is the most secure method (of course
depending on the security of the script/application that supplies the
file, in worst case this can endanger the security of the whole server).
>
.htaccess and similar web server restrictions has the draw back that not
everyone offers this and it can be easy to do it the wrong way when
unexperienced with web server configuration.
>
The idea of storing binary files in a database is quite good, but it
will affect the sql server in a negative way, specially the larger the
binary files are.
>

A common misconception by those who haven't used databases for storing
large amounts of data. Properly configured, the database will have
excellent performance.

A fourth method is to encrypt the files and store them in the "document
root", and the special download script decodes the file when downloaded
by someone with access to get the decrypted file. (this can be combined
with all the other methods too), this way someone accessing the file
directly can't use it.
>
A lot simpler way is to rename the files to something quite random (md5
hash the name, don't forget to salt it), store the hashed filename in a
database table where you have the original filename too. The download
script in this case will take an argument of the original filename, look
in the database for the hashed name, provides the file to the user (with
header you send it as the original name), this way you can't get the
file with direct download unless you know the hashed file name. If you
combine this one with the previous method, you should have a quite good
false security on the files.
>
>
>

Even worse performance than storing the data in the database in the
first place. More overhead for the scripting language, while no
significant savings on the database end.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: how to secure documents in server

Paul Lautman wrote:

The Natural Philosopher wrote:

>J.O. Aho wrote:

>>The idea of storing binary files in a database is quite good, but it
>>will affect the sql server in a negative way, specially the larger
>>the binary files are.
>>>

>Ok, why should it take longer to pull a large file out of one locatin
>in a database than one location in a filesssytem?
>>
>IME the things that slow databases down are not getting data out of
>them, its performing complex relational queries.

>
I have tested this and I have found it slightly slower to get files from a
database table than from the file system. Then again, it is slightly slower
building pages dynamically with php/MySQL than it is to serve fixed html
pages. So basically, when I find that storing files in a database is the
best way to handle the application I am writing, that's the way I do it.
>
>
>

Paul,

But try putting 100K files in a directory on the file system and see how
much it slows things down. Whereas the database will hardly notice any
performance decrease.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jorge]

Re: how to secure documents in server

On Jul 18, 8:58 pm, The Natural Philosopher <a...@b.cwrot e:

J.O. Aho wrote:
>

The idea of storing binary files in a database is quite good, but it
will affect the sql server in a negative way, specially the larger the
binary files are.

>
Ok, why should it take longer to pull a large file out of one locatin in
a database than one location in a filesssytem?
>

I think the point is that retrieving such a large data chunk from a db
might momentarily impact the performance of forthcoming db operations,
think about what happens to the sql database caches.

--Jorge.

[Joost Diepenmaat]

Re: how to secure documents in server

Jerry Stuckle <jstucklex@attg lobal.netwrites :

But try putting 100K files in a directory on the file system and see
how much it slows things down. Whereas the database will hardly
notice any performance decrease.

That really depends on the filesystem. But yeah, most common file
systems don't like that. In any case, neither relational databases nor
normal file systems are optimized for this kind of use - especially
not if the blobs are large.

In other words, your mileage may vary. See also

http://perspectives.mvdirona.com/2008/06/30/FacebookNeedleInAHaystackEfficientStorageOfBillionsOfPhotos.aspx

--
Joost Diepenmaat | blog: http://joost.zeekat.nl/ | work: http://zeekat.nl/

[Bart Van der Donck]

Re: how to secure documents in server

Jerry Stuckle wrote:

Bart Van der Donck wrote:
>

>Captain Paralytic wrote:

>

>>http://www.php-mysql-tutorial.com/php-mysql-upload.php

>

>I'm surprised this document doesn't mention how disastrous it can be
>for the performance of a database. Only use for tiny binary data and a
>limited amount of records, I'ld say... I would even vote to dismiss
>LONGBLOB; it often creates more problems than it solves.

>
You're just using the database for what it's made for - storing and
accessing data.  It's not at all disastrous - in fact, if you get enough
files in the database, performance may actually improve over that file
system's.

I would be interested to see some articles or benchmarks about this
issue. Got any ? From my experience I've actually always encountered
the opposite (MySQL and MS Access) whose performance dramatically
decreases with larger BLOBS. I'm working with many GB's of pictures
for whom I store nothing in tables (ID of the record = name of the
picture / application ties pics to IDs). I've good experiences with
this approach, even under heavy load. But I'm always interested to
learn how this strategy could be improved.

--
Bart

[Paul Lautman]

Re: how to secure documents in server

Jerry Stuckle wrote:

Paul Lautman wrote:

>The Natural Philosopher wrote:

>>J.O. Aho wrote:
>>>The idea of storing binary files in a database is quite good, but
>>>it will affect the sql server in a negative way, specially the
>>>larger the binary files are.
>>>>
>>Ok, why should it take longer to pull a large file out of one
>>locatin in a database than one location in a filesssytem?
>>>
>>IME the things that slow databases down are not getting data out of
>>them, its performing complex relational queries.

>>
>I have tested this and I have found it slightly slower to get files
>from a database table than from the file system. Then again, it is
>slightly slower building pages dynamically with php/MySQL than it is
>to serve fixed html pages. So basically, when I find that storing
>files in a database is the best way to handle the application I am
>writing, that's the way I do it.
>>
>>

>
Paul,
>
But try putting 100K files in a directory on the file system and see
how much it slows things down. Whereas the database will hardly
notice any performance decrease.

I have always found it slightly slower to get the equivalent file from the
database rather than from the file system. But as I say, it doesn't bother
me. If the application is generally better with the files in a database,
that's where they go. If the application is easier with them on disk, then I
put them there. Likewise, if something works better with static html pages I
will use them. When it comes to down to it, we have a vast range of
technologies at our disposal. I look upon my role as being good at picking
the right one for the right task. There is always a balance to be struck
between speed of processing, functionality, ease of maintenance, ...

[Jerry Stuckle]

Re: how to secure documents in server

Bart Van der Donck wrote:

Jerry Stuckle wrote:
>

>Bart Van der Donck wrote:
>>

>>Captain Paralytic wrote:
>>>http://www.php-mysql-tutorial.com/php-mysql-upload.php
>>I'm surprised this document doesn't mention how disastrous it can be
>>for the performance of a database. Only use for tiny binary data and a
>>limited amount of records, I'ld say... I would even vote to dismiss
>>LONGBLOB; it often creates more problems than it solves.

>You're just using the database for what it's made for - storing and
>accessing data. It's not at all disastrous - in fact, if you get enough
>files in the database, performance may actually improve over that file
>system's.

>
I would be interested to see some articles or benchmarks about this
issue. Got any ? From my experience I've actually always encountered
the opposite (MySQL and MS Access) whose performance dramatically
decreases with larger BLOBS. I'm working with many GB's of pictures
for whom I store nothing in tables (ID of the record = name of the
picture / application ties pics to IDs). I've good experiences with
this approach, even under heavy load. But I'm always interested to
learn how this strategy could be improved.
>
--
Bart
>

Over 20 years of experience doing it, starting with DB2 on mainframes.

But don't count MS Access in there. Use a real database. MySQL
qualifies. And it has to be configured properly.

BTW - benchmarks tell exactly one thing - how a database runs UNDER
THOSE CONDITIONS. Change the conditions and benchmarks aren't valid any
more.

With that said, under live conditions, I've seen virtually no slowdown
when accessing blob data in a database. And in some cases it actually
runs faster.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: how to secure documents in server

Paul Lautman wrote:

Jerry Stuckle wrote:

>Paul Lautman wrote:

>>The Natural Philosopher wrote:
>>>J.O. Aho wrote:
>>>>The idea of storing binary files in a database is quite good, but
>>>>it will affect the sql server in a negative way, specially the
>>>>larger the binary files are.
>>>>>
>>>Ok, why should it take longer to pull a large file out of one
>>>locatin in a database than one location in a filesssytem?
>>>>
>>>IME the things that slow databases down are not getting data out of
>>>them, its performing complex relational queries.
>>I have tested this and I have found it slightly slower to get files
>>from a database table than from the file system. Then again, it is
>>slightly slower building pages dynamically with php/MySQL than it is
>>to serve fixed html pages. So basically, when I find that storing
>>files in a database is the best way to handle the application I am
>>writing, that's the way I do it.
>>>
>>>

>Paul,
>>
>But try putting 100K files in a directory on the file system and see
>how much it slows things down. Whereas the database will hardly
>notice any performance decrease.

>
I have always found it slightly slower to get the equivalent file from the
database rather than from the file system. But as I say, it doesn't bother
me. If the application is generally better with the files in a database,
that's where they go. If the application is easier with them on disk, then I
put them there. Likewise, if something works better with static html pages I
will use them. When it comes to down to it, we have a vast range of
technologies at our disposal. I look upon my role as being good at picking
the right one for the right task. There is always a balance to be struck
between speed of processing, functionality, ease of maintenance, ...
>
>
>

Yes, but with that many files in a directory, even Linux slows down
quite a bit. It isn't made to handle that many different files.

But for a good database, you're just starting.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: how to secure documents in server

Jorge wrote:

On Jul 18, 8:58 pm, The Natural Philosopher <a...@b.cwrot e:

>J.O. Aho wrote:
>>

>>The idea of storing binary files in a database is quite good, but it
>>will affect the sql server in a negative way, specially the larger the
>>binary files are.

>Ok, why should it take longer to pull a large file out of one locatin in
>a database than one location in a filesssytem?
>>

>
I think the point is that retrieving such a large data chunk from a db
might momentarily impact the performance of forthcoming db operations,
think about what happens to the sql database caches.
>
--Jorge.
>

Not at all, if the database is properly configured.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[The Natural Philosopher]

Re: how to secure documents in server

Paul Lautman wrote:

Jerry Stuckle wrote:

>Paul Lautman wrote:

>>The Natural Philosopher wrote:
>>>J.O. Aho wrote:
>>>>The idea of storing binary files in a database is quite good, but
>>>>it will affect the sql server in a negative way, specially the
>>>>larger the binary files are.
>>>>>
>>>Ok, why should it take longer to pull a large file out of one
>>>locatin in a database than one location in a filesssytem?
>>>>
>>>IME the things that slow databases down are not getting data out of
>>>them, its performing complex relational queries.
>>I have tested this and I have found it slightly slower to get files
>>from a database table than from the file system. Then again, it is
>>slightly slower building pages dynamically with php/MySQL than it is
>>to serve fixed html pages. So basically, when I find that storing
>>files in a database is the best way to handle the application I am
>>writing, that's the way I do it.
>>>
>>>

>Paul,
>>
>But try putting 100K files in a directory on the file system and see
>how much it slows things down. Whereas the database will hardly
>notice any performance decrease.

>
I have always found it slightly slower to get the equivalent file from the
database rather than from the file system. But as I say, it doesn't bother
me. If the application is generally better with the files in a database,
that's where they go. If the application is easier with them on disk, then I
put them there. Likewise, if something works better with static html pages I
will use them. When it comes to down to it, we have a vast range of
technologies at our disposal. I look upon my role as being good at picking
the right one for the right task. There is always a balance to be struck
between speed of processing, functionality, ease of maintenance, ...
>
>

Yes. Exactly. The key is to not get religious about it ..."the RIGHT way
is to.."

Advantages of the database...

- one point backup of all data
- definitely not directly accessible via HTML
- has much better indexing and searching than a flat file system in a
directory.
- possibly simpler integration with other bits of data assciated with te
file to be served )i.e. you MIGHT want a decsription of what it is).

On the downside, its a few more machine cycles and possibly a lot more
RAM to serve it up.


HOWEVER it is perfectly possible to have separate database on even a
separate machine to do the serving, if it gets too onerous.

[Paul Lautman]

Re: how to secure documents in server

The Natural Philosopher wrote:

Advantages of the database...
>
- one point backup of all data
- definitely not directly accessible via HTML
- has much better indexing and searching than a flat file system in a
directory.
- possibly simpler integration with other bits of data assciated with
te file to be served )i.e. you MIGHT want a decsription of what it
is).

Also, and this is the bit I really like, when you delete the record the file
automatically goes with it.

[Paul Lautman]

Re: how to secure documents in server

Jerry Stuckle wrote:

Paul Lautman wrote:

>The Natural Philosopher wrote:

>>J.O. Aho wrote:
>>>The idea of storing binary files in a database is quite good, but
>>>it will affect the sql server in a negative way, specially the
>>>larger the binary files are.
>>>>
>>Ok, why should it take longer to pull a large file out of one
>>locatin in a database than one location in a filesssytem?
>>>
>>IME the things that slow databases down are not getting data out of
>>them, its performing complex relational queries.

>>
>I have tested this and I have found it slightly slower to get files
>from a database table than from the file system. Then again, it is
>slightly slower building pages dynamically with php/MySQL than it is
>to serve fixed html pages. So basically, when I find that storing
>files in a database is the best way to handle the application I am
>writing, that's the way I do it.
>>
>>

>
Paul,
>
But try putting 100K files in a directory on the file system and see
how much it slows things down. Whereas the database will hardly
notice any performance decrease.

Actually I guess I ought to qualify my timings comment. I have no proof that
it is the database that was slowing things down per-se. To serve the images
required invoking a load of script, which wasn't going to help and of course
the MySQL installation was on a shared server, so no opportunity to optimise
the settings for this task.

[Michael Fesser]

Re: how to secure documents in server

..oO(The Natural Philosopher)

>Yes. Exactly. The key is to not get religious about it ..."the RIGHT way
>is to.."
>
>Advantages of the database...
>
>- one point backup of all data
>- definitely not directly accessible via HTML
>- has much better indexing and searching than a flat file system in a
>directory.
>- possibly simpler integration with other bits of data assciated with te
>file to be served )i.e. you MIGHT want a decsription of what it is).
>
>On the downside, its a few more machine cycles and possibly a lot more
>RAM to serve it up.

Some more pros and cons:

http://groups.google.com/group/alt.php.sql/msg/c0e4dd4f90eafa84

Micha