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
– ScaisEdge, Commented Apr 3, 2017 at 7:38
you have to read the file content using file_get_content and set to a field — Ima
– Ima, Commented Apr 3, 2017 at 7:46

Community · Accepted Answer · 2017-05-23 10:30:51Z

2

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, 2017 at 10:30

CommunityBot

11 silver badge

answered Apr 3, 2017 at 7:50

Louis Loudog Trottier

1,39714 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Louis Loudog Trottier Over a year ago

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.

Hazirah_Halim Over a year ago

It displayed Warning: : failed to open stream: Invalid argument in C:\xampp\htdocs\contractdb\filetest.php on line 28

Louis Loudog Trottier Over a year ago

change $filedata to your file path $_FILES['upload']['tmp_name'], will edit answer

Louis Loudog Trottier Over a year ago

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 Over a year ago

Also, as mentioned, you might want to escape your data;

Ima · Accepted Answer · 2017-04-03 08:02:22Z

-1

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;

answered Apr 3, 2017 at 8:02

Ima

1,10912 silver badges26 bronze badges

3 Comments

Hazirah_Halim Over a year ago

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)

Ima Over a year ago

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 Over a year ago

I don't know why the downvote here. This is not a full solution to the problem, the original author used my suggestion to troubleshoot their problem and verified the first part of the problem is not there and I think they went forward with next part of the problem

Collectives™ on Stack Overflow

PHP - file not uploaded into the database using prepared statements

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related