Inserting file_get_contents($variable) into database as blob [duplicate]

Question

i am fairly new to PHP and SQL, and facing a problem that i am not able to find a solution for.

I am trying to upload and image to a mysql database, using this PHP code:

$fileName = $_FILES['file']['name'];
$fileTmpName = $_FILES['file']['tmp_name'];
include 'dbh.php';
$fileData = file_get_contents($fileTmpName);
$sql = "INSERT INTO images (name, data) VALUES ('$fileName', '$fileData')";
mysqli_query($conn, $sql);
mysqli_close($conn);

The result is that the database doesn't change at all. If instead of $fileData i just insert a random value (e.g. "INSERT INTO images (name, data) VALUES ('$fileName', 'test')") it inserts the values into the database, so the problem must be $fileData is guess.

The type of the column data is blob, i also tried longblob, varchar and text.

Thanks for your help.

use PDO instead of mysqli and pass data as object instead of string. OR you can do base64_encode things before inserting them and base64_decode them when getting them out. — Dimi
– Dimi, Commented Jan 12, 2018 at 20:24
"that i am not able to find a solution for"... Seriously? There is a similar question that is literally the first line in the "Related" questions on the right... The database doesn't change because you are getting an error (that you are not checking, by the way) because you do not escape the variables properly when creating your query. — rlanvin
– rlanvin, Commented Jan 12, 2018 at 20:29

jhilgeman · Accepted Answer · 2018-01-12 20:35:38Z

1

When inserting data into a BLOB field, you can use just the hex representation, like:

INSERT INTO table (myblobfield) VALUES (0x123456789...etc...)";

You can use bin2hex() to convert the $fileData data to hex first:

$sql = "INSERT INTO images (name, data) VALUES ('$fileName', 0x".bin2hex($fileData).")";

Just bear in mind that it's USUALLY not a good idea to store binary data in BLOB fields. It tends to perform poorly in the long run in most situations, especially if the data is accessed frequently.

A lot of people try to use PHP to pull image data out and stream it to a browser. But this means that the image data has to be first queried and sent back from MySQL (which eats up some database I/O), then it ends up taking up PHP memory temporarily, and then PHP has to finally push it to the final recipient. So if you have a 100k image, then it adds a 100k load to the database and adds another 100k to PHP memory usage, and likely takes up another web server slot for a longer period of time. So it's just a lot of overhead.

If it's static content like a standard JPG image or whatever, it's almost always better to allow the web server to simply serve it up directly. There are various optimizations along the way to allow that scenario to happen quickly (and it scales well with future solutions).

edited Jan 12, 2018 at 20:35

answered Jan 12, 2018 at 20:31

jhilgeman

1,58812 silver badges32 bronze badges

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

3 Comments

rlanvin Over a year ago

I always see solutions with base64... I like bin2hex it's a good idea (if it works)!

marilo Over a year ago

Thanks a lot for helping, this was the problem.

jhilgeman Over a year ago

base64 is a really bad idea. It not only adds another 33% or so onto the original size, but it also means that the data has to be decoded every time before it's usable (unless you're trying to use base64 versions in inline images).

Collectives™ on Stack Overflow

Inserting file_get_contents($variable) into database as blob [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related