Video database debate

PHP
1

Hi all - I tried posting elsewhere, but received no feedback. Thus I thought I’d try here…

I was hoping I could get some input from the community on a video database (using PHP/MYSQL) that I am designing - so let me give you a general overview. The site will house various flv files and their respective screenshots, and will be “owned” by a particular institution subscribing to our service. These videos can be uploaded as either public (where all institutions can view them) or private (only the institution’s user can view them). The files tend to be a minimum of 25 mb and generally should not exceed 100 mb.

I am having trouble deciding whether the best way to store the flv and corresponding screen shots is within the database or within a folder. I currently have the files being uploaded to a folder for the institution or a folder called public. I was going to look in to the feasibility of utilizing the htaccess to check the user’s session variables to decided whether or not they should have access to the folder. I am uncertain how to do this. I have found ways to check the database for an inputed username and password, but not how to ensure that the user is the proper level to access the folder. Moreover, this route requires an extra pop-up log-in box that I would rather not hassle the user with. On the other hand, I’m not sure how thousands of videos would be extracted from a database to be viewed… but I do see that it is easier to verify that the user is authorized to view the restricted content. Although I am having trouble converting the binary back in to an flv file when attempting to implement this method for testing.

I am looking forward to the community’s feedback on this issue, and hopefully for some light to be shed on the direction on the route to take with this. I am open to all comments and suggestions.

Thanks,

MRN

2

Hello,

As suggested, your best solution is to store the videos outside of the web directory. Store data about the videos in a database. I would store an id number (unique for each video), video name, video title, video length, video size, video owner, video file path (not to be seen by the public) and anything else that would enhance a search feature. In another table I would store permissions for each video. In that table I would store a row number (unique for each row), video id number (from first table), permission value (could be “yes” or “no”). I would also have a table for authentication. When a user tries to access a video within a private directory, you can check to see if they have access rights/permissions. If they do, pull the file path from the first table and use PHP to display the video in their browser. If they don’t, direct them to a permissions denied page.

Also, I suggest a file tree like this (hope this aligns properly):

Videos
|
|__Institution A
| |_public
| |_private
|
|__Institution B
| |_public
| |_private
|
|

The whole Videos directory will be outside of the web path which prevents people from accessing the videos with a direct URL. There are other options for adding security to the institution directories as well.

Just my 2 cents,
Noob

3

Thanks for the response, OOPNoob - I will look in to this method further as it seems to be the popular choice.

Thank you again to all those who replied tho this thread, your replies are much appreciated!!

4

Thanks for the response cranial-bore. I am not worried about authenticated users from retrieving the videos from their cache if they have access only to their own videos. My problem would be people accessing videos that do not belong to their user group. Do you know any good references to what you are speaking of with data served to the client via the file system (not sure if their is a “bible” so to speak on php other than the manual which most programmers refer to).

5

To ensure I understand correctly, PHP would be able to access a directory out side of the WWW directory. When you say pull from, would the video physically be pulled to the WWW side or can PHP tell the flv player to play a file outside the WWW directory. Sorry for the confusion, but I greatly appreciate your help.

6

The player won’t be able to access anything above the WWW folder. The player is client side software, just like a web browser, so no access to those files.
You can use a pass through PHP script which checks the credentials, and then reads the video file (from the server file system) and serves the data to the client, but I’m not sure how well that would work with video. There may be performance or memory implications to doing that with PHP (or there may not…I don’t know).

The solution may depend on how secure you need the videos. Authenticated users could always retrieve it from their cache or use other browser debuggers to discover its location.

7

You could put all the videos in a directory that is not web accessible (i.e. outside the DOCUMENT_ROOT) and put a script in the DOCUMENT_ROOT somewhere to send the file to the user, provided they are logged in and have access to that file.

Something along the lines of:


$video = Video::findByPk($_GET['videoId']); // Assuming activerecord
if ($member->loggedIn() && ($video->isPublic() || $member->hasAccess($video->institutionId))
{
   // send video to member
}
8

I can offer one suggestion for you: don’t store your video material in a database. Store it in folders on a filesystem, and keep the metadata in database only. Databases are not (in general) designed for sotring 25MB+ binary files.

9

Thank you for your response.
That was what I figured initially, but then I became concerned with how to secure the directories that will house the video data. Do you have any recommendations? I searched through Google but wasn’t sure the best way to go about this.