473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,322 developers and data experts.

Uploading files into a MySQL database using PHP

Atli
5,058 Expert 4TB
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t.

In situations where your PHP application needs to store entire files, the preferred method is to save the file onto the server’s file-system, and store the physical location of the file in your database. This is generally considered to be the easiest and fastest way to store files.

However, you may find yourself in situations where you would want to keep the file itself with the other data in your database. This gives you - or rather: MySQL - complete control over the file data, rather than just the location of the file on the server.

There are some downsides to this method though, such as; decreased performance and added complexity to both your PHP code and your database structure. This is something you should carefully consider before using this in a real-life application.

Having said that, this article demonstrates how you can upload a file from a browser into MySQL, and how to send the files back to the browser.

Before you start
To get through this smoothly, you should be familiar with the following:
The battle plan
As with all programs, before we start writing we need to plan a little ahead. Just so we know what we are going to write before we write it.

Before we start on the program, we need to design the database. This is not a complex design, as we are not talking about creating some complex filing system. We only need a single table, containing a BLOB field for our file and various other fields to store information on our file, such as name, size, type.

Now then. The first phase of the program is getting the file from our users onto the server where our PHP can interact with it. This is the simplest part of the process, requiring only a basic HTML form.

The second phase involves reading the uploaded file, making sure it was uploaded successfully and adding it to the database. This is a similar process as the one used when uploading a file to the file-system, but using the MySQL functions rather than the file-system functions.

The third phase is to list all files that have been uploaded and saved on the database, with a link so it can be downloaded. The only problem here would be the fact that the file does not exists on the server, so how do we create a link to it? That is a problem handled by phase 4, all we need to do in phase 3 is create a link with the ID of the file to be downloaded embedded in the URL.

The fourth, and final, part is the one that is most confusing about this process. The part where we fetch the file and send it to the client's browser.
We start by using the MySQL functions, and the ID sent by phase 3, to fetch the file data from the database. Then we set a few headers, letting the browser know what to expect, before finally sending the contents of the file.

Now, using this summary as a guide, lets start writing our program.

Phase 0: Building a database
The database is simple. One table with a BLOB field for the file data and a few fields for various pieces of information relating to the file:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `file` (
  2.     `id`        Int Unsigned Not Null Auto_Increment,
  3.     `name`      VarChar(255) Not Null Default 'Untitled.txt',
  4.     `mime`      VarChar(50) Not Null Default 'text/plain',
  5.     `size`      BigInt Unsigned Not Null Default 0,
  6.     `data`      MediumBlob Not Null,
  7.     `created`   DateTime Not Null,
  8.     PRIMARY KEY (`id`)
  9. )
  10.  
As you see, we store the file name, including the extension.
We have the mime type, which we use to let the browser know what kind of file we are dealing with.
The size of the file in bytes.
And finally the data itself, in a MediumBlob field.

Phase 1: Uploading the file
Now, we need to get the file from the user. The table we designed does not require any additional information from the user, so we will make this simple and create a HTML form with only a single "file" input field and a submit button:
Expand|Select|Wrap|Line Numbers
  1. <!DOCTYPE html>
  2. <head>
  3.     <title>MySQL file upload example</title>
  4.     <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  5. </head>
  6. <body>
  7.     <form action="add_file.php" method="post" enctype="multipart/form-data">
  8.         <input type="file" name="uploaded_file"><br>
  9.         <input type="submit" value="Upload file">
  10.     </form>
  11.     <p>
  12.         <a href="list_files.php">See all files</a>
  13.     </p>
  14. </body>
  15. </html>
Note the third attribute of the <form> element, "enctype". This tells the browser how to send the form data to the server. As it is, when sending files, this must be set to "multipart/form-data".
If it is set any other way, or not set at all, your file is probably not going to be transmitted correctly.

At the bottom, we have a link to the list we will create in phase 3.

Phase 2: Add the file to the database
In the form we built in phase 1, we set the action property to "add_file.php". This is the file we are going to build it this phase of the process.

This file needs to check if a file has been uploaded, make sure it was uploaded without errors, and add it to the database:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // Check if a file has been uploaded
  3. if(isset($_FILES['uploaded_file'])) {
  4.     // Make sure the file was sent without errors
  5.     if($_FILES['uploaded_file']['error'] == 0) {
  6.         // Connect to the database
  7.         $dbLink = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');
  8.         if(mysqli_connect_errno()) {
  9.             die("MySQL connection failed: ". mysqli_connect_error());
  10.         }
  11.  
  12.         // Gather all required data
  13.         $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
  14.         $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
  15.         $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
  16.         $size = intval($_FILES['uploaded_file']['size']);
  17.  
  18.         // Create the SQL query
  19.         $query = "
  20.             INSERT INTO `file` (
  21.                 `name`, `mime`, `size`, `data`, `created`
  22.             )
  23.             VALUES (
  24.                 '{$name}', '{$mime}', {$size}, '{$data}', NOW()
  25.             )";
  26.  
  27.         // Execute the query
  28.         $result = $dbLink->query($query);
  29.  
  30.         // Check if it was successfull
  31.         if($result) {
  32.             echo 'Success! Your file was successfully added!';
  33.         }
  34.         else {
  35.             echo 'Error! Failed to insert the file'
  36.                . "<pre>{$dbLink->error}</pre>";
  37.         }
  38.     }
  39.     else {
  40.         echo 'An error accured while the file was being uploaded. '
  41.            . 'Error code: '. intval($_FILES['uploaded_file']['error']);
  42.     }
  43.  
  44.     // Close the mysql connection
  45.     $dbLink->close();
  46. }
  47. else {
  48.     echo 'Error! A file was not sent!';
  49. }
  50.  
  51. // Echo a link back to the main page
  52. echo '<p>Click <a href="index.html">here</a> to go back</p>';
  53. ?>
  54.  
  55.  
Phase 3: Listing all existing files
So, now that we have a couple of files in our database, we need to create a list of files and link them so they can be downloaded:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // Connect to the database
  3. $dbLink = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');
  4. if(mysqli_connect_errno()) {
  5.     die("MySQL connection failed: ". mysqli_connect_error());
  6. }
  7.  
  8. // Query for a list of all existing files
  9. $sql = 'SELECT `id`, `name`, `mime`, `size`, `created` FROM `file`';
  10. $result = $dbLink->query($sql);
  11.  
  12. // Check if it was successfull
  13. if($result) {
  14.     // Make sure there are some files in there
  15.     if($result->num_rows == 0) {
  16.         echo '<p>There are no files in the database</p>';
  17.     }
  18.     else {
  19.         // Print the top of a table
  20.         echo '<table width="100%">
  21.                 <tr>
  22.                     <td><b>Name</b></td>
  23.                     <td><b>Mime</b></td>
  24.                     <td><b>Size (bytes)</b></td>
  25.                     <td><b>Created</b></td>
  26.                     <td><b>&nbsp;</b></td>
  27.                 </tr>';
  28.  
  29.         // Print each file
  30.         while($row = $result->fetch_assoc()) {
  31.             echo "
  32.                 <tr>
  33.                     <td>{$row['name']}</td>
  34.                     <td>{$row['mime']}</td>
  35.                     <td>{$row['size']}</td>
  36.                     <td>{$row['created']}</td>
  37.                     <td><a href='get_file.php?id={$row['id']}'>Download</a></td>
  38.                 </tr>";
  39.         }
  40.  
  41.         // Close table
  42.         echo '</table>';
  43.     }
  44.  
  45.     // Free the result
  46.     $result->free();
  47. }
  48. else
  49. {
  50.     echo 'Error! SQL query failed:';
  51.     echo "<pre>{$dbLink->error}</pre>";
  52. }
  53.  
  54. // Close the mysql connection
  55. $dbLink->close();
  56. ?>
Phase 4: Downloading a file
This part is the one that usually causes the most confusion.

To really understand how this works, you must understand how your browser downloads files. When a browser requests a file from a HTTP server, the server response will include information on what exactly it contains. These bits of information are called headers. The headers usually include information on the type of data being sent, the size of the response, and in the case of files, the name of the file.

There are of course a lot of other headers, which I will not cover here, but it is worth looking into!

Now, this code. We start simply by reading the ID sent by the link in phase 3. If the ID is valid, we fetch the information on the file who's ID we received, send the headers, and finally send the file data:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // Make sure an ID was passed
  3. if(isset($_GET['id'])) {
  4. // Get the ID
  5.     $id = intval($_GET['id']);
  6.  
  7.     // Make sure the ID is in fact a valid ID
  8.     if($id <= 0) {
  9.         die('The ID is invalid!');
  10.     }
  11.     else {
  12.         // Connect to the database
  13.         $dbLink = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');
  14.         if(mysqli_connect_errno()) {
  15.             die("MySQL connection failed: ". mysqli_connect_error());
  16.         }
  17.  
  18.         // Fetch the file information
  19.         $query = "
  20.             SELECT `mime`, `name`, `size`, `data`
  21.             FROM `file`
  22.             WHERE `id` = {$id}";
  23.         $result = $dbLink->query($query);
  24.  
  25.         if($result) {
  26.             // Make sure the result is valid
  27.             if($result->num_rows == 1) {
  28.             // Get the row
  29.                 $row = mysqli_fetch_assoc($result);
  30.  
  31.                 // Print headers
  32.                 header("Content-Type: ". $row['mime']);
  33.                 header("Content-Length: ". $row['size']);
  34.                 header("Content-Disposition: attachment; filename=". $row['name']);
  35.  
  36.                 // Print data
  37.                 echo $row['data'];
  38.             }
  39.             else {
  40.                 echo 'Error! No image exists with that ID.';
  41.             }
  42.  
  43.             // Free the mysqli resources
  44.             @mysqli_free_result($result);
  45.         }
  46.         else {
  47.             echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
  48.         }
  49.         @mysqli_close($dbLink);
  50.     }
  51. }
  52. else {
  53.     echo 'Error! No ID was passed.';
  54. }
  55. ?>
Any decent browser should be able to read the headers and understand what type of file this is, and that it is to be downloaded, not opened.

The finish line
So, as you see, this is not as complex as one might think.

This code is of course only written for demonstration purposes and I would not recommend using it without adding a little extra security. Un-edited, this code would basically allow anybody to upload anything to your server, which is not a good idea!

I hope this has been helpful, and I wish you all the best.

See you around,
- Atli Þór

Revisions
  • August 20th, 2008 - Replaced the old mysql functions with the improved mysqli functions.
  • December 12th, 2009 - Updated the introduction to include a bit more detail on the pros and cons of this method. Also improved the code structure a bit. Replaced the mysqli procedural functions with their OOP counterparts. (Thanks to kovik for pointing out the need for these changes!)
Nov 23 '07
221 366601
roseple
26
Ok, and thank you very much on all your help.
Ooops, I just want to ask... Is this uploading codes can support mp4 and mp3 files? I already increase my upload and post_max_size.

Thanks..
Jun 19 '09 #51
Atli
5,058 Expert 4TB
This code can upload any file, regardless of size, type or extension.

If your PHP and MySQL setup is set up to handle large file uploads, then this code can process them.
Jun 19 '09 #52
Hello..

Can anyone know why this error is present:
Fatal error: Call to undefined function mysqli_connect() in c:\Inetpub\wwwroot\uploading\connect.php on line 20
Code in line 20:
Expand|Select|Wrap|Line Numbers
  1. $dbLink = mysqli_connect('localhost', 'root', 'ai3', 'filestorage');
Thanks..
Jun 22 '09 #53
Hello..

Why does the error "Error, Failed to insert the File."

Thank you..
Jun 22 '09 #54
Atli
5,058 Expert 4TB
Hi, apple.
@apple08
This error occurs when you try to use a function belonging to an extension that isn't loaded (or doesn't exist).

In this case, your PHP installation is missing the Improved MySQL extension
See the Installation page in the manual of instructions on how to enable the extension.

You can also rewrite the code to use the old MySQL extension, if that is available in your installation.
You would just have to rewrite the connection part and remove the "i" from the other functions.

@apple08
That is the error I wrote into my example code in case the SQL query that is supposed to insert the file failed.
It should have printed additional information that would be good to have if you want our help debugging this.
Jun 22 '09 #55
Hello,

To be specific, the error I encountered was " Error, failed to insert the File. MySQL server has gone away." There are also times that the error was " Error, failed to insert the File. Got a packet larger than the max_allowed_packet bytes."

That said errors only occur when I tried to upload a file larger than 2Mb.

Thank you.
Jun 23 '09 #56
Atli
5,058 Expert 4TB
That would indicate that you are sending more data than MySQL is willing to accept.

You need to alter the MySQL configuration so that it can accept more data.
The "max_allowed_packet" directive is a good place to start.

The MySQL configuration file is named "my.cnf" (or "my.ini", on Windows).
It is usually just under the MySQL installation dir, although that can vary based on the installation type.
Jun 23 '09 #57
Hello, do you guys know how I can make the list of my uploaded files become a thumbmails instead of table list?

Thanks.
Jun 25 '09 #58
roseple
26
Hi...

I just want to ask why does my server is too slow whenever I upload a quite large fie. Is it because I save the file to the database.

Thank you....
Jun 25 '09 #59
msei
6
Hi,

I have a question about the list files, Is there a way to list only the field TYPE ='FYR' instead list all the record. Any help will be appreciated.

Thank you in advance.

msei
Jun 30 '09 #60
Atli
5,058 Expert 4TB
Hi.

If you added an additional field named TYPE, then you would just have to add a where clause to the SQL query specifying the values you want listed.
Expand|Select|Wrap|Line Numbers
  1. SELECT ... FROM `FileStorage` WHERE `TYPE` = 'FYR'
Jun 30 '09 #61
Hi Atli. I tried pasting your codings to my final project and it worked just fine. However, how can i download the file and open them successfully especially with .doc files? Sorry i need to know the step by step procedures. I tried following the phase 4 but still couldn't understand. Thanks in advance.
Aug 9 '09 #62
Atli
5,058 Expert 4TB
Hi xaralee.

The code in phase #4 should successfully send *any* type of file back to the browser.
This is obviously dependent on whether or not the browser sent the correct mime-type and extension when you uploaded the file, but I doubt any decent browser would fail to do that with a Word document.

How the browser actually handles the downloaded file is not something this code can control. If you want the browser to open the .doc file in Word, then you will have to tell your browser to do that.

Most browsers give you a choice when downloading a file, whether to open it, and which application to use, or whether to download it.
Aug 12 '09 #63
@Atli
Hi Atli. I solved the problem already. Thank you for your advice. (:
Aug 12 '09 #64
Frinavale
9,735 Expert Mod 8TB
Atli this is an awesome article :)
You may want to include a links to the resources that you're using (like the mysqli extension) though :)
Sep 2 '09 #65
Atli
5,058 Expert 4TB
@Frinavale
Thanks ;-)
Good idea. I've added links to the "Before you start" part, to begin with.
Might be worth adding a separate reference part tho.
Sep 11 '09 #66
msei
6
Hi Atli,

I did everything following the steps provide to do the upload and download file and it was working fine, but for some reason I tried to download a file it keep giving me the following error message "Adobe Reader could not open file.pdf' because it is either not a support file type or because the file has been damaged." I did download the Adobe Reader 9.0 and still giving me the same error message as I open the file. But for some reason if I open the file without download from the fle list, it does open fine. Any idea what am I doing wrong? Any help will be appreciated.

Thank you in advance.

Xiou
Sep 13 '09 #67
Atli
5,058 Expert 4TB
Hey Xiou.

What browser are you using to test this?
Which versions of PHP, MySQL, and what OS is your server running?

I just tested this on my PC and there were no problems uploading and reading PDF files in any of the major browsers. I'm using Adobe Reader 9.1.

My server specs:
Win7 (x64), Apache 2.2, PHP 5.3, MySQL 5.1

It sounds like your file is being corrupted somewhere on the way tho.
Maybe some sort of charset conversion problem? I can't really say.
Sep 15 '09 #68
xiou
3
I need a help in how to upload 2 files instead 1? thank you in advance. Any help will be appreciated
Oct 19 '09 #69
Atli
5,058 Expert 4TB
Hey.

To upload more than one file, you add more <input> elements, each with a different name.
Expand|Select|Wrap|Line Numbers
  1. <input type="file" name="uploaded_file_1" /><br />
  2. <input type="file" name="uploaded_file_2" /><br />
  3. <input type="file" name="uploaded_file_N" /><br />
  4.  
And then you grab each file from the $_FILES array using it's name:
Expand|Select|Wrap|Line Numbers
  1. $file1 = $_FILES["uploaded_file_1"];
  2. $file2 = $_FILES["uploaded_file_2"];
  3. $fileN = $_FILES["uploaded_file_N"];
  4.  
And then run each of them through the code that inserts it into the database.
You can make this less repetitive by putting the files into an array and looping through it.

There is also a way to upload them as a array, by using the same name for all the <input> boxes, adding brackets to the end [], but that is not necessary for a static form. (More useful for forms that expand using client-side code.)
Oct 19 '09 #70
kovik
1,044 Expert 1GB
The server has complete control over the access of files in its filesystem. The only time that using the database provides "extra security" is if you have no control over the server (i.e. residing on a shared server) or your security layer is based solely on a third-party controller (i.e. a server-side user system).

However, in those cases, you could simply store a pointer to the file (i.e. the filename) in the database, block the files from regular access using .htaccess, and make the files only accessible via another script.

All in all, there's nothing that a BLOB can do that the file system and a file pointer cannot. I feel that if you are going to advocate a method that goes against the accepted methods of development that you should provide more justification for doing so, or at least a set of pros and cons.
Dec 11 '09 #71
Atli
5,058 Expert 4TB
Hey kovik.

The only time that using the database provides "extra security" is if you have no control over the server...
I don't know who you are quoting there, but is sure isn't me.

I never said this provided "extra security". I said it gave you more control, which it does. It makes you (or rather; MySQL) responsible for handling and storing the file data, instead of passing that responsibility on to the OS.

And for the record, if you have no control over the server, a database won't be any more secure than the file-system. They will both be just about equally insecure.

The server has complete control over the access of files in its filesystem.
Sure, but different types of servers have different types of control.
How OSs handle files varies somewhat. MySQL, on the other hand, handles the same across all the OSs (or at least handles predictably across OSs.). In some situations, not having to worry about the various OS/file-system quirks could be of great help.

All in all, there's nothing that a BLOB can do that the file system and a file pointer cannot.
And I never suggested otherwise. But even tho the same things can be achieved using both systems, how they are achieved is not the same, and sometimes one method may fit your needs better than the other.

For example; backing up the files when they are inside MySQL is more convinient then when they are on the file-system. You can just dump all the data, files included, into a single file, whereas if the files or on the file-system, the databases and the files have to be backed up separately.

I feel that if you are going to advocate a method that goes against the accepted methods of development that you should provide more justification for doing so...
Allow me to quote the very first paragraph of the article:
You may be asking yourself: "Why put files inside my database? Why not just put them on the file-system?".
In most cases, that is exactly what you should do. It's simple, effective, and requires very little effort on your part.
I am not advocating the use of this method. I'm only explaining it. Whether or not you want to use it is completely up to you.

And, by the way, there are no "accepted methods of development" written in stone anywhere.
Which method you should use depends on what you want to achieve and it just so happens that the file-system method fits better in most cases. But it is by no means superior in all situations.
Dec 12 '09 #72
kovik
1,044 Expert 1GB
I base what is "accepted" by what I typically hear respected members of web development communities like PHPDN and SitePoint say. So no, they are not set in stone, but they do exist. The file server handles files. The database handles data. That's pretty must the gist of it.

And I saw your disclaimer, but it doesn't say anything about the weaknesses of this method or the strengths of the most common method of file pointers in the database and actual files residing on the server. My main concern is that you, the author, have the "Moderator" tag associated with your name, which automatically gives you a large amount of credibility, meaning that newcomers may blindly follow this advice without knowing the implications. When pushing uncommon methodology, one should explain why they feel it is necessary. A broad statement of "special circumstances" and "a little more control" isn't exactly acceptable.

Also, a few other issues I have is the coding style of the example code and the security aspect of this method. The most used standards in open source development for PHP would have to the Zend PHP coding standards, which decrease the LOC, add more whitespace, and increase readability (though the last part is purely opinion). Conforming to the standards makes code easier to read as long as we all agree to conform. And, as for the security aspect, I'm not so sure about the integrity of the MIME type that is detected in the $_FILES array. I've had times when, for example, the $_FILES array determined that a file was an image/jpeg, but getimagesize() determined the MIME type to be image/pjpeg (special Photoshop image type).
Dec 12 '09 #73
Atli
5,058 Expert 4TB
Thanks for the pointers.
I do agree with what you are saying for the most part. I should of course have explained the downsides better, if only to be thorough. I wrote this article more than two years ago, and I've barely edited it since then. Maybe it's about time to do that :)

I don't necessarily agree that the code examples should be in Zend format, though. It should of course be properly formatted, but whether it is in Zend format, or even the standard .Net format, doesn't really matter, as long as it is readable.
And I, personally, don't like some of the rules the Zend standard defines, so I would be inclined not to include them in my examples.

And, as for the security aspect, I'm not so sure about the integrity of the MIME type that is detected in the $_FILES array. I've had times when, for example, the $_FILES array determined that a file was an image/jpeg, but getimagesize() determined the MIME type to be image/pjpeg (special Photoshop image type).
True, but this topic could be an article in itself, so I'm reluctant to clutter the code - which is only mean to demonstrate the file upload process - with stuff to fix this. Since there aren't any limitations on what you can upload in the examples anyways, verifying the mime type seems a bit pointless.
Should add a warning about this though.


But, anyhow. I've updated the article. Added a bit of detail to the intro and re-formatted the examples a little. Thanks for the input! Is very helpful :-)
Dec 12 '09 #74
kovik
1,044 Expert 1GB
Very nice. Those changes added a lot more professionalism to the article. ^.^

My only problem (is that the right word?) now is that you don't really warn the user about the usage of the "improved" mysqli extension usage. Every server that runs PHP and MySQL will undoubtedly have the mysql extension dll loaded, but the mysqli extension may be overlooked. I see that you give a link, but it's not exactly obvious. As a reader, I skipped over the introductory links, and I can picture other readers doing the same.

An improvement on this article could be embedding the reference links inside of the text. References to the mysqli extension information, maybe to W3C's definition of the "multipart/form-data" encoding type, etc.

Sorry if I'm being too demanding. :P
Dec 12 '09 #75
MOBCOM
4
Hello Atli, Kovik,

I may actually have a situation where using mysql blobs is preferrable to filesystem for unsearchable binary data.

We have been using filesystem to store photos and various formats of videos, but now are looking into mysql for mail attachments.

The three reasons we consider mysql now are these (control and security):

1. Whereas the web-accessible photos and videos are post-formated versions (converted to new dimensions and renamed), thus any exploit code from uploads is removed and can not be activated by access through direct url.

2. Mail attachments are in all formats and not sanitized like photos and videos, thus creating an increased possibility of 0-day exploit code being uploaded that will be accessible by web url, if filesystem is used.

3. Mail attachments may require additional security of not being accessible through web urls by anyone other than the attachment owner (at least long term when session key has expired). And though we use sha512+filedate+salt for photos/videos urls, filesystem storage would still leave the weakness of files being always accessible in future by everyone once the constant file url is known whereas mysql storage can be done through a php file requiring log in.

"Solution" would have been to store the mail attachments in directories that are not web accessible, e.g. /home/securefiles/topsecret.pdf and include the file through php script, but this creates the problem of how to include the file from remote servers - creating the vulnerability of permanent remote urls again.

Whereas mysql database provides a central authentication and security mechanism that can be accessed from remote servers and is not publicly web-accessible.

If you can think of a filesystem storage alternative that provides the security and authentication mechanisms of mysql storage in a multi-server environment with web-accessible urls, let me know, otherwise this is a situation where mysql storage is preferable to filesystem storage.
Jan 21 '10 #76
kovik
1,044 Expert 1GB
@MOBCOM
... Say that again, possibly without the use of the word "whereas" with the word "thus." Also, "exploits" are when one takes advantage of a flaw. If there is a flaw in your code, then it won't matter which medium you use to store the malicious data. It is still malicious.

@MOBCOM
Not sanitized? Why do you say that? It is up to the mailing application to handle that. Smart companies like Google and Yahoo sanitize their outgoing mail to ensure they do not get blacklisted, and validate incoming mail for the user's safety.

@MOBCOM
You are grossly underestimating the power of a server. MySQL is only an application run by the server; the server itself is by no means weak or insecure. Your server has the power to allow or deny ANY request.

@MOBCOM
If you can retrieve the file from a database using a script, what is stopping you from retrieving the file from your file system using a script?

@MOBCOM
You want remote servers to have access to these files?

@MOBCOM
The multi-server environment is the only time that MySQL would be the "simpler" option. That doesn't mean that MySQL is the only, or the smartest, option.

You state that using MySQL's authentication allows remote servers to connect to the database. You also state that this provides security. However, a server can tell where a request is coming from. MySQL's authentication only cares about whether the requester knows the correct credentials. In order for this data to be accessed, the requester only needs to discover these credentials. If this was handled by the server, the server could accept requests from a white-list and deny all others. Then, it's a matter of whether it is easier to discover your MySQL login details or to compromise your server and alter your white-list.
Jan 21 '10 #77
MOBCOM
4
@kovik
My statement's wording is correct. It's not the php code that is the issue, the permissions of the uploaded file can be easily set to be non-executable (heard of chmod?). The issue is that historically there have been plenty of attacks which exploit vulnerabilities on the server level.


@kovik
Because by nature it is possible to "sanitize" photos and videos by e.g. re-converting them to a different format. It is not possible to do this for the hundreds of other file formats that can be uploaded as attachments.

@kovik
oh come on, Google's idea of validation is not allowing .exe files to be sent. Yahoo and Hotmail use some crappy anti-virus to scan their attachments which is as useful/useless as airport security.

If files are accessed through direct url, then anti-virus isn't even deployed. And anti-virus is completely useless for detecting 0-day exploits which was my whole point, and the point of a 0-day exploit is not to be detected by anti-virus, nothing to do with someone's code (those are - code vulnerabilities, sql injection).


@kovik
My point was - some things are better to be authenticated on server layer and some on application layer, because when a vulnerability on application layer is discovered (web application/php/mysql), it won't necessarily compromise your server, whereas when server level authentication gets compromised your mysql/php setup goes too. The mysql storage keeps things at application layer (chrooted), protecting server layer from attacks. Also, no high-load production servers use SElinux and there have been cases where SElinux has been the cause of vulnerabilities.

There's an excellent paper by Ken Thompson from 1984 about open code being secure because everyone can read it (NSA being major supporter of SElinux): http://cm.bell-labs.com/who/ken/trust.html

@kovik
I'm talking about a multi-server/multi-DC situation, big sites where everything isn't one one box. Again, read my application layer vs server layer point above. If server level authentication is used that is shared across servers / datacenters and becomes vulnerable then all servers become vulnerable if application layer authentication is used then vulnerability is limited to application layer - now when you consider that chroot is used for php and mysql + data in mysql is non-executable no matter the file permission and dangerous commands in php have been disabled, then the application layer on a production server can be configured to have by multitudes higher security.

At MOB we only offer the highest level of security, and the faux-security that mass-oriented products like Yahoo, Hotmail, Gmail offer you is not acceptable!

With the exception of high security requiring setups, it has long been common knowledge that databases should be used for data only and for everything else filesystem should be the preferred setup, as it's faster, uses significantly less resources, and is easier to back-up.

Then again, times are changing, as the Oracle guru Tom Kyte says: "You have my advice, you want data protected, secure, manageable, restored to a point in time -- you be using a database."
Jan 21 '10 #78
kovik
1,044 Expert 1GB
In regards to sanitation, you still haven't explained how the database has an advantage over the file system.

Also, you mentioned direct URL access again. Using the database, you would not want direct file access. The server can accomplish this in a similar matter that the database does: by placing the data in a non-public area and then loading it using a script.

As for whether vulnerability lies at the server level or the application level, how do you plan on this being a reality? I'm assuming that by this, you are referring to something along the lines of a user uploading a PHP script to your server, and then running it. In what way does the placement of this data determine where your vulnerabilities lie? Allowing a vulnerability like this, regardless of the where it resides, should result in the same difficulties.

I also don't see any justification of your bias against your competitors other than that they are your competitors. Google has made the "gmail.com" domain a safe one to receive e-mail from. Google's, Yahoo's, and Microsoft's virus scanning, while only preliminary, helps to block known viruses and keep users aware of the risks when downloading files.
Jan 22 '10 #79
MOBCOM
4
@kovik
Re-read my previous post.

@kovik
My justified bias was not against competitors, but against your claim that "Smart companies like Google and Yahoo" are secure. I'll say again - No ESP can make the attachments they forward 100% secure as almost all attachment formats (jpeg,png,gif,pdf,js,flash,etc.) can contain (encrypted,obfuscated) malicious code, so anti-virus and disabling .exe attachments is faux-security to make average computer users feel secure.

But I was talking about best storage for server security, not end-user security where db/fs would be equally (un)secure.

And one of the companies you named (the smarter one), also stores attachments in database.


Example: You access your mail over wifi in a public internet cafe, though you access it over https, a man-in-the-middle that eavesdrops on your connection, will still see the urls of your attachments, and be able to directly access them, because they're files, with direct url, whereas if they're accessed through php files that authenticate privileges before even getting the file data from db, you are secure.
Now before you reply, figure out why you can't use php authentication the same way for files stored on filesystem.

Another example: We tested with a direct link of a photo that was received with Hotmail as attachment over 3 months ago and immediately deleted, the photo is still stored on Hotmail servers and accessible through that link.
Jan 22 '10 #80
kovik
1,044 Expert 1GB
I re-read your post more than once before responding. You aren't measuring the file system against the database on a level field. I'm starting to feel that maybe you need to re-read MY post.

You state that the database adds extra security because files are not publicly accessible via a URL. But for that to be an advantage, that would mean using the file system makes files publicly accessible. You and I both know that files on the file system do not *have* to be publicly accessible in order to be used.


Allow me to clarify for you.

Example: You access your mail over wifi in a public internet cafe, though you access it over https, a man-in-the-middle that eavesdrops on your connection, will still see the urls of your attachments, and be able to directly access them, because they're files, with direct url, whereas if they're accessed through php files that authenticate privileges before even getting the file data from db, you are secure.
Do you see the flaw in your logic? You are implying that PHP can communicate with the database, but that it cannot communicate with the file system. This is false. Your "extra" security is coming from PHP, not from the database.
Jan 22 '10 #81
MOBCOM
4
@kovik
Now You are simply twisting what I said by using the word "implying". I can just as well say you are implying that you agree with me and are out of arguments.

The extra security, among other factors, comes from MySQL always handling the input as data. When you store data on filesystem it is handled as a file, which can be an executable file, depending on permissions. Data stored in MySQL is never handled as executable file by the server.

Lets say you let your visitor upload a file named "virus.txt" to "/home/secure/virus.txt". Then in future a remote code execution vulnerability is found in PHP/Apache/Kernel/SELinux/FTP/Cups/Anti-virus that lets the attacker execute arbitrary code from files located on the server, like virus.txt, just by knowing the file's path on the server, whereas when the upload would be stored in mysql as data, this type of very common attack becomes by multitudes more difficult, pretty close to impossible actually. Do you understand now?
Jan 23 '10 #82
Atli
5,058 Expert 4TB
The extra security the database provides comes from the fact that a database exists on a "deeper" layer than the file system.

Meaning; a file on the file system exists in the same "scope" as the server itself, and by extension, the PHP scripts. And as MOBCOM says, a server-level vulnerability, or even a vulnerability in a PHP scrip, would provide an attacker direct access to the files on the file-system.

A file inside a database, on the other hand, would not be *as* vulnerable. Even a OS-level code-injection attack would require database access in order to manipulate the files stored there, and even then it would not be able to execute them directly. A server-level file-execution attack, which could easily execute a file on the file-system, would have no chance to execute files stored in a database.

As to which method makes files safer from unwanted viewers, that depends. A vulnerability in the PHP application would make them equally unsafe. But, again, a vulnerability in the server or the OS would give an attacker direct access to the file-system, but it would still require database access to fetch the file from the database. (Which I guess it could get form the file-system, granted.)

So in that case the database would be marginally safer.
Jan 23 '10 #83
Atli
5,058 Expert 4TB
@kovik
No true. MySQL users can be - and should be - limited to a specific domain/IP-range.
Jan 23 '10 #84
kovik
1,044 Expert 1GB
@MOBCOM
Yes, I do. I didn't consider the vulnerability issues beyond public access. So you're right. That would be an advantage of the database versus the file system. In order to exploit a flaw, one would need access to both the server and the database rather than just the server. And, to run a file from the database, one would need to be able to access the database, retrieve the file data, and then run it using a script that has to exist on the server level instead of just running a file on the server level.

Looks like Atli has something to add to his article, now. :P
Jan 23 '10 #85
kovik
1,044 Expert 1GB
@Atli
Wouldn't that be handled at the server-level, though?
Jan 23 '10 #86
Atli
5,058 Expert 4TB
@kovik
I would assume MySQL gets the location of the connection from the OS, just as a HTTP server would. However, this makes MySQL at least equally secure in that regard as the HTTP server.

Simply knowing the MySQL login credentials is not enough. You need to be connecting from a valid location, which in the case of most web-based applications should be either localhost or an in-house network, neither of which is exactly easy to fake. (Remote addresses can be faked easily enough, sure, but it would take something extra to fake a network/localhost connection.)
Jan 24 '10 #87
kovik
1,044 Expert 1GB
My curiosity caused me to take a look into this, and the connections are handled by sockets. I know what sockets are used for, but no idea how (regardless of how much I read). I feel like I'm getting off-topic though. lol
Jan 24 '10 #88
Hello Fiends...

I follow all steps mentioned in this tutorials...All things are going fine..
But i have one problem When i download the file(it is downloaded no problem) but when i open it then file is not opening..Like suppose i have downloaded an image then after download if i try to open it erro is photogallry can not open this,may be file format is not supported..I am using window vista..So plz tell me where i am falling wrong
Jan 24 '10 #89
i posted one problem regarding opening downladed file error(not opening image)..i am not finding it..where it is
Jan 24 '10 #90
Atli
5,058 Expert 4TB
Hey itsmenarwal.

@itsmenarwal
That would be your first problem ;-)

Seriously though. I would guess that something - most likely a warning - is being printed with your image data, thus corrupting it.

Try commenting out the Content-type header. That would make the file data print to the browser window, along with whatever is corrupting it. That way you can spot the error and fix it.

You can also try just silencing all errors by adding the following to the top of your page:
Expand|Select|Wrap|Line Numbers
  1. ini_set('display_errors', false);
  2. error_reporting(0);
i posted one problem regarding opening downladed file error(not opening image)..i am not finding it..where it is
Posts from new members (under 5 posts) go through extra spam-filtering, so they may not always be immediately visible. There should have been a notice about that when you posted it. - In any case, it seems to be visible now.
Jan 24 '10 #91
@ati

First of all i want to say thanks to you for such good tutorials.Really they help new guies very much.I just started php i found this link for uploading files..It is really appriciable.Keep it up..Kepp going bro...


I dont know much about headers but from ur post i cam to know that to see the data on client browser server has to send some inforamtion with the file which tell its type etc..The headers which i used are..

Expand|Select|Wrap|Line Numbers
  1. header("Content-Type: ". $row['mime']);
  2. header("Content-Length: ". $row['size']);
  3.  header("Content-Disposition: attachment; filename=". $row['name']);
This time i tried to open a pdf file rather than jpeg file.I have acrobat reader installed in my system.


And Now it is showing error that ""Adobe reader can not open 'ajex.pdf because it is either not supported file type or beacuse the file has been damaged.It was sent as email attachment and was not corectly decoded"

I also tried silencer code at the top but below <?php but nothing happening..
Now tell me friends how i can overcome from this problem
Jan 24 '10 #92
Atli
5,058 Expert 4TB
Try commenting out the headers.
Expand|Select|Wrap|Line Numbers
  1. /*header("Content-Type: ". $row['mime']);
  2. header("Content-Length: ". $row['size']);
  3. header("Content-Disposition: attachment; filename=". $row['name']);*/
And then open any file.

This should spill all the raw data for the file into the browse window as text. If there is indeed an error corrupting it, it should display with the data. Then it is just a matter of correcting it.

If that turns out not to be the problem, it gets a little more complicated.
Jan 25 '10 #93
i Tried to comment the above headers -> no error is displaying by doing this simply xampp.imagename(just like temp name is displaying..Still i can download the file but file is not opening..Please help
Jan 25 '10 #94
RomeoX
25
hi there.

Could you please Atli fix my code. I tried to change your code from mysqli to mysql but I couldn't edited. there is a problem syntax error.

Here is the code for download page
Expand|Select|Wrap|Line Numbers
  1.  
  2. <?php 
  3. // Connect to the database 
  4. $dbLink = mysql_connect("localhost", "root", "") or die (mysql_error()); 
  5. mysql_select_db("library", $dbLink) or die(mysql_error()); 
  6.  
  7. // Query for a list of all existing files 
  8. $sql = 'SELECT id, name, mime, size, FROM pdf2'; 
  9.  
  10. $result = mysql_query($sql,$dbLink) 
  11. or die("Error! Query failed: <pre>". mysql_error($dbLink) ."</pre>"); 
  12.  
  13.  
  14. // Check if it was successfull 
  15. if($result) {
  16. if(mysql_num_rows($result) == 0) {
  17.         echo '<p>There are no files in the database</p>'; 
  18.         }
  19. else { 
  20. # Get the row 
  21. $row = mysql_fetch_assoc($result); 
  22.  
  23.         // Print the top of a table 
  24.         echo '<table width="100%"> 
  25.                 <tr> 
  26.                     <td><b>Name</b></td> 
  27.                     <td><b>Mime</b></td> 
  28.                     <td><b>Size (bytes)</b></td> 
  29.                     <td><b>Created</b></td> 
  30.                     <td><b>&nbsp;</b></td> 
  31.                 </tr>'; 
  32.  
  33.         // Print each file 
  34.         while($row = $result->fetch_assoc()) { 
  35.             echo " 
  36.                 <tr> 
  37.                     <td>{$row['name']}</td> 
  38.                     <td>{$row['mime']}</td> 
  39.                     <td>{$row['size']}</td> 
  40.                     <td>{$row['created']}</td> 
  41.                     <td><a href='get_file.php?id={$row['id']}'>Download</a></td> 
  42.                 </tr>"; 
  43.         } 
  44.  
  45.         // Close table 
  46.         echo '</table>'; 
  47.     } 
  48.  
  49.     // Free the result 
  50. @mysql_free_result($result); 
  51. else 
  52.     echo 'Error! SQL query failed:'; 
  53.     echo "<pre>{$dbLink->error}</pre>"; 
  54.  
  55. // Close the mysql connection 
  56. @mysql_close($dbLink); 
  57. ?> 
  58.  
Feb 27 '10 #95
Atli
5,058 Expert 4TB
Hey RomeoX.

If you need help debugging an error message, please include the error message in the post. That way we don't have to guess what it is.

However, your error is likely on line #34. The result of a mysql_query call is not an object. You can not use method calls on it like you can on the result object of a mysqli::query call. You should be using the mysql_fetch_assoc function instead.
Feb 27 '10 #96
RomeoX
25
Hi Atli, You are totally right. Now it's working fine. I changed the things that u told me about. Thanks a million.
Feb 28 '10 #97
Atli
5,058 Expert 4TB
Glad to hear that, RomeoX :-)

@itsmenarwal
Sorry to leave you hanging like that. I blame IE. (For no particular reason... It just seems fitting :P)

Anyways, if you are still having this problem, browsers usually print the image name when they receive an image that is corrupt. - What you need to do is find out why it is corrupted, and to do that you need the browser to display the image data as text. - That you can do by hunting down all header() calls in your code; comment them out or remove them, and add a single "header('content-type: text/plain');" anywhere before the image is printed.

Once you can view the image data as text, the warning/error (if that is indeed teh problem) should be clearly visible among the output. (It should be either before or after all the random characters).

Note, IE seems to have a problem with "text/plain" so you should do this using any other browser. (Firefox is excellent for this kind of stuff... and all kinds of stuff, really.)
Mar 1 '10 #98
santhanalakshmi
147 100+
hi,

I am facing a problem in phase 2.

Fatal error: Call to a member function real_escape_string() on a non-object in C:\xampp\htdocs\add_file.php on line 13


What should be my text file format for uploading ?
Apr 12 '10 #99
Dormilich
8,658 Expert Mod 8TB
the function is called mysql_real_escape_string()
Apr 12 '10 #100

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: hdf | last post by:
hi i have problems uploading my database using phpmyadmin n i want to load it using the ssh termal in setd of phpmyadmin. can anyone hlp
11
by: menmysql | last post by:
i am getting the following error while connecting mysql database using jsp java.sql.SQLException: Communication link failure: Bad handshake can any one tell what is the actual problem
8
by: menmysql | last post by:
i am not bale to solve this problem since two weeks i am trying to access records from mysql database using jsp. inside this jsp program i wrote all my JDBC code. it is working very nicely and...
9
by: christopher_board | last post by:
Hi all. I am trying to connect to a MySQL Database using PHP on a local machine using locahost. I am using the following code <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass =...
1
by: ganeshg | last post by:
Hai All, Please let me know how to insert audio files into mysql database using .net. Thanking you.....
3
by: Suresh P | last post by:
Hi All, I tried to access the mysql database in ODBC using ip address and username/password. It returns, "cannot connect to MySQL server on IP ADDRESS(10060)". This could be related to Firewall...
1
by: nadeenahmed | last post by:
I have Connected to a MySQL Database using a neatbeans editor. Now, I want to use that same database I created earlier on another pc. Can anyone help and tell me how that is done, please? Thank...
6
by: mfaisalwarraich | last post by:
Hi everyone, I am trying to add multiple pinpoint to google map using Lon/Lat. All addresses will be fetched from mysql database using PHP. I have looked at google and searched for it on...
1
by: santhanalakshmi | last post by:
Hi, I wrote some coding, to import excel data in mysql database using PHP Script? But my script, its not all inserting the excel data in mysql database. I don't know, what mistake did i made?...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.