Remote image file to sqlite blob in PHP?

问题:

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.

回答1:

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

回答2:

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!