MySQL and BLOB field impossible to upload/display images from PHP (but it works manually) / Binary/HEX DUMP?

Question

I'm becoming mad due this problem. Here is my database:

Cod_Classification  int(11)
Cod_App         char(10)
ID_eventclass   char(5)
Descrizione char(35)
Active          char(1)
Logo_Eve    blob

I use this form to populate it:

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Prova di Upload</title>
</head>

<body>

<form action="UploadImage.php" method="post" enctype="multipart/form-data">
    <p>
    <label for="ID_evcls">Codice evento</label> <input  type="text" name="ID_evcls" maxlength="5" size="10"> <br>
    <label for="Desc">Descrizione</label> <input  type="text" name="Desc" maxlength="35" size="40"> <br>
    Logo da utilizzare: <input name="userfile" type="file" /><br>
    <input type="submit" value="Send File" />
    <p>
</form>

</body>
</html>

And this is the PHP part:

<?php
$db_host = "localhost";
$db_user = "USERNAMEDB";
$db_database = "NAMEDATABASE";
$db_password = "*********";
$db_tabeventclassification = "adv_eventclassification";

$ID_ev = $_POST[ID_evcls];
$ID_ds = $_POST[Desc];
$ID_logo = $_FILES['userfile']['name'];
$ID_tmp = $_FILES['tmp_name'];

echo $ID_ev.' '.$ID_ds.' '.$ID_logo.' '.$ID_tmp.'<br><br>';
move_uploaded_file($_FILES['userfile']['tmp_name'], $_FILES['userfile']['name']);
echo '<br><img src="'.$ID_logo.'"><br>';

$datimmagine = file_get_contents($ID_logo);
echo '<br><img src="data:image/jpeg;base64,'.base64_encode($datimmagine).'"><br>';

$connessione = mysql_connect($db_host,$db_user,$db_password);
echo "OK, database is connected<br><br>";

$query='INSERT INTO '.$db_tabeventclassification.' (`Cod_App`, `ID_eventclass`, `Descrizione`, `Active`, `Logo_Eve`) VALUES ("RCWORLDTLV","'.strtoupper($ID_ev).'","'.$ID_ds.'","1","'.addslashes(base64_encode($datimmagine)).'")';
$result = mysql_db_query("AdVisual_02_", $query ,$connessione); 

?>

When I launch the html and i upload the image, I CAN SEE PERFECTLY ON SCREEN, and if I use PhpMyAdmin I can see the blob field with data. But when I RETRIEVE data from database, i have a broken link image result, ALWAYS. Here you have the application I've built to show data:

<?php
$AdVisualV2MANAGER_ver='0.0030';

echo 'AdVisual V2 Backend Manager Versione '.$AdVisualV2MANAGER_ver.'<br>';
// echo 'Television ID: '.$Host_Cod_App.'<br>';

$db_host = "localhost";
$db_user = $db_prefix."AdVisualV02USR";
$db_database = $db_prefix."AdVisual_02_";
$db_password = "adv2pwpwpw";
$db_tabconfig = "adv_config";
$db_tabpreroll = "adv_preroll";
$db_tabimpressions = "adv_videoimpressions";
$db_tabevents = "adv_events";
$db_tabeventclassification = "adv_eventclassification";
mysql_connect($db_host,$db_user, $db_password);

echo "Connection to the Server opened; Database is ".$db_database." opening result is ".mysql_select_db($db_database)."<br>";
echo "Now listing events<br><br>";

$result = mysql_query("SELECT * FROM ".$db_database.".".$db_tabeventclassification." WHERE `Active` =1");
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}

echo '<table width="750" border="1">
            <tr>
                <th width="100" align="center" valign="top"><b>EVENTO</b></th>
                <th width="300" align="center" valign="top"><b>DESCRIZIONE</b></th>
                <th width="50" align="center" valign="top"><b>LOAD</b></th>
                <th width="200" align="center" valign="top"><b>LOGO</b></th>
            </tr>';
$numerorighe = mysql_num_rows($result);
for ($i=0;$i<$numerorighe;$i++) {
    $riga = mysql_fetch_row($result);
    echo '<tr>';
    echo '<td align="left" valign="top">'.$riga[2].'</td>';
    echo '<td align="left" valign="top">'.$riga[3].'</td>';
    $button[i]='<input type="button" id="bt"'.$i.' onclick="LoadJpg('.$i.')" value="Load '.$i.'-->">';
    echo '<td align="center" valign="top">'.$button[i].'</td>';
    echo '<td align="center" valign="top"> <img src="data:image/jpeg;base64,'.base64_encode($riga[5]).'"></td>';
    echo '</tr>';
}

echo '</table>';

?>

<script>
function LoadJpg(scelta)
{
    document.write('<input type="file" name="datafile" accept="image/jpeg">');
}
</script>

But if I upload manually the same image in the blob field, using phpmyadmin, it works perfectly. Even if I dump the content, always using phpmyadmin, downloading a .bin file and renaming it as .jpg it works perfectly.

The matter is, when i upload an image with phpmyadmin i see that it converts data as i an hexadecimal format, and generate an SQL statement like that:

UPDATE `AdVisual_02_`.`adv_eventclassification` SET `Logo_Eve` = 0xffd8ffe0001 ... 

0acf43a5a97a6089ffd9 WHERE `adv_eventclassification`.`Cod_Classification` = 61;

What the hell can I do? Where am I wrong? I'm becoming crazy on this since 3 days!!!!! Thanks to any good fellow will try to help me ciao

Michel Feldheim · Accepted Answer · 2013-03-18 22:57:48Z

1

The problem is the way you save the file to your database. You want to display the image inline html, which is fine for really small images below 1kb, not recommended for larger files. In this context using base64_encode for the output is fine, because you can't have binary data in your text/html output document. But the MySQL blob datatype is made especially for binary data.

$query='INSERT INTO '.$db_tabeventclassification.' (`Cod_App`, `ID_eventclass`, `Descrizione`, `Active`, `Logo_Eve`) VALUES ("RCWORLDTLV","'.strtoupper($ID_ev).'","'.$ID_ds.'","1","'.mysql_real_escape_string( $datimmagine).'")';

That's it. Currently you simply safe the image as text (base64) then you convert this text again using the base64 algorithm for output

Interesting article to read:

http://www.java-samples.com/showtutorial.php?tutorialid=930

edited Mar 18, 2013 at 22:57

answered Mar 18, 2013 at 22:44

Michel Feldheim

18.3k5 gold badges64 silver badges77 bronze badges

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

1 Comment

Roberto Alfieri Over a year ago

Bingo!!!! It was the "mysql_real_escape_string" feature i was not adding!!!! THANK YOU VERY MUCH you saved my life and the one of my family

Community · Accepted Answer · 2017-05-23 12:07:52Z

0

Try with .chunk_split(base64_encode($riga[5])). as per this answer on Pulling BLOB image data from MySQL in PHP. Your Base64 encoded image's data string is possibly too long and needs to be 'chunked' before you echo it to your document, to comply with RFC 2045:

qp-part := qp-section ; Maximum length of 76 characters

This would appear to be sorted 'on its own' when you copy/paste from another editor (or BLOB viewer), but isn't sorted with echo automatically when retrieving the record's value.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Mar 18, 2013 at 22:46

TildalWave

1,6671 gold badge14 silver badges21 bronze badges

2 Comments

Sam Dufel Over a year ago

RFC 2045 is for quoted-printable emails, not HTML

Roberto Alfieri Over a year ago

I'm sorry but it seemes that even adding this instruction no changes at all :-(((

user1122069 · Accepted Answer · 2013-03-19 00:30:11Z

0

Use the PHP PDO extension for this and always. The mysql extension itself is deprecated.

$pdo  = new PDO();// see reference docs for constructor arguments
$stmt = $pdo->prepare('UPDATE table set bfield=?');
$stmt->bind($image,PDO::PARAM_LOB);
$stmt->execute();

I notice also in your code that you are encoding the base64 data twice. Change base64_encode($riga[5]) to $riga[5] and your code should work unless your text field is too small.

edited Mar 19, 2013 at 0:30

answered Mar 18, 2013 at 23:35

user1122069

1,8272 gold badges26 silver badges54 bronze badges

2 Comments

Roberto Alfieri Over a year ago

TY for your suggestion, I've read on the PHP manual the PDO/MySqli change, but i'm going fool in solving this problem i've not the head in this moment to introduce new concepts - be sure i will manage this as soon as i can and TY again

user1122069 Over a year ago

note also the addition to the answer - "I notice also in your code that you are encoding the base64 data twice. Change base64_encode($riga[5]) to $riga[5] and your code should work unless your text field is too small." - I think this should solve it

Collectives™ on Stack Overflow

MySQL and BLOB field impossible to upload/display images from PHP (but it works manually) / Binary/HEX DUMP?

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related