BLOB data fails to be written to a database

Question

I created a database that stores various data about an image as well as the image itself. I have five keys (id, name, size, format, file). The id is auto-incrementable. The file is a value of a BLOB type - (mediumblob). Rest is of its respective type. The weird thing is that every other variable is successfully written to the database except for the BLOB image itself.

Wait for the user to upload an image using $_POST:

if(isset($_POST['file_one_submit'])){
    $post = 'file_one_input';
}

Define all the variables. Including the $file variable with:

$file = file_get_contents($_FILES[$post]['tmp_name']);

Then I make a query to replace the record in the database with the new information:

$stmt = $conn -> prepare("REPLACE INTO `images` (id, name, size, format, file) VALUES(?, ?, ?, ?, ?);");
$stmt -> bind_param("isisb", $id, $name, $size, $format, $file);
$stmt -> execute();

After the query's been executed all records in the database successfully get updated with the exception of the file column which should contain the BLOB data. I thought that the $post variable may be incorrectly defined but getting the name of the uploaded file for instance works:

upload_name = $_FILES[$post]['name'];

I've tried to echo the $file variable to check its contents and got presented with a long rubbish of data which I assume is the proper string generated during the conversion to BLOB with the file_get_contents() function.

I've checked that the size of the file does not exceed the capabilities of the mediumblob BLOB type. I've also attempted to check for errors in my code with this post without success. No errors or warnings appear on the page when the code is run. I've also enabled error/warning checks with E_STRICT but still, no output was presented.

Answer 1

I got it working by using send_long_data method. (in these examples I have removed some irrelevant columns for the example).

Option 1: Use send_long_data method for passing binary data

The benefit of using b according to https://www.php.net/manual/en/mysqli-stmt.bind-param.php:

Note:

If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in types and use mysqli_stmt_send_long_data() to send the data in packets.

//...

if(isset($_POST['file_one_submit'])){
    $post = 'file_one_input';

    $id = 1;
    $file = NULL;

    $stmt = $conn -> prepare("REPLACE INTO `images` (id, file) VALUES(?, ?);");
    $stmt->bind_param("ib", $id, $file);
    
    //send_long_data(int $param_num, string $data): bool
    $stmt->send_long_data(1, file_get_contents($_FILES[$post]['tmp_name'])); 
    $stmt->execute();
}
?>

<form action="file.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file_one_input" />
    <input type="submit" name="file_one_submit" value="Upload" />
</form>

Option 2: Change b to s

I think it is better to use b, but if I changed b to s (string) this also seems to be working:

//...

if(isset($_POST['file_one_submit'])){
    $post = 'file_one_input';

    $id = 1;
    $file = file_get_contents($_FILES[$post]['tmp_name']);

    $stmt = $conn -> prepare("REPLACE INTO `images` (id, file) VALUES(?, ?);");
    $stmt->bind_param("is", $id, $file);
    $stmt->execute();
}
?>

<form action="file.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file_one_input" />
    <input type="submit" name="file_one_submit" value="Upload" />
</form>

Answer 2

Option b is used for sending data in batches. It's used when your data size exceeds max_allowed_packet (16MB by default). You must then use the method send_long_data() to send the data in packets.

// $file should be set to NULL
$stmt->bind_param("ssssb", $id, $name, $size, $format, $file);
$fp = fopen($_FILES[$post]['tmp_name'], "r");
while (!feof($fp)) {
    $stmt->send_long_data(4, fread($fp, 8192));
}
fclose($fp);
$stmt->execute();

When you are sure that your blob data will never exceed the limit, you can safely bind it as a string. By default, you should bind every variable as a string (s) as the type doesn't matter in most cases. Binding everything as a string is easier and safer 99% of the time.

_{Usually, I recommend PDO as the easier solution, but even when using PDO with large objects, you still have to handle streams in a similar manner.}

Answer 3

If the image is for a web page, I recommend not storing the image in the database. Instead, write to a file in tree of other web pages/files. Then build <img src=$path> where $path is relative to the root for web files.

This avoids the nasties being asked about in this Question, makes the database a little more efficient, and actually speeds up delivering web pages.

Put the path in the table so that you can build the img tag when building the web page.

Answer 4

So you can manipulate it using the PHP Streams API.

$conn = new PDO('database settings');
$stmt = $conn->prepare("REPLACE INTO `images` (id, name, size, format, file) VALUES(?, ?, ?, ?, ?);");


$file_tmp = $_FILES['file']['tmp_name'];
$fp = fopen($file_tmp, 'rb');

$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['name']);
$stmt->bindParam(3, filesize($file_tmp));
$stmt->bindParam(4, $_FILES['file']['type']);
$stmt->bindParam(5, $fp, PDO::PARAM_LOB);

$conn->beginTransaction();
$stmt->execute();
$conn->commit();

This example opens up a file and passes the file handle to PDO to insert it as a LOB. PDO will do its best to get the contents of the file up to the database in the most efficient manner possible.

More detail is here