Extracting all images from mysql database blob to directory

Question

i'm strugging to extract pictures from blob as it shows the image cannot currently be displayed because it contains errors when i download it Error interpreting JPEG image file (Unsupported marker type 0x5c) and trying to ideintifiy which the file is and getting this via "linux shell"

[none]$ file PICTURE.bin PICTURE.bin: JPEG image data identify: Unsupported marker type 0x5c `PICTURE.bin' @ error/jpeg.c/JPEGErrorHandler/322

i tried to open it via text editor and got this weird thing at the start rather than JPEG OR JPG. it shows at the start of the image file something like this "JFIF" i have tried a php script to extract the image and downloading it directly from phpmyadmin with no luck the script im using is this " not mine i found it on the internet":

<?php

// ************* MODIFY THESE FOR YOUR SERVER AND DATABASE SETTINGS *************

// y = first letter in yourdomain.com
// o = second letter in yourdomain.com
$basedir = '/home/zzz/public_html/z/'; // Needs trailing slash
$host    = 'localhost';
$user    = 'zz';  //(user information)
$password= 'zz';  //(password information)
$dbname  = 'zzz';

$type            = 'jpg';
$blob_id         = 'APLT_ID';
$blob_data       = 'PICTURE';
$blob_table      = 'zzzz';

// ************************************************** ***************



$db = mysql_connect($host, $user, $password) or die("Could not connect: " . mysql_error());
mysql_select_db($dbname, $db);

$sql = "SELECT $blob_id, $blob_data FROM $blob_table";
echo $sql;
$result = mysql_query($sql);

header('Content-Type: text/html; charset=utf-8');

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $id = $row["$blob_id"];
    $data = $row["$blob_data"];
    $file = "$basedir$id.$type";
    echo "Looking for File: $file<br>";
    if (!file_exists($file)) {
        $fp=fopen($file,"w");
        echo "Opening File: $file<br>";
        fwrite($fp,$data);
        echo "Writing File: $file<br>";
    } else {
        echo "Skipping File: $file<br>";
        fclose($fp);
        echo "Closeing File: $file<br>";
    }
    sleep(1);
}

mysql_free_result($result);
?>

can the images be encoded or something?

cause i just wasted 2 days trying to figure this thing out before posting and seeking help

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Dec 18 '16 at 19:43
It sounds like it was stored incorrectly. Did you use `binmode` or anything else when fetching the file? — Rick James, Dec 19 '16 at 05:26
nope , i tried to get it via phpmyadmin or this script above. — raymondxxx, Dec 19 '16 at 14:04

score 0 · Answer 1 · answered Dec 18 '16 at 19:48

0

Images are binary data and need to be stored in binary mode without any alterations to newline characters. Change your code to open/create the file in binary mode and if the data in the database is valid than your code should work.

$fp=fopen($file,"wb");

answered Dec 18 '16 at 19:48

Ralph Ritoch

3,260
27
37

how do i do that? i have 0 programming skills brother – raymondxxx Dec 18 '16 at 20:33
Change the line that says `$fp=fopen($file,"w");` to `$fp=fopen($file,"wb");` – Ralph Ritoch Dec 18 '16 at 20:37
tried it it didn't work, i tried to list the files from file manager and i see the pictures have 500+ kb size, so it does work extracting i think , but it might be encoded maybe? – raymondxxx Dec 18 '16 at 21:09
when i edit the image via text, i get this at the start JFIF – raymondxxx Dec 18 '16 at 21:44
`JFIF` is the 'magic' at the beginning of the file. It indicates that it is a jpg file. No problem there, nor any clues there. – Rick James Dec 19 '16 at 05:23
1

You need to `fclose` the file. – Rick James Dec 19 '16 at 05:25
i think there's fclose : fclose($fp); echo "Closeing File: $file
"; or maybe im missing something "0 programming skills sorry brother" – raymondxxx Dec 19 '16 at 06:20

score 0 · Answer 2 · answered Aug 05 '21 at 06:55

If you are looking for a MySQL query, try this.

Log to your MySQL server as root

First, check where MySQL is allowed to write output file:

mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

My system can only extract file into /var/lib/mysql-files/. If you get nothing, you can probably save the file anywhere on the file system.

To extract the file, run the following SQL query:

SELECT blob_column FROM table WHERE id='some_id' INTO DUMPFILE "/var/lib/mysql-files/file.jpg";

NOTE: Use DUMPFILE. Do NOT use OUTFILE. That will give you a corrupted output file.

Extracting all images from mysql database blob to directory

2 Answers2