Remote image file to sqlite blob in PHP?

Question

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.

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
– TTT, Commented Aug 4, 2010 at 3:29

Alex Jasmin · Accepted Answer · 2010-08-04 04:20:18Z

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()

edited Aug 4, 2010 at 4:20

answered Aug 4, 2010 at 3:36

Alex Jasmin

39.5k7 gold badges80 silver badges68 bronze badges

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

2 Comments

TTT Over a year ago

I agree!! It is also much faster becuase less parsing of different sql statements is needed. It can be three times faster. See stackoverflow.com/questions/904796/…

Origamiguy Over a year ago

Thanks! The image is on a remote server, I only have http read access, will fopen still work? I can't test right now.

NullUserException · Accepted Answer · 2023-05-22 21:46:43Z

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

edited May 22, 2023 at 21:46

answered Aug 4, 2010 at 0:59

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

9 Comments

Origamiguy Over a year ago

I knew it was file_get_contents(), I was typing from memory on my iPhone, and got it wrong.

TTT Over a year ago

There are pros and cons to storing binary data in the db and some db's do a better job than others.

NullUserException Over a year ago

@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 Over a year ago

@TTT effbot.org/zone/sqlite-blob.htm I couldn't find anything official though

thomasrutter Over a year ago

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.

|

Collectives™ on Stack Overflow

Remote image file to sqlite blob in PHP?

2 Answers 2

2 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related