4

I have an image file stored on a remote server. I only have HTTP access to the server, so I'm getting its content using file_get_contents(URL)

I need to store this content in a local sqlite3 database in a field of type 'blob'. I'm using the PDO object to connect to the database, and I'm using 

$db->exec("INSERT INTO myTable (myImageBlob) VALUES
           ('".file_get_contents($filePath)."')")

to add data to the database.

This isn't working. Apologies if I'm making a really noobish mistake. We all have to learn somehow...

For reasons I will not delve into, it is not a possibility for me to store the image locally and put the URL in the database. It /has/ to be stored in a blob.

Origamiguy
  • 1,294
  • 2
  • 13
  • 19
  • Always use parameterized queries when you use sqlite, three advantages. 1. Much faster (less parsing of sql statements needed by the db). 2. No sql injection. 3. No problems when your string contains a ' or a ''. – TTT Aug 04 '10 at 03:29

2 Answers2

5

Don't do it. Every time you insert binary data into a database, God kills a kitten.
Instead, store that image somewhere in the file system and save the path in your db.

Remember to think of the kittens!

alt text

NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • I knew it was file_get_contents(), I was typing from memory on my iPhone, and got it wrong. – Origamiguy Aug 04 '10 at 01:15
  • 1
    There are pros and cons to storing binary data in the db and some db's do a better job than others. – TTT Aug 04 '10 at 03:33
  • @TTT SQLite is not one of them. "For best operation in large tables, the SQLITE author recommends keeping the row size around 250 bytes or below." – NullUserException Aug 04 '10 at 04:13
  • @TTT http://effbot.org/zone/sqlite-blob.htm I couldn't find anything official though – NullUserException Aug 04 '10 at 04:34
  • It's from 2003, maybe it is still true, maybe not, it needs to be tested. – TTT Aug 04 '10 at 05:09
  • @TTT I wouldn't do it anyways. – NullUserException Aug 04 '10 at 05:48
  • One advantage of using BLOBs would be to keep all the your data in a single file. SQLite has this simple one file per database model that makes it easy to send a database by email among other things. – Alex Jasmin Aug 04 '10 at 21:06
  • 1
    A lot of people religiously espouse the "no binary data in DB" rule but it really depends on situations. It buys ACID compliance, some binary blobs are small in size (eg avatar/thumbnail pics), keeps everything in one place. Do it, but do it only if you're confident that your backup strategy has been designed to handle it. The main problem with doing it is that the databases can grow very large very quickly sometimes. Trade off atomicity (ACID) against ease of file transfer/backup. – thomasrutter May 05 '16 at 00:04
  • @thomasrutter I have warmed up a little more to the idea since 2010, but I still prefer using the filesystem 95% of the time. – NullUserException May 05 '16 at 05:20
5

Concatenating data you have no control over in an SQL statement is a very bad idea. For instance the image data may contain a quotation mark that will terminate the string or a backslash that will be interpreted as a control character. Worst someone could build a fake image to injects malicious SQL code in your application.

I suggest you use a prepared statement instead:

$query = $db->prepare("INSERT INTO myTable (myImageBlob) VALUES (?)");
$query->bindParam(1, fopen($filePath, "rb"), PDO::PARAM_LOB);
$query->execute();

Note that by passing PDO::PARAM_LOB to bindParam() you insert the blob's data from a stream. That's why I'm using fopen() instead of file_get_contents()

Alex Jasmin
  • 39,094
  • 7
  • 77
  • 67
  • I agree!! It is also much faster becuase less parsing of different sql statements is needed. It can be three times faster. See http://stackoverflow.com/questions/904796/how-do-i-get-around-the-problem-in-sqlite-and-c/926251#926251 – TTT Aug 04 '10 at 03:53
  • Thanks! The image is on a remote server, I only have http read access, will fopen still work? I can't test right now. – Origamiguy Aug 04 '10 at 12:02