Sanitising file content for Blob storage

[learning_brain]

I thought this was going to be easy....

I have a file upload input and then use..

$fileHandle = fopen($fileUpload, "r");
        $fileContent = fread($fileHandle, $fileUpload_size);
        $fileContent = addslashes($fileContent);

which I thought would prepare it fine..

Now my sanitise function is

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

Which can handle a variety of values dependent on content type.

The insert MYSQL is

$insertSQL = sprintf("INSERT INTO FILEATT (ATTNOTEID, CREATEDATE, CREATEDBY, SUBJECT, ATTACHMENT, LONGNOTE, FILENAME, FILE, FILETYPE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString( $_POST['hiddenfieldnotesid'], "int"),
                       GetSQLValueString( $_POST['hiddenfieldcreated'], "date"),
                       GetSQLValueString( $_POST['hiddenfieldcreatedby'], "text"),
                       GetSQLValueString( $_POST['textfieldtitle'], "text"),
                       GetSQLValueString($_POST['path'], "text"),
                       GetSQLValueString($_POST['longnote'], "text"),
                       GetSQLValueString($fileUpload_name, "text"),
                       GetSQLValueString($fileContent, "text"),
                       GetSQLValueString($fileUpload_type, "text")
                       );

This seems to work fine, but when I try to upload a larger image (<INPUT TYPE="hidden" NAME="MAX_FILE_SIZE" VALUE="1000000">, it seems to store fine but only displays the upper half of the image when downloading.

I get loads of problems with other file types - docs get corrupted - pdf's don't download successfully - bit of a nightmare!

So I thought - am I not preparing correctly?

The first move was...

$insertSQL = sprintf("INSERT INTO FILEATT (ATTNOTEID, CREATEDATE, CREATEDBY, SUBJECT, ATTACHMENT, LONGNOTE, FILENAME, FILE, FILETYPE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString( $_POST['hiddenfieldnotesid'], "int"),
                       GetSQLValueString( $_POST['hiddenfieldcreated'], "date"),
                       GetSQLValueString( $_POST['hiddenfieldcreatedby'], "text"),
                       GetSQLValueString( $_POST['textfieldtitle'], "text"),
                       GetSQLValueString($_POST['path'], "text"),
                       GetSQLValueString($_POST['longnote'], "text"),
                       GetSQLValueString($fileUpload_name, "text"),
                       $fileContent,//direct entry with addslashes value
                       GetSQLValueString($fileUpload_type, "text")
                       );

With the result...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\0JFIF\0
\0\0d\0d\0\0ÿì\0Ducky\0
\0\0\0\0<\0\0ÿî\0Adobe\0dÀ\0\0\0
ÿÛ\0„\0' at line 1

So I tried

$insertSQL = sprintf("INSERT INTO FILEATT (ATTNOTEID, CREATEDATE, CREATEDBY, SUBJECT, ATTACHMENT, LONGNOTE, FILENAME, FILE, FILETYPE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                        GetSQLValueString( $_POST['hiddenfieldnotesid'], "int"),
                        GetSQLValueString( $_POST['hiddenfieldcreated'], "date"),
                        GetSQLValueString( $_POST['hiddenfieldcreatedby'], "text"),
                        GetSQLValueString( $_POST['textfieldtitle'], "text"),
                        GetSQLValueString($_POST['path'], "text"),
                        GetSQLValueString($_POST['longnote'], "text"),
                        GetSQLValueString($fileUpload_name, "text"),
                        '$fileContent',//direct entry with addslashes value
                        GetSQLValueString($fileUpload_type, "text")
                        );

And got

Unknown column '$fileContent' in 'field list'


Arrrgghhh - what am I doing wrong?

 

[MaestroFX1]

....it seems to store fine but only displays....

Save the binary data as a file and check it.

 

[learning_brain]

Done - no it's not saving the entire content to db.

In some cases, it will save 0kb.....

 

[MaestroFX1]

Howdy!

It is bit confusing when you say "....This seems to work fine, but when I try to upload a larger image...”.
Hence, I assumed it worked “perfectly” for smaller files, but didn't work “perfectly” for larger.

Also, you also said it shows you the upper half of the image.
So, there must be half of the complete binary data.

Documents like PDF are shown as corrupt, which is quite obvious as PDF files are checked completely for their integrity.
Images aren't and so you see the upper half.

Okay, let’s get back to the point.

#1
Have you checked the script on local host?
(As you might have experienced MySQL timeouts here are common for large strings.
My compressed336kb/uncompressed2105kb SQL hasn’t been imported yet.
It is still sending requests to stoli.x10hosting.com….has been 20min and I’m on 100Mbps.)
So, check it locally first.

If is working locally, the direct inference would be your date strings in POST aren’t getting completely executed.
So, they are too large for these servers and would be clear indication of upload restriction placed.

If not, correct the script locally first.

#2
Use phpMyAdmin to browse to blob data in the table.
Check the data; it should be complete for smaller files and half for larger file.

I’m going to write further only after your response.

 

[misson]

Don't roll your own sanitization. Unless you really know what you're doing, there's an edge case you'll miss.

Rather than sanitizing, use a prepared statement. You won't have to worry about truncation, which might be causing the problems you're experiencing and can also be used for some attacks.

Rather than inserting the file content directly in the statement, try LOAD_FILE. The DB user needs the FILE privilege.

$fields = array(
    'hiddenfieldnotesid' => 'attnoteid', 
    'hiddenfieldcreated' => 'createdate', 
    'hiddenfieldcreatedby' => 'createdby', 
    'textfieldtitle' => 'subject', 
    'path' => 'attachment', 
    'longnote' => 'longnote',
    'filename' => 'filename',
    'filetype' => 'filetype'
);

$columnStr = '`' . implode('`,`', $fields) . '`,`file`';
$paramStr = ':' . implode(',:', array_keys($fields)) . ',LOAD_FILE(:file)';
$insertQuery = $db->prepare("INSERT INTO  fileatt ($columnStr) VALUES ($paramStr)");

$args = array_intersect_key($_POST, $fields);
$args['filename'] = $fileUpload_name;
$args['filetype'] = $fileUpload_type;
$args['file'] = $fileUpload_name;

$insertQuery->execute($args);

 

[learning_brain]

Many thanks to both of you.

Maestro - I wasn't sure at first if this was going to help because I'm not hosted on X10... but it did help...

misson - thanks for this, but as put in the first post, I did try inserting directly without rolling.. with the same problem. In addition, I don't really want to use the LOAD_FILE because this is limited to files hosted on the same server and this would mean copying the file - reading - deleting... a bit messy.

This particular problem is for an XAMPP installation on a server on a closed network. After trying really simple execution, I was still getting the same problem so I investigated further and found that it was down to a schoolboy error..... field definition.

After changing the field to longblob instead of blob, all my problems have melted away...... duh.

 

[MaestroFX1]

@learning_brain
------
Maestro - I wasn't sure at first if this was going to help because I'm not hosted on X10... but it did help...
------

Good to hear that!
I came across a few hiccups too when I was trying blob storage a couple of months back.
What helped me was a step-by-step approach and found out that I had file upload restriction.