Mysql blob field has first line of space and therefore wont play file

Question

I have some code to upload and download a sound recording from android. The problem i am having is that it appears an extra blank line is appearing in the binary. When this is removed the file plays i would like to know how to stop this line appearing. Below is my upload and download code as well as a print screen of the blank line

Upload code

    mysql_select_db ($database); 

// Make sure the user actually 
// selected and uploaded a file
if (isset($_FILES['image']) && $_FILES['image']['size'] > 0) { 

      $size = $_FILES['image']['size'];
      $type = $_FILES['image']['type'];

      // Temporary file name stored on the server
      $tmpName  = $_FILES['image']['tmp_name'];  

      // Read the file 
      $fp      = fopen($tmpName, 'r');
      $data = fread($fp, filesize($tmpName));
      fclose($fp);

      $data = trim(addslashes($data));

      // Create the query and insert
      // into our database.
      $query = "INSERT INTO media";
      $query .= "(file, file_size, file_type) VALUES ('$data','$size','$type')";
      $results = mysql_query($query, $link);

      $mediaid = mysql_insert_id();

    $gender = $_POST['gender'];
    $cat_id = $_POST['cat'];
    $name = $_POST['name'];
    $lat = $_POST['lat'];
    $lon = $_POST['lon'];
    $user = $_POST['user'];


  $query="INSERT INTO instance (name, gender, cat_id, lon, lat, user_id) VALUES ('$name', '$gender', '$cat_id', '$lon', '$lat', '$user')";
  $result=mysql_query($query);


      $instanceid = mysql_insert_id();

      $query4 = "INSERT INTO media_link";
      $query4 .="(media_id, instance_id) Values ('$mediaid','$instanceid')";
      $results4 = mysql_query($query4, $link);

    }

// Close our MySQL Link
mysql_close($link);
?>

download code

$test2 = @mysql_query("select * from media where media_id = '$media'");
    $result2 = mysql_fetch_array($test2);

header('Content-Type: audio/AMR');
header('Content-Disposition: attachment; filename="ifound.amr"');


    print $result2['file'];
exit;

?>

Blank line that is appearing

Is there a reason you're using `addslashes` rather than `mysql_real_escape_string`? — John Parker, May 02 '11 at 17:14
Not to mention failing to escape the other values dropped into the query. `$type`, and all the `instance` properties are completely open to abuse. — bobince, May 02 '11 at 17:33
Why put the file data into your database? Generally, you would put a reference to where the file is located on your server in your database instead. Prevents problems like these. — dqhendricks, May 02 '11 at 17:43
see: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain For info on the dangers of SQL-injection. — Johan, May 02 '11 at 17:57
and please replace all those `$gender = $_POST['gender'];` security holes with `$gender = mysql_real_escape_string($_POST['gender']);` — Johan, May 02 '11 at 18:00

score 1 · Accepted Answer · answered May 02 '11 at 17:30

Check if your download code has a blank line before the first <?php . Remember to check any file it gets included from as well.

Also change addslashes to mysql_real_escape_string. It might not cause a problem here, but it is security hole.

If you can't find the root of your problem, you could always try base64_encode / base64_decode. It takes 30% more storage space, but it's a bullet proof way to store binary data in strings.

Just a tip:

  $fp      = fopen($tmpName, 'r');
  $data = fread($fp, filesize($tmpName));
  fclose($fp);

could be replaced with

  $data = file_get_contents($tmpName)

score 1 · Answer 2 · edited Dec 20 '11 at 09:46

I also having the same problem on the coding, but after that I found out that actually one of the including files hase empty space like below:

tool.php

line 1 
line 2 <?php
line 3  .......
line 4 ?>

line 1 is causing the problem when I include on

<?php
if($_SERVER['REQUEST_METHOD']=="GET"){  
    if(isset($_GET["ImageID"])){    
        /* below require file causing the problem */
        require_once($_SERVER['DOCUMENT_ROOT'] . "/model/Game/Tools.php");
        $image = new ClsGameImage();
        $image->Select($_GET["ImageID"]);
        header("Content-type: ".  $image->MIMEType);
        header("Content-length: " . $image->ImageSize);
        header("Content-Disposition:attachment;filename=". $image->Name0);
        echo $image->Image0;
    } 
}
?>

adorablepuppy · Answer 3 · 2011-05-02T17:27:23.247

It's possible ltrim could help in this situation if the line is being introduced by PHP. There is also a ltrim function for MySQL if it's being introduced in the database.

Also, use mysql_real_escape_string instead of addslashes.

You may want to consider serving media from a media directory instead of storing it in a database. I know this does nothing for replication purposes, but there are things you can do to propagate filesystem changes to multiple computers, if necessary.

This is obviously a preferential choice.

Mysql blob field has first line of space and therefore wont play file

3 Answers3