-1

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.

marilo
  • 11
  • 1
  • 1
  • What is in the file? – Jay Blanchard Jan 12 '18 at 20:23
  • 2
    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 Jan 12 '18 at 20:24
  • @Jay Blanchard i tested various .jpg files – marilo Jan 12 '18 at 20:27
  • "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 Jan 12 '18 at 20:29

1 Answers1

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

jhilgeman
  • 1,543
  • 10
  • 27
  • I always see solutions with base64... I like `bin2hex` it's a good idea (if it works)! – rlanvin Jan 12 '18 at 20:34
  • Thanks a lot for helping, this was the problem. – marilo Jan 12 '18 at 20:37
  • 1
    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). – jhilgeman Jan 12 '18 at 20:38