PHP - file not uploaded into the database using prepared statements

Question

I made an upload file code without prepared statements. The file is successfully uploaded. But when I add prepared statements to the code, the contents of the file is not uploaded. Only the file name, size and type and uploaded in the database.

This is the code:

PHP:

<?php
include("config.php");
error_reporting( ~E_NOTICE );
if(isset($_POST['submit'])  ){

//user has the option whether to upload the file or not
if ($_FILES['upload']['size'] != 0 ){

$filename = $con->real_escape_string($_FILES['upload']['name']);
$filedata= $con->real_escape_string(file_get_contents($_FILES['upload']['tmp_name']));
$filetype = $con->real_escape_string($_FILES['upload']['type']);
$filesize = intval($_FILES['upload']['size']);

$allowed =  array('zip','rar', 'pdf', 'doc', 'docx');
$ext = pathinfo($filename, PATHINFO_EXTENSION);

    if(in_array($ext, $allowed)){           

        if($filesize < 2000000) {

            //$query = "INSERT INTO contracts(`filename`,`filedata`, `filetype`,`filesize`) VALUES ('$filename','$filedata','$filetype','$filesize')"; <- old code line

            $query = "INSERT INTO contracts(`filename`,`filedata`, `filetype`,`filesize`) VALUES (?,?,?,?)";

            $stmt = $con->prepare($query);
            $stmt->bind_param("sbsi", $filename, $filedata, $filetype,$filesize);
            $stmt->execute();

            if ($stmt->errno){
            echo "FAILURE!!! " . $stmt->error;
            } else {
            echo "<br>Inserted";
            }
            $stmt->close(); 

            /* if ($con->query($query) === TRUE) <- old code line
            {
            echo "Uploaded<br>";

            } else {
            echo "Error! <br>" . $con->error;
            }   */

        } else {

        $errorMsg = "Sorry, your file is too large. Only 2MB is allowed";
        }

    }else{
        $errorMsg = "Sorry, only zip, rar, pdf, doc & docx are allowed.";        
    }

//if user has no file to upload then proceed to this else statement
} else {

$filename = $con->real_escape_string($_FILES['upload']['name']);
$filetype = $con->real_escape_string($_FILES['upload']['type']);
$filesize = intval($_FILES['upload']['size']);

//$query = "INSERT INTO contracts(`filename`,`filedata`, `filetype`,`filesize`) VALUES ('$filename','$filetype','$filesize')"; <- old code line

$query = "INSERT INTO contracts(`filename`,`filetype`,`filesize`) VALUES (?,?,?)";

            $stmt = $con->prepare($query);
            $stmt->bind_param("ssi", $filename, $filetype,$filesize);
            $stmt->execute();

            if ($stmt->errno){
            echo "FAILURE!!! " . $stmt->error;
            } else {
            echo "<br>Inserted";
            }
            $stmt->close(); 

        /*  if ($con->query($query) === TRUE) <- old code line
            {
            echo "Uploaded<br>";

            } else {
            echo "Error! <br>" . $con->error;
            }   */

}

$con->close(); 
}   

?>

HTML:

<html><head></head>
<body>

<form method="post" action="" enctype="multipart/form-data">
<?php echo $errorMsg; ?>
Upload File:
<input type="file" name="upload" /><br> 
<input type="submit" name="submit" value="Submit"/>
</form>
</body>
</html>

Why is the contents of the file is not uploaded and missing in the database with prepared statements? What is wrong with my code?

in your query you simply insert `filename`,`filetype`,`filesize` .. and not the file itself .. — ScaisEdge, Apr 03 '17 at 07:38
@Deadooshka so I don't have to use prepared statements to prevent sql injection for uploading file? — Hazirah_Halim, Apr 03 '17 at 07:46
you have to read the file content using file_get_content and set to a field — Ima, Apr 03 '17 at 07:46
Why do you want to store file content into database? My suggestion is store file path in database & save the file itself in disk. — Dipanwita Kundu, Apr 03 '17 at 07:53
this is purely a choice OP have to make depending on his needs. It's not because evryone has a car than you don't need a truck. This said, i would suggest the same as you did. — Louis Loudog Trottier, Apr 03 '17 at 07:55
Show us the database structure also. And print the `$filedata` to know the exact content of the variable. — Dipanwita Kundu, Apr 03 '17 at 07:57
Not related, but you should not escape your data when you use a prepared statement. — jeroen, Apr 03 '17 at 07:58
And why are you trying to store information in the database when the file-size is 0 (the `else` section)? — jeroen, Apr 03 '17 at 08:04
Are you trying to store the SAME FILE while testing this script as you did when successfully testing the other script??? — RiggsFolly, Apr 03 '17 at 08:04
Have you checked `$_FILES['upload']['error']` to see if an error code exists and then check what that code means? — RiggsFolly, Apr 03 '17 at 08:06
If you escape the contents of the file, you risk changing the bytes in the file. Possibly the escaping is causing byte 0 to become a zero and hense everything thinks the file has no content other than an end of file — RiggsFolly, Apr 03 '17 at 08:09
If you want to store a file into the database it is nornal to convert the bytes in the file into a base64encoded string before attempting to store the file into the table and then convert it back when you want to see it again — RiggsFolly, Apr 03 '17 at 08:10

score 1 · Accepted Answer · edited May 23 '17 at 10:30

1

Have you considered using http://php.net/manual/en/mysqli-stmt.send-long-data.php ((PHP 5, PHP 7) (Assuming your $conn object is good and working).

$stmt = $con->prepare($query);
$null = NULL;
$stmt->bind_param("sbsi", $filename, $filedata, $filetype,$filesize);
$stmt->send_long_data(1, file_get_contents($_FILES['upload']['tmp_name'])); 
$stmt->execute();

PS : 1 represent the bind argument associated staring from 0, in your case the blob (b) is 2nd, so 1 on a 0 count.

THis is untested and i never used it, i just knew i could be done. Hopefully it will help.

More on the oracle blog : https://blogs.oracle.com/oswald/entry/php_s_mysqli_extension_storing

You might want to escape your binary, see Inserting Binary into MySQL BLOB

Another version from php.net :

$stmt = $con->prepare($query);
$null = NULL;
$stmt->bind_param("sbsi", $filename, $filedata, $filetype,$filesize);
$fp = fopen($_FILES['upload']['tmp_name'], "r");
while (!feof($fp)) {
    $stmt->send_long_data(1, fread($fp,$filesize));
}
fclose($fp);
$stmt->execute();

edited May 23 '17 at 10:30

Community

1
1

answered Apr 03 '17 at 07:50

Louis Loudog Trottier

1,367
13
26

You could also consider storing only he filepath in your DB and store the file as a file, but this is purely a choice you have to mkae on you needs. – Louis Loudog Trottier Apr 03 '17 at 07:53
It displayed `Warning: : failed to open stream: Invalid argument in C:\xampp\htdocs\contractdb\filetest.php on line 28` – Hazirah_Halim Apr 03 '17 at 08:01
change $filedata to your file path $_FILES['upload']['tmp_name'], will edit answer – Louis Loudog Trottier Apr 03 '17 at 08:03
Good to know, never used it. The only time i stored images in a db was somewhat sensitive data and was years ago. Sorry for making you my tester :) – Louis Loudog Trottier Apr 03 '17 at 09:34
Also, as mentioned, you might want to escape your data; – Louis Loudog Trottier Apr 03 '17 at 09:36

score 0 · Answer 2 · answered Apr 03 '17 at 08:02

If your database column type of filedata is long enough to accept file content, you can do this.

Check the field type of filedata. Suggestion:make type of column filedata a text or blob
Now check if you file is actually uploaded to server. You can do this by printing the file content

Use following code to check if file is actually uploaded

if ($_FILES['upload']['size'] != 0 ){

$filename = $con->real_escape_string($_FILES['upload']['name']);
$filedata= $con->real_escape_string(file_get_contents($_FILES['upload']['tmp_name']));
$filetype = $con->real_escape_string($_FILES['upload']['type']);
$filesize = intval($_FILES['upload']['size']);

$allowed =  array('zip','rar', 'pdf', 'doc', 'docx');
$ext = pathinfo($filename, PATHINFO_EXTENSION);

//Print the details to check if file is actually uploaded. 
//Note: Remove this line after debugging
print_r([$filename, $filedata, $filetype, $filesize, $ext]); exit;

It prints `Array ( [0] => Document 1.docx [1] => a long line of gibberish unicode [2] => application/vnd.openxmlformats-officedocument.wordprocessingml.document [3] => 10964 [4] => docx) — Hazirah_Halim, Apr 03 '17 at 08:22
This means the file is being uploaded successfully. Now check your db structure to find if the long string fit in the column `filedata`. I hope you already fixed this. — Ima, Apr 03 '17 at 11:37

PHP - file not uploaded into the database using prepared statements

2 Answers2