5

I followed this tutorial to make an image uploader. It works fine, except when I try to upload larger file sizes: It just 'changes' the file size to 64KB and only displays a small part of a larger image. So, I googled and found that 'blob' has a maximum file size of 64kb, so I changed it to longblob, which has a max file size of 4GB.

But now, trying to upload large images (1MB and larger), I get the SQL error 'MySQL server has gone away'. With images smaller than 1MB, the upload works. An image of 1400x900 with a file size of 915kb uploads, but an image of 1400x900 with a file size of 1.6mb doesn't. A gif of 250x179 with a file size of 1mb does upload. It seems like if the file size is larger than 1MB that the SQL insert function in the PHP simply doesn't connect anymore: How is this possible?

My php.ini has a maximum file size of 32MB, so that's not the issue. I am using MAMP.

HTML:

<form action="tutorial.php" method="POST" enctype="multipart/form-data">
    File:
    <input type="file" name="image" > <input type="submit" value="Upload">
</form>

PHP:

// connect to database
include 'include/config.php'; 

// file properties
$file = $_FILES['image']['tmp_name'];

if (!isset($file)){
    echo "Please select an image.";
} else {
    $image = addslashes(file_get_contents($_FILES['image']['tmp_name']));
    $image_name = addslashes($_FILES['image']['name']);
    $image_size = getimagesize($_FILES['image']['tmp_name']);

    if ($image_size==FALSE){
        echo "This is not an image";    
    }else{
        if(!$insert = mysql_query("INSERT INTO gallery VALUES ('', '$image_name', '$image')")){
            die(mysql_error());
            echo "Problem uploading the image.";
        }else{
            $lastid = mysql_insert_id();
            echo "Image uploaded.<p />Your image:<p /><image src=include/get.php?id=$lastid>";
        }
    }
}

PHP config:

//database credentials
$username = "root";
$password = "root";
$hostname = "localhost"; 

//connection to the database
mysql_connect($hostname, $username, $password) or die(mysql_error()); 
mysql_select_db('imandra') or die(mysql_error()); 
//echo "Connected to MySQL";

PHP get:

include 'config.php'; 

$id= addslashes($_REQUEST['id']);

$image = mysql_query("SELECT * FROM gallery WHERE id='$id'");
$image = mysql_fetch_assoc($image);
$image = $image['image'];

header("Content-type: image/jpeg");

echo $image;
halfer
  • 19,824
  • 17
  • 99
  • 186
Lisa
  • 897
  • 9
  • 27
  • So your upload_max_filesize and post_max_size directives are both 32MB? – j08691 Jul 19 '14 at 22:13
  • Yes, I've checked it in my ini file, everything there seems fine. I used [this](http://www.sitepoint.com/upload-large-files-in-php/) as guideline. – Lisa Jul 19 '14 at 22:16
  • Whilst this is an interesting problem, it is generally considered best practice to save files to the filing system, and just to record the file reference in the database. Databases are not ideally suited to storing large binary objects, and doing so will make it impossible to restore data from backups without handling extremely large files. – halfer Jul 19 '14 at 22:18
  • Also, for insert queries, consider this form: `INSERT INTO gallery (col1, col2) VALUES (val1, val2)`. That way you are future-proofed if you change the format of the table, and you don't have to specify an empty/null to skip over the primary key. – halfer Jul 19 '14 at 22:20
  • Have you checked the MySQL log? – Salman Jul 19 '14 at 22:20
  • This is the log (If I googled the right log haha. If it's not, please explain where I can find it. First one is the failed attempt, second one a successful one): 140720 0:25:40 5 Connect root@localhost on 5 Init DB imandra 5 Quit 140720 0:25:54 6 Connect root@localhost on 6 Init DB imandra 6 Query INSERT INTO gallery VALUES @halfer: I'll be sure to check it out, I just wanted to try this database method as a different option, but I think I'll use the file storing in the future. Works better haha :). But I'd still like to know what causes this issue. – Lisa Jul 19 '14 at 22:28
  • I believe `addslashes` to be insufficient to protect against SQL injection. Use `mysql_real_escape_string` in the short term, and migrate to a database library that is still supported in the medium term. – halfer Jul 19 '14 at 22:28
  • Ah, in relation to the question, could there be a max size for MySQL queries? I am not sure if there is a database setting for this. – halfer Jul 19 '14 at 22:29
  • Maybe that's it, because it fails on the query.. I found this but I'm not that technical. Can anyone tell me if there's anything in [here](http://msdn.microsoft.com/en-us/library/ms143432.aspx)? – Lisa Jul 19 '14 at 22:30
  • http://dev.mysql.com/doc/refman/5.7/en/gone-away.html - might be hitting the limit mentioned in the 7th item. – Tieson T. Jul 19 '14 at 22:32
  • @Lisa That's a link to MSSQL server, so no. – Tieson T. Jul 19 '14 at 22:33
  • Haha like I said, not that technical ;). I think this is it? The thing causing the issue? "*you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB.*" – Lisa Jul 19 '14 at 22:33
  • Ah, [this answer](http://stackoverflow.com/a/4813924/472495) warns against the INSERT approach too - see the comments. Moving to a better database engine (e.g. PDO) might allow you to do this outside of a traditional query. – halfer Jul 19 '14 at 22:35
  • @Lisa I assume as much, but I don't have a MySQL instance handy to verify, so you'll have to try it or wait for an actual answer from someone who's not just guessing (as I am at the moment). – Tieson T. Jul 19 '14 at 22:37
  • Have a look [at this answer](http://stackoverflow.com/questions/6346319/php-pdo-mysql-inserting-into-mediumblob-stores-bad-data), which uses PDO to accomplish the same thing. That uses parameterisation, which gets around any query string length limitation. – halfer Jul 19 '14 at 22:40
  • 1
    OK. It seems like the most likely explanation, with the maximum query size of 1MB. In the mean time I'll switch to an upload with the filing system, as was recommended :)! I'll wait for the definitive answer, I'm quite curious. Thanks for the tips @halfer ! Maybe it's time to school myself in PDO then! – Lisa Jul 19 '14 at 22:41

1 Answers1

4

You might want to take a look at the settings in your mysql configuration file (my.cnf).

Increase the size of the max_allowed_packet setting, e.g.

max_allowed_packet=50M
Michael
  • 1,247
  • 1
  • 8
  • 18