Saving Files as blob in database ajax php pdo

Question

$fileCount = count($_FILES);
for ($i = 0; $i < $fileCount; $i++) {
    $fp = fopen($_FILES["file_".$i]['tmp_name'], 'rb');
    $stmt4 = $dbh - > prepare("INSERT INTO files_tbl (pin,remarks,fileblob,file_type,nameoffile,filesize) VALUES (?,?,?,?,?,?)");
    $stmt4 - > bindValue(1, $pin, PDO::PARAM_STR);
    $stmt4 - > bindValue(2, $remarks, PDO::PARAM_STR);
    $stmt4 - > bindParam(3, $fp, PDO::PARAM_LOB);
    $stmt4 - > bindParam(4, $_FILES["file_".$i]['type'], PDO::PARAM_STR);
    $stmt4 - > bindValue(5, $_FILES["file_".$i]['name'], PDO::PARAM_STR);
    $stmt4 - > bindValue(6, $_FILES["file_".$i]['size'], PDO::PARAM_STR);
    $stmt4 - > execute();
}

This is how i insert file as blob in php. It is saving a file but it is not saving properly. When i say it is not saving properly i mean something is wrong along the way. When i compare saving the file using my project and manually adding the file in XAMPP there is a difference in fileblob for example i save a file manually in xampp the fileblob is [BLOB - 488.9 KiB] when i use the project is becomes [BLOB - 479.2 KiB]. I think this the reason when i try to show the file from database it is showing a blank page(when the file i show is the file i insert using project) but if the file i try to show is the file i insert manually in xampp it is showing the file.

What could be wrong in my insert?why am i not saving the proper blob

UPDATE

<input type="file" id="filecontent" name="filecontent" multiple="">

ajax

    var file = $('#filecontent')[0].files;
for (var i = 0; i < file.length; i++) {
    formData.append("file_" + i, file[i]);

    //more data are passed to formData
    //formData.append("file", file[i]);
    console.log(file[i]);
}

$.ajax({
    url: '../include/AddNew.php',
    type: 'POST',
    dataType: "json",
    data: formData,
    processData: false, // tell jQuery not to process the data
    contentType: false, // tell jQuery not to set contentType
    success: function(data) {
        console.log(data);
        alert(data.message);
        //window.location.reload(true);
    },
    error: function(data) {
        //alert("Error!"); // Optional
    }
});

UPDATE

when i tried to put print_r($_FILES); before the line for ($i = 0; $i < $fileCount; $i++) { the output is

Array
(
    [file_0] => Array
        (
            [name] => whomovedmycheese - Copy.pdf
            [type] => application/pdf
            [tmp_name] => C:\Users\HogRider\xampp\tmp\phpE775.tmp
            [error] => 0
            [size] => 500624
        )

    [file_1] => Array
        (
            [name] => whomovedmycheese.pdf
            [type] => application/pdf
            [tmp_name] => C:\Users\HogRider\xampp\tmp\phpE786.tmp
            [error] => 0
            [size] => 500624
        )

)

UPDATE

Answer 1

As per PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data, try using the following line to construct your PDO object:

$dbh = new PDO($dsn, $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES latin1 COLLATE latin1_general_ci"));

Explanation

I think there are, as Ben M notes in the linked question, two bad design decisions at work here.

There is this concept of a connection charset. The idea is that the SQL text can be in any charset and is then converted upon retrieval by the SQL server.

This does not work that well with binary data as it is not text and, thus, must not, by definition, be in any charset, but is still transferred using string literals.

This issue can be worked around by quoting BLOB data during transfer (either using the BASE64_* functions or by hex-escaping) and, indeed, that is what many people are doing.

The second design decision is in PDO/PHP: PDO does not do any charset conversion (it can’t, because strings in PHP are inherently charset-agnostic) so PHP is the only (or one of the few languages) where the choice of the SQL transfer charset is actually important because it needs to match the encoding the input strings are actually in.

In other languages, the transfer charset just needs to be expressive enough to encompass any characters that might be used in strings. In today’s world of emojis, this, most likely is only guaranteed by unicode charsets (utf-8 and the like). However, none of these are binary-safe (in that not every possible combination of bytes yields a valid string) so even if we could work around the PHP issue, we’d still be left with problem #1.

In an ideal world, SQL commands would always be in the ASCII charset during transfer and every string value would have a charset argument, of which “binary” could be a possible value, supplied with it. MySQL actually has such a construct for strings, which it calls an “introducer”. “_binary”, however, does not seem to be a valid value.

This charset information would then be used by the other end to convert the string value into its the native charset (either the column’s for client-to-server transfers or the programming language’s string charset for server-to-client transfers).

That way, the only thing that would have to be escaped in BLOB values would be the string delimiter (" or ').

Answer 2

You can try

MySQL

CREATE TABLE files (
    id   INT           AUTO_INCREMENT PRIMARY KEY,
    mime VARCHAR (255) NOT NULL,
    data BLOB          NOT NULL
);

PHP

  class BlobDemo {

        const DB_HOST = 'localhost';
        const DB_NAME = 'nameofdb';
        const DB_USER = 'username';
        const DB_PASSWORD = 'password';

        /**
         * PDO instance
         * @var PDO 
         */
        private $pdo = null;

        /**
         * Open the database connection
         */
        public function __construct() {
            // open database connection
            $conStr = sprintf("mysql:host=%s;dbname=%s;charset=utf8", self::DB_HOST, self::DB_NAME);

            try {
                $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD);
                //for prior PHP 5.3.6
                //$conn->exec("set names utf8");
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }

        /**
         * insert blob into the files table
         * @param string $filePath
         * @param string $mime mimetype
         * @return bool
         */
        public function insertBlob($filePath, $mime) {
            $blob = fopen($filePath, 'rb');

            $sql = "INSERT INTO files(mime,data) VALUES(:mime,:data)";
            $stmt = $this->pdo->prepare($sql);

            $stmt->bindParam(':mime', $mime);
            $stmt->bindParam(':data', $blob, PDO::PARAM_LOB);

            return $stmt->execute();
        }
}

$blobObj = new BlobDemo();

// test insert pdf
$blobObj->insertBlob('pdf/prova.pdf',"application/pdf");

work for me,I try with PDF,JPG and HTML for insert update and view.