Insert Blobs in MySQL Databases With PHP

Insert Blobs in MySql databases with php

Problem

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','file_get_contents($tmp_image)')";

This creates a string in PHP named $sql. Forget about MySQL for a minute, because you're not executing any query yet. You're just building a string.

The magic of PHP means that you can write a variable name — say, $this->image_id — inside the double quotes and the variable still gets magically expanded.

This functionality, known as "variable interpolation", does not occur for function calls. So, all you're doing here is writing the string "file_get_contents($tmp_image)" into the database.

Solution (1)

So, to concatenate the result of calling file_get_contents($tmp_image), you have to jump out of the string and do things explicitly:

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','" . file_get_contents($tmp_image) . "')";

(You can see even just from the syntax highlighting how this has worked.)

Solution (2)

Now the problem you have is that if the binary data contains any ', your query is not valid. So you should run it through mysql_escape_string to sanitize it for the query operation:

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','" . mysql_escape_string(file_get_contents($tmp_image)) . "')";

Solution (3)

Now you have a really big string, and your database is getting bulky.

Prefer not storing images in databases, where you can help it.

Better way to insert blob into MySQL with PHP

Using the send_long_data method will allow you to solve the case where a file is too big compared to the max_allowed_packet value in your particular MySQL server setup. However the fact your two codes works demonstrate that you did not reach this limit in your tests. The send_long_data method has been designed to be called multiple times in a loop with partial "chunks" of the data too big to be send in one pass. Furthermore I do not favor the way you wrote the second code because you are not able to handle error during file read. I will suggest a third way to code this :

define ('YOUR_MYSQL_MAX_ALLOWED_PACKET', 8192);
$fp = fopen($tmpName, "r");
// TODO check here if fopen succeed
$prep_stmt = "INSERT INTO dokumenty (name, size, type, content, autor, poznamka) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($prep_stmt);

while (!feof($fp)) {
    $stmt->send_long_data(3, fread($fp, YOUR_MYSQL_MAX_ALLOWED_PACKET));
}
fclose($fp);

Where the YOUR_MYSQL_MAX_ALLOWED_PACKET constant value must be adjusted to your MySQL particular setup.