Saving images to MySQL Database? Yes? Or not?

Question

I'm trying to store images directly in a sql table using PHP. I have a table:

CREATE TABLE `products` (
     `id` INT UNSIGNED AUTO_INCREMENTS PRIMARY KEY,
     `date` DATE,
     `img` LONGBLOB, 
     `path` VARCHAR(255)
);

Using php I try to save the image to a mysql table .:

<?php 
// snippet of main part of php code
// query string
$query = "INSERT INTO `produkt` (`id`, `date`, `img`, `path`)";
$query .= " VALUES('', date('d.m.Y'), ?, ?)";

// prepare query 
$stmt = $conn->prepare($query);

//  bind  params
$stmt->bind_param("bs", $img, $path);
$img = addslashes(file_get_contents($_FILES['img']['tmp_name']));
$path = $_FILES['img']['tmp_name'];

// execution of the prepared statement
$stmt->execute();

?>

All data comes to the database, except for the img column, where the image does not want to be saved. I find that this sql database image management logic will probably be a bad idea, how can I manage images using a sql database as efficiently as possible without unnecessarily burdening my MySQL database? Can you describe a simple example? Thank you.

Answer 1

Saving images directly in the database can easily blow up the size of your database. Whether you want to do this or not might depend on the amount of images you want to save in the database, and on the expected size of those images. However, I would usually advise against this approach.

An alternative to saving the images directly inside the database as BLOBs is to just store the path to the image in the database. The image itself is stored on the disk (HDD / SSD) of the server. You could use PHP's move_uploaded_file() method to handle this, because the file path in $_FILES['img']['tmp_name'] is just a temporary path. That means: If you just save the path from $_FILES['img']['tmp_name'] you might get problems when trying to retrieve the file from that path later.

This approach usually uses much less data inside the DB than storing a full image (unless your images are really small). Of course, your PHP script will then have to read the image file from the disk.

Answer 2

A few comments:

You seem to be using date('d.m.Y') I guess to get the current date. You're using it as a PHP function, but it's inside an SQL statement. I suggest you use CURDATE() if you put it inside the SQL statement. If you use the PHP function, do it outside the SQL statement and bind the result as a parameter.

Your code stores both the img content and the path to the tmpfile for the upload. I don't see the need for this. Pick one or the other. An uploaded tmpfile is removed at the end of the PHP request, so I don't know why you would store that path.

Don't use addslashes() for content you're binding to a query parameter. This will store the literal slashes in your content. Since your image file is not text, it will add slashes before binary bytes that happen to be the same as ASCII character \ (0x5C). You don't need slashes anyway when using bound parameters, even if it's for text.

See https://www.php.net/manual/en/mysqli-stmt.send-long-data.php for the right way to store larger data content via bound parameter. This allows files that are larger than MySQL's max_allowed_packet buffer, because it sends the content in chunks.

$query = "INSERT INTO `produkt` (`id`, `date`, `img`)
          VALUES('', CURDATE(), ?)";

// prepare query 
$stmt = $conn->prepare($query);

//  bind  params
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen($_FILES['img']['tmp_name'], "r");
while (!feof($fp)) {
    $stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);

// execution of the prepared statement
$stmt->execute();

Are you asking whether it's better to store the image or the path? There is no universal rule about this. Many developers would say, "never store big images and other files in the database" because it makes the database larger.

But there are good reasons to do that sometimes, such as:

• If you delete a row, it will atomically delete the image stored on that row. Whereas if you store it outside the database, then you need to remember to delete any orphaned images when you delete a row in the database.

• Data in the database obeys transaction semantics. If you insert/update/delete a row that references an image, the change is isolated until you commit. You may also choose to rollback the transaction. You can't do that with files on the filesystem.

• A database backup will collect all the image data along with row data, and this will be atomic. If you keep image files outside the database, a data backup must be done in two steps, and you always worry if you got the images that are true for that database snapshot.