1

First of all, thanks for having a look at this. Allow me to explain:

I have images on disk that I'd like to place into the database in a longblob field. I've read that this is generally discouraged, but this is still something that is required in my application.

To import the images, I have the following code in my class:

$file_handle = fopen("$folder_path/$image","rb");
$file_content = fread($file_handle,filesize("$folder_path/$image"));

$image_record["file_name"] = $image;
$image_record["file_folder_id"] = $folder_data["folder_id"];
$image_record["file_owner_id"] = $this->current_user_data["user_id"];
$image_record["file_mime_type"] = mime_content_type("$folder_path/$image");
$image_record["file_binary"] = addslashes($file_content);

if($this->db_insert("file", $image_record))
{

    $this->view_file($this->load_file($image));
    echo header("Content-type:".$image_record["folder_type"]);
    $this->view_file($this->load_file($image));
    echo stripslashes($image_record["folder_binary"]);
}

It appears that when I try to save the binary to the database, the image data is getting corrupted by that process.

Using the same exact code, the $db->insert() statement works just fine on another website with near identical database schema except for the fact on the one it works, the storage engine is InnoDB instead of MyISAM.

The code within the if statement always displays an image, so I have my doubts about the data corruption happening at the time I read the file or display it.

Please help me understand what's going on.

Thanks!

user1623192
  • 11
  • 1
  • Why are you adding slashes? What does your actual database class do? – Brad Aug 24 '12 at 17:03
  • DB is a bad place to store images but have you made sure it is a blob? – Sammaye Aug 24 '12 at 17:05
  • It might be the field in your other database can't handle the right length of data. `BLOB` and `TEXT` fields are limited to 64KB by default which is not enough for most images. – tadman Aug 24 '12 at 17:06
  • 2
    What is happening in $this->db_insert? You can simply read the filedata and store that data in a blob, and use another field to store the rest of the image data... – Green Black Aug 24 '12 at 17:07
  • @user1623192, What are you talking about? `addslahes()` does nothing to escape your data. If you are using `addslashes()`, you need to go back and remove every instance of it and use a true escape function for your database. Better yet, use prepared queries and then you don't have to worry about it at all! `addslahes()` is no doubt where one of your problems is occurring. – Brad Aug 24 '12 at 17:18
  • I need to do addslashes to escape the data so it works in the INSERT statement $this->db_insert will ultimately write. I am currently using the longblob field on both my working and not working tables. $this->db_insert generates the MySQL Insert statement.In this case... insert into file (file_name,file_folder_id,file_owner_id,file_mime_type,file_binary) values ('outpost_e7d478377961aed8c25922b2be5dc9b6.jpg','4','3','application/octet-stream','BINARY_DATA_TOO_LONG_TO_FIT_HERE') – user1623192 Aug 24 '12 at 17:23
  • @Brad - addslashes wasn't my first choice for this either. I started with mysql_real_escape_string() and got the same result. – user1623192 Aug 24 '12 at 17:27
  • http://php.net/manual/en/pdo.lobs.php – Brad Aug 24 '12 at 17:42

1 Answers1

-1

You should base64 encode the images and store them in a longtext field, at leasts that is what i do and i find that it works great. Also, when grabbing the base64 records from your db, you can ectually echo out the base64 string into the images src property... here is the documentation: http://php.net/manual/en/function.base64-encode.php

Jeff Wooden
  • 5,339
  • 2
  • 19
  • 24
  • -1, there is absolutely no need to use text fields with base-64 encoded data. Blobs are meant for storing binary data, and encoding/decoding it this way is just inefficient. – Brad Aug 24 '12 at 17:18
  • @Brad both methods have pros and cons, i find working with base64 easier, especially since lately ive been programming html5 for phones and using canvas, etc... thanks for the -1 on your personal opinion, maybe you should look at this answer, which is just one of many: http://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data – Jeff Wooden Aug 24 '12 at 17:27
  • I'm not familiar with this technique. Is the inefficiency in question cripplingly painful, or is it just inefficient from an academic point of view? – user1623192 Aug 24 '12 at 17:31
  • It's not just an opinion, it is a factual statement. The question states that the database has a BLOB column. Therefore, a text-compatible representation of the data is not needed. Using base-64 encoding when it is not needed is a waste of storage space, and a (minor) waste of CPU for encoding. What you find to be easier for your work on the client-side is irrelevant to a data storage problem server-side. – Brad Aug 24 '12 at 17:35
  • @user1623192, Base-64 encoding requires about 33% more storage space than the raw binary data. Whether or not that is cripplingly painful depends on your use of it. – Brad Aug 24 '12 at 17:36
  • @user1623192, as my previous link suggested you're looking at 33% more in storage space, so depending on the nature of your application, you'll have to decide what solution works best for you. If you're working in JavaScript/HTML5, binary data becomes difficult to work with, but not impossible. Also, a more efficient method than base64 is to ASCII85, which you can get the PHP class for here: http://www.phpkode.com/source/s/ascii--binary-to-text/ascii85.class.php – Jeff Wooden Aug 24 '12 at 17:40
  • From my experience any DB storage of images is cripplingly painful, no matter how small. We should probably be trying to discourage him rather than encourage him tbh, but not sure if that is within the scope of the question. – Sammaye Aug 24 '12 at 18:11
  • @Sammaye - I'm trying to create a simple image gallery and found that carting around source code + images + database dumps is rather annoying. There is a possibility that I'd have a "file" record with the metadata pointing to a file that no longer exists/in a directory with incorrect permissions, etc. Also as far as chmods/chowns/etc on the file system is concerned, I'm usually at the mercy of my current hosting provider. With these concerns, it seemed that saving the images in the database is more appealing. Can you help me understand why it's such a bad idea to do this? – user1623192 Aug 24 '12 at 18:47
  • @user1623192 Are these files user provided if so then move hosting provider cos that is a restrictive hosting provider for a reason and most likely your site type could breach their Terms and Conds. As to why it is bad: http://stackoverflow.com/questions/6313969/phpstore-image-into-mysql-blob-good-or-bad That's a starter. Basically the problem is speed and scalability, I suppose if your making a very very simple gallery then it is ok but still file system would be easier to manage in the long run with modifications to the files. – Sammaye Aug 24 '12 at 18:54