SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem using PHP to pull binary files from a BLOB field in MYSQL.

    Well, I'm not sure if this belongs more in the MySQL forum or here, but it seems like I'm posting mostly PHP code so here goes.

    I've searched high and low, and the best I've been able to find is a post on this user forum, so I registered to ask for your expertise. (Here is that post.)

    I am trying to upload binary files to a table in MySQL. (I know that this is some resistance to doing this, but this is the route we've decided to go down for the project I'm working on.)

    Here is the code for my upload form:
    Code:
    <html>
    <form method="post" enctype="multipart/form-data" action="grabfile.php">
    
    <input type="hidden" name="MAX_FILE_SIZE" value="2000000">
    <input name="userfile" type="file" id="userfile">
    
    <input name="upload" type="submit" class="box" id="upload" value=" Upload ">
    </html>
    And here is the phpfile that runs when the Upload button is clicked:
    Code:
    <?php
    if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
    {
    $fileName = $_FILES['userfile']['name'];
    $tmpName  = $_FILES['userfile']['tmp_name'];
    $fileSize = $_FILES['userfile']['size'];
    $fileType = $_FILES['userfile']['type'];
    
    $fp      = fopen($tmpName, 'r');
    $content = fread($fp, filesize($tmpName));
    $content = addslashes($content);
    fclose($fp);
    
    if(!get_magic_quotes_gpc())
    {
        $fileName = addslashes($fileName);
    }
    
    include 'config.php';
    
    
    $query = "INSERT INTO upload (name, size, type, content ) ".
    "VALUES ('$fileName', '$fileSize', '$fileType', '$content')";
    
    mysql_query($query) or die('Error, query failed');
    
    
    echo "<br>File $fileName uploaded<br>";
    }
    
    ?>
    This nicely puts the selected file into my DB. However, when I try to download the file, there seems not be any character encoding information. The entire file downloads--or at least, a file of the same size downloads--but nothing can open the file. JPGs and JPEGs can't be opened (on Windows or Linux), and a .doc prompts me to select the character set. But no matter which character set I choose, the file is just gibberish.

    Here is the code I'm trying to download the file with:

    Code:
    <?php
    
        include 'config.php';
    
        if(isset($_GET['id']))
    {
        $id=intval($_GET['id']);
        $query = "SELECT name, type, size, content FROM upload WHERE id=$id";
        $result = mysql_query($query) or die('Error, query failed');
        list($name, $type, $size, $content) = mysql_fetch_array($result);
        header("Content-Disposition: attachment; filename=$name");
        header("Content-length: $size");
        header("Content-type: $type");
        echo $content;
    
        exit;
    
    }
    
    ?> 
    
    <html> 
    	<head> 
    		<title>Download File From MySQL</title> 
    		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
    	</head> 
    <body> 
    
    <?php
        $query  = "SELECT id, name FROM upload";
       $result = mysql_query($query) or die('Error, query failed');
        if(mysql_num_rows($result) == 0)
        {
        echo "Database is empty <br>";
        }else{
        while(list($id, $name) = mysql_fetch_array($result))
    {
    ?>
       <a href="download.php?id=<?php echo $id ?>"><?php echo $name ?></a> <br/>
    
    <?php
        }
        }
    ?>
    
    </body>
    
    </html>

    In case this helps, the columns for upload in MySQL look like this:
    Code:
    mysql> show full columns in upload;
    +---------+-------------+-------------------+------+-----+---------+----------------+----------------------+---------+
    | Field   | Type        | Collation         | Null | Key | Default | Extra          | Privileges           | Comment |
    +---------+-------------+-------------------+------+-----+---------+----------------+----------------------+---------+
    | id      | int(11)     | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update |         | 
    | name    | varchar(30) | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update |         | 
    | type    | varchar(30) | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update |         | 
    | size    | int(11)     | NULL              | NO   |     | NULL    |                | select,insert,update |         | 
    | content | mediumblob  | NULL              | NO   |     | NULL    |                | select,insert,update |         | 
    +---------+-------------+-------------------+------+-----+---------+----------------+----------------------+---------+
    Thanks in advance for any insight you can give me.

    -Josh

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    I just tried your files on my localhost WAMP (Win7 Pro x64) server and it works fine for me.
    The only thing I did have to change was this line:
    PHP Code:
    header("Content-Disposition: attachment; filename=$name"); 
    Into:
    PHP Code:
    header("Content-Disposition: attachment; filename=\"$name\""); 
    (so replace $name with \"$name\")

    I have an images called "clouds 3.jpg" and when I uploaded it and downloaded it again it came back as the file "clouds" (extension-less).

    Tested with a .jpg and a .docx, both work as expected. The database table I created is an exact replica of yours.

    It could be a problem in the config.php? This is the one I used:
    PHP Code:
    $link mysql_connect('localhost''***''***') or die ('Could not connect to db!');
    mysql_select_db('***'$link) or die ('Unable to select db!');
    mysql_set_charset('utf-8'); 
    Also tried the iso-8859-1 charset, and that works as well.

    As a last idea, maybe you've got some unwanted magic quotes going on somehere?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ScallioXTX,

    Thanks so much for your help. Sadly, I'm still stuck.

    I tried escaping $name and adding mysql_set_charset('utf-8'); to my config.php file. But I still cannot open files that I've downloaded from the db. Also checked that magic quotes are turned off. We're running PHP 5.2.14.

    When I try to open a jpeg in linux, I get this message:
    Error interpreting JPEG image file (Not a JPEG file: starts with 0x0a 0xff)
    Does that bit of hex code give any clues?

    If my script works for you, then it must be something in the way my MSQL installation or database is configured, right? Or maybe the way our isp has php configured?

    Thanks,

    -Josh

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Are both magic_quotes_gpc and magic_quotes_runtime set to off?

    Otherwise I don't know what the problem may be. I'm running PHP 5.2.8 with error_reporting = E_ALL | E_STRICT by the way.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, magic_quotes_gpc and magic_quotes_runtime are both disabled.

    I've even tried recreating the table as InnoDB and switching it back to MyISAM, but still no joy. I'll see if there's some way to upgrade PHP on the ISPs side.

    Thanks for the input. At least I can kind of rule out code problems now.

    -Josh

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Just thought of something.

    If you change grabfile.php temporarily such that it directly sends the just uploaded file back to the browser directly and that works you can rule out a problem with MySQL. If that doesn't work the problem is with MySQL.

    grabfile.php
    PHP Code:
    <?php
    if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
    {
    $fileName $_FILES['userfile']['name'];
    $tmpName  $_FILES['userfile']['tmp_name'];
    $fileSize $_FILES['userfile']['size'];
    $fileType $_FILES['userfile']['type'];

    $fp      fopen($tmpName'r');
    $content fread($fpfilesize($tmpName));
    fclose($fp);

    header("Content-Disposition: attachment; filename=\"$fileName\"");
    header("Content-length: $fileSize");
    header("Content-type: $fileType");
    echo 
    $content;
    exit();
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    If your file is indeed binary, this will surely bugger it up.

    PHP Code:
    $content addslashes($content); 
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AnthonySterling,
    I commented out the addslashes line, but then the query failed.

    ScallioXTX,

    I tried your suggestion and used your posted code to bypass the DB. Indeed, the file opens beautifully. So this means that something bad is happening on the way to the DB, in the DB, or on the way from the DB, correct?

    I've got a post in on my ISPs forum. It does not seem heavily-read, but I'm hopeful that someone there will have an insight that can help me.

    In the mean time, if anyone thinks of anything, I'll be happy to try it. I really *really* appreciate the time you guys are taking for this. It's been a real headache for me!

  9. #9
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by welsh059 View Post
    AnthonySterling,
    I commented out the addslashes line, but then the query failed.
    I also tried that and the query failed for me as well.

    Quote Originally Posted by welsh059 View Post
    So this means that something bad is happening on the way to the DB, in the DB, or on the way from the DB, correct?
    Absolutely 100% correct

    Quote Originally Posted by welsh059 View Post
    In the mean time, if anyone thinks of anything, I'll be happy to try it. I really *really* appreciate the time you guys are taking for this. It's been a real headache for me!
    You're very welcome. I'm here to help
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  10. #10
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, this is really getting weird now. The folks at my ISP were not very helpful, since no one is ever very keen on putting binary data in a DB. So I figured I'd install a LAMP configuration on my localhost just to make sure that the problem is on the server side.

    But even on localhost, I have the exact same problem! However, it turns out that PDFs download just fine--both from the server and from localhost.

    So this is a real head scratcher. I guess I will go back to the drawing board and see if I can find another script that works.

    If anyone has any ideas, I'll be thrilled to hear them.

    Thanks!

    -Josh

  11. #11
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Curiouser and curiouser!

    I just looked at your code and suddenly noticed $fp = fopen($tmpName, 'r'); which opens the file for reading, but does not specify the file is binary.

    Could you try with replacing

    PHP Code:
    $fp      fopen($tmpName'r');
    $content fread($fpfilesize($tmpName));
    $content addslashes($content);
    fclose($fp); 
    with

    PHP Code:
    $content add_slashesfile_get_contents($tmpName) ); 
    since file_get_contents is binary-safe

    If this doesn't work I'm totally out of ideas, so let's hope it does eh?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  12. #12
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, file_get_contents did no help me
    PHP says that this function is binary safe, but there may be a bug.

    I also tried readfile instead of fopen. Same result.

    So then I wondered if this was a bug in the version of php I'm using. So I booted into Windows 7, Installed WAMPSERVER, Added php 5.2.8, Recreated my scripts etc. And guess what? It still didn't work! ScallioXTX this should be the same configuration that you've used with success, right? So there must be something different between what I'm doing and what you're doing. Could I trouble you to post or send the output of your phpinfo() ?

    Also, I'm trying to figure out if the files are being garbled on their way *to* the DB or *from* the DB. Does anyone know of a way to just pluck the BLOB file out of MYSQL without using the php scripts I've written? I've tried with PhpMyAdmin, but if it's possible to do this, I'm not seeing it.

    Thanks!

    -Josh

  13. #13
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    I've just sent you a PM with my complete config. Hope that helps

    I use SQLYog for viewing and accessing the database and there I can click a blob field and see the image. There is a free version of this program available as well, but I don't know if that supports it (I have the commercial version).
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  14. #14
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    welsh059, have you tried:

    PHP Code:
    $uploaded_file['data']         = file_get_contents($_FILES['upload']['tmp_name']); 
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  15. #15
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SpacePhoenix,
    I gave that a try and it did not fix the problem. Thanks though!

    ScallioXTX,
    Thanks much for the configuration files. Sadly, they didn't fix the problem either.
    But....

    After installing SGLyog (the trial version--<strike>the open source version doesn't allow the viewing of Blob data, that I could see at any rate.</strike>--my bad--the community version does allow viewing of BLOB data!) I connected to the DB and double clicked on a BLOB field and lo and behold! My images have been in the DB, happy as clams all along!

    This seems like great news because now I have narrowed the problem down to my download scripts, right? MySQL is configured fine. PHP is allowing me to upload. I guess it could be PHP or Apache messing me up on the download side of things.

    Actually, I never did get your httpd.conf to work in my WAMP stack. Can anyone think of a field there that would be corrupting files on the way downstream?

    Here are the two scripts I'm using to download. I apologize for dumping the html in too, but I want to include it all, in case these is some small oversight that is making these not work.

    This is the file that generates the list of files in the DB:
    Code:
    <?php
    
        include 'config.php';
    
        if(isset($_GET['id']))
    {
        $id=intval($_GET['id']);
        $query = "SELECT name, type, size, content FROM upload WHERE id=$id";
        $result = mysql_query($query) or die('Error, query failed');
        list($name, $type, $size, $content) = mysql_fetch_array($result);
    
        header("Content-Disposition: attachment; filename=\"$name\""); 
        header("Content-length: $size");
        header("Content-type: $type");
        echo $content;
    
        exit;
    
    }
    
    ?> 
    
    <html> 
    	<head> 
    		<title>Download File From MySQL</title> 
    		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
    	</head> 
    <body> 
    
    <?php
        $query  = "SELECT id, name FROM upload";
       $result = mysql_query($query) or die('Error, query failed');
        if(mysql_num_rows($result) == 0)
        {
        echo "Database is empty <br>";
        }else{
        while(list($id, $name) = mysql_fetch_array($result))
    {
    ?>
       <a href="download.php?id=<?php echo $id ?>"><?php echo $name ?></a> <br/>
    
    <?php
        }
        }
    ?>
    <a href ="getfiles.php">Upload another file</a>
    <br />
    
    <br />
    
    </body>
    
    </html>
    And here is the script that processes the download:
    Code:
    <?php
    ini_set('display_errors', 1);
    error_reporting(E_ALL);
    if(isset($_GET['id']))
    {
    // if id is set then get the file with the id from database
    
    include 'config.php';
    
    $id    = $_GET['id'];
    $query = "SELECT name, type, size, content FROM upload WHERE id = '$id'";
    
    $result = mysql_query($query) or die('Error, query failed');
    
      $name = @mysql_result($result, 0, "name");
      $size = @mysql_result($result, 0, "size");
      $type = @mysql_result($result, 0, "type");
      $content = @mysql_result($result, 0, "content");
    
    
    header("Content-length: $size");
    header("Content-Type: $type");
    header("Content-Disposition: attachment; filename=\"$name\"");
    header("Content-Description: PHP Generated Data");
    header("Content-transfer-encoding: binary");
    
    echo $content;
    
    
    exit;
    }
    
    ?>
    Any ideas?

    Thanks again for the help. I think I'm getting close here!

    -Josh
    Last edited by welsh059; Aug 9, 2010 at 13:33. Reason: Adding current download script.

  16. #16
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Eureka!

    Stumbled across this little line of code:
    Code:
    while (@ob_end_clean());
    The forum I found it says that "if you server has output buffering on, then it won't send the image data correctly."

    So now, at long last, I can download the images, pdfs, etc. from the DB!

    Thank you all SO much for your help with this. My boss is ecstatic, and I will be sleeping much more soundly tonight. SitePoint rocks!

    -Josh


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •