Converting files to blob to save in database

Question

I have an old user site that we are currently updating. The site's users have previously uploaded profile pics that are stored in a directory. I am now wanting to keep these profile images in the database, (easy to backup all data) but am having trouble working out how to do it.

There are a lot of tutorials talking about do ing this from files that have just been uploaded and using the tmp name etc, but how can I re-create with files that have already been uploaded?

I have looked into $data = file_get_contents($filename); Which seems to create a binary file, but doesn't seem to save in database with:

mysql_query("UPDATE profiles SET company_logo = mysql_real_escape_string('".$data."') WHERE id = 1 ") or die(mysql_error());

*UPDATE* So after looking into this some more, I think I will go with the majority and save the images as they already are... in folders..

Please please please don't do that. The filesystem is an awesome database for files. — Adi
– Adi, Commented Sep 6, 2012 at 9:32
You really shouldn't store files in the database. There are good reasons why files should stay in the filesystem. Have a look at this question — Aleks G
– Aleks G, Commented Sep 6, 2012 at 9:34

JvdBerg · Accepted Answer · 2012-09-06 09:37:07Z

1

It's generally a bad idea to store images in the database.

It is better to store the images in a file and keep a reference in a field or table.

However, if you want to store them, you must store them as BLOB (Binairy Large OBject). On a BLOB field, no character translation is done. When you store them, you must store them exactly as the file contents. So remove the mysql_real_escape string, as it will mess up your binary data.

answered Sep 6, 2012 at 9:37

JvdBerg

21.8k8 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jimbo Over a year ago

Why is it a bad idea to store image in database? I know the loading times might be slightly slower...

JvdBerg Over a year ago

It always difficult not to start a war on db image storage :) Have a look ath this discussion: stackoverflow.com/questions/3748/…

Aleks G Over a year ago

Remove mysql_real_escape_string may be dangerous. Thinks what would happen if you actually have something like '); drop table x; in the file? A much better approach would be to use prepared statements and then execute them with the blob data. That way DB server/driver would handle any necessary conversion itself.

JvdBerg Over a year ago

For the blob storage, you have to remove the mysql_real_escape_string, otherwise the binary data will(could) mess up, depending on the contents. I agree, that if you store binary data, one always should use parameter binding.

Collectives™ on Stack Overflow

Converting files to blob to save in database

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related