SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Storing images: MySQL BLOB vs. file directory

    Firstly, I just want to say that I know that there is no right or wrong answer to this question. Sometimes it's more appropriate to store images (or any other binary documents such as word and excel files) inside a MySQL database, other times it's more appopriate to store them in the web directory.

    I'd like to focus (if I may) on the example of where a user is uploading an image via a web form.

    My big problem with saving the image to a file directory in this example is that (and correct me if I'm wrong) I have to CHMOD 777 the directory that I want the image to be stored. I'm using a remote virtual host, so doing this opens up security weakness - especially to others who have access to the same server (albeit hopefully not (easily) into my site's sub-directory if it's been setup correctly).

    So uploading the file into a MySQL database avoids this issue completely. Also, I reckon that it gives me more control over access to the image (or document) that I've loaded. For example, if the file stored on the MySQL database is flagged 'private' by the person controlling the upload, then I am in a better position to allow/deny access to that script than if it's stored in a file directory.

    'Course, the downside of storing images/documents in a MySQL database is efficiency. There's no way that access to a file stored in a MySQL database can be more efficient than if it's sitting there out in the open in a directory. But I'm not sure exactly how much of an issue efficiency is...

    I've noticed this subject being touched on in one form or another in different threads, but I'd like to tackle it head-on in this one.

    Does anyone have any words of wisdom to add to the discussion? I'd be very interested to get some alternative points of view, pros and cons, etc.
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  2. #2
    SitePoint Member
    Join Date
    Jun 2001
    Location
    UK
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Efficiency: Well in effect all mysql tables are files, hence u're occuring the same filesystem overheads. The further problem is based on the number of concurent connections mysql is capable of, and the issues of row locking to delay results returned (as images are generally larger than say an int column).

    Security: Thats tricky, although I really, REALLY, think storing images in a database is such a waste of resources (especially if your dealing in any serious volumes of traffic). However being on virtual hosting doesnt help... it's difficult to suggest what to do, do u have the ability to run php scripts as other users? i.e. so the php script runs as a user known to the system hence chmod 777 isnt required?

    If your server supports it, i'd read thro apache configuration via .htaccess

    It isnt a huge debate, but just my 2 cents.

    -dibby

  3. #3
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Spaceman,
    seems a waste of effort to store images in a DB, but many will disagree.

    Regardless, assuming your upload script makes use of copy($userfile,$destination); etc then the image is being created & owned by apache and you should not have to chmod the directory to 777, if you have a problem copying to the directory, just delete it and use mkdir($directory), that way apache owns the directory as well & you should have no problems.

    Also as you have control over the filename during the upload process you can add a prefix or 2 to the filename to 'set' permissions etc, so gif.gif becomes Pgif.gif - easy enough to check before accessing an image etc.

    my 2c

  4. #4
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your feedback, guys.

    With my virtual hosting service, I don't have the ability to run PHP scripts as other users.

    I tried to make a test directory in a php script using the mkdir function:

    <?
    mkdir('test',0777)
    ?>

    (and 755, 775, etc.) but got the error message:

    Warning: MkDir failed (Permission denied)

    so clearly this has been disabled/disallowed. I contacted my virtual hosting provider directly, and their response was:

    "The only other way is to run your upload cgi out of the cgi wrapper directory which will allow the chmod to be lower."

    So it looks like that unless I get myself a dedicated server, then maybe the most 'secure' way of storing my images/documents with my current virtual hosting service is indeed by dumping the binary into a MySQL database...
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Regarding row locking, AFAIK, in MySQL only BerkelyDB tables allow row level locking. the default MyIMAP table uses table level locking. The Nusphere table (forgotten its name) also allows row level locking and will become part of the standard (free) version of MySQL in time.

    I've been giving a lot of thought lately to relational databases versus things like hash tables or balanced-tree data structures. You know, at the end of the day, I think the issue of storing your image as a blob in the db versus in a file in the filesystem is really more an issue of abstraction. AFAIK, MySQL stores BLOBs as seperate objects and places a pointer to them in the actual table.

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Allentown PA
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    spaceman --
    chmod the directory you want PHP to create the directory in to 777, then have PHP create the directory. Then, if you want, you can change it's permissions back.

    In case that didn't make sense, take this example:
    You have /mydir, and you want PHP to create /mydir/test. So chmod /mydir to 777, then have PHP create /mydir/test. After the directory is there, you can reset /mydir's permissions.

    freakysid --
    Technically, InnoDB is the only (currently available, free) MySQL table type that supports row level locking. BDB tables are page level locked. (Although I'm not sure about the actual differences between how the two work. Page level locking is still much better than table level)
    NuSphere's Gemini table type also allows row level locking, as you mentioned. Although I'm not sure if it's becoming part of the free version.
    Last edited by Ed Sullivan; Jun 9, 2001 at 13:05.

  7. #7
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Ed!

    Your advice worked. Nice one.

    At the risk of straining the friendship, what is the minimum (ie. most secure) CHMOD I should be able to get away with - that still allows files to be uploaded? 755 just worked 100% (including the anonymous upload), but I'm wondering how low I can go? Sorry - I'm revealing my less-than-complete understanding of UNIX permissions and how they relate to the manageability of files on the web.

    Thanks also to freakysid for your comments. Just one question - what does AFAIK mean? I'm obviously not up with the latest acronyms... :-)
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AFAIK == As Far As I Know == 3 cm

    Thanks to Ed for the clarification. I was confusing row locking with the BerkeleyDB table type and commit/rollback transaction support. Yeah, I'm pretty sure that I read at mysql.com that they were planning on implimenting the Gemini table type (may already be available in a beta).

    With unix permissions they work like so:

    The three didgits represent the owner, group and others. The owner is the creator of the file (unless this is changed using chown). Others is anybody, which means that regardless of which user apache is running as (often this is user "nobody", group "nobody") it will have access to the file according to the other permissions.

    Each digit represents the sum of the following:
    read =4,
    write = 2,
    execute = 1.

    So 777 represents full read+write+execute permissions for the owner, group and others.

    755 represents full read+write+execute permission for the owner and read+execute permission only for the group and others.

    Now you have two choices. You can set the ownership of the directory to be the same user as apache runs as (usually "nobody"). Then you can restrict access to others to just read (744). Or you can say, what the heck there is not too much to get excited about with security over this directory and leave it at 777.

    BTW, if you want to get your head around linux further, then visit www.linuxdoc.org the Linux Documentation Project. There are some excellent books, guides and how to's there for dl.
    Last edited by freakysid; Jun 9, 2001 at 20:43.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •