Problems with storing images in a mySQL database

Question

** Solved **
I have a bit of a problem with my code

i have to store images into a mysql database using php and a HTML form. I have followed a guide, but i doesnt really work. It uploads the file to the server... and then it gets abandoned... at least not stored.

for the code... the language of text and variables is... Dutch if you have problems with that part ill be glad to help out.

The database layout of the target table is:

Fotos

fotonr        int(10)                       Index number
album         varchar(20)                   Grouping catagory(not needed)
Lid           int(4)                        Index number of the member that placed it
type          varchar(20)                   To store what kind of image it is
image         blob                          The image itself

for that i use the following segment(database links are not in the file, already build before)

<p>
<?php
$target_path= "images/";
echo "checking file upload!<br />";
if(isset($_FILES['file']))
{
    echo"SET!!!<br />";
    if(isset($_POST['album']))
    {
        $album=trim($_POST['album']);
        if($album!="")
        {
            $album=  stripslashes($album);
        }
        else $album="Niet Ingedeeld/";
    }
    else $album="Niet Ingedeeld/";
    $myalbum=mysql_real_escape_string($album);
    $target_path=$target_path.$album;
    foreach($_FILES['file']['tmp_name'] as $key=>$afbeelding)
    {
        echo $_FILES['file']['name'][$key]."<br />";

        if ($_FILES['file']['tmp_name'][$key]!=''){
        $size = getimagesize($_FILES['file']['tmp_name'][$key]);
        $width=$size[0];
        $height=$size[1];
        echo "groote: ".$width."x".$height."<br />";
        if($width>800&&$height>600)
        {
            echo "Uw afbeelding is te groot!(maximaal 800x600)<br />";
        }
        else
        {

            $mynr= mysql_real_escape_string($_SESSION['nummer']);
            /*$type=exif_imagetype($_FILES['file']['tmp_name'][$key]);*/
            $type=$size[mime];
            echo 'Het type was:'.$type.'<br /> ';
            if($type=="image/gif" ||$type=="image/jpeg" ||$type=="image/bmp" ||$type=="image/png" ||$type=="image/gif" ){
            $mytype =mysql_real_escape_string($type);
            $tmpName  = $_FILES['file']['tmp_name'][$key];

            /*$fp      = fopen($tmpName, 'r');
            $content = fread($fp, filesize($tmpName));
            $mycontent = mysql_real_escape_string($content);*/
            $content = file_get_contents($tmpName); 
            $data = unpack("H*hex", $content ); 
            $mycontent = '0x'.$data['hex']; 


            $sql="INSERT INTO`nacverk`.`Foto` (`album` , `lid` ,`image`,`type` )VALUES ('$myalbum' ,'$mynr','$mycontent', '$mytype')";

            $result=mysql_query($sql); 
            /*fclose($fp);*/
            if(!$result)
            {
                echo "<h1>Bestand uploaden mislukt!<br /> ".mysql_error()."<br /></h1>";
            }
            else
            {
                echo "<h1>Bestand Succesvol geupload!<br /></h1>";
            }
            }
            else{
                echo "<h1> NOD32 detected a viral intrusion!<br /></h1>";
            }

        }

    }}
}
mysql_query("OPTIMIZETABLE `Foto` ");
?>
</p><hr />
<h3> Upload hier uw Foto's!</h3>
<hr />
<p>
<form enctype="multipart/form-data" action="" method="post" name="foto-upload">
<input name="MAX_FILE_SIZE" value="10485760" type="hidden">
Uw afbeeldingen:<br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
<input name="file[]"  type="file"><br />
Het album waar ze aan toegevoegd moeten worden:<br />
<input name="album" type="text" maxlength="20"
<?php if(isset($_GET['album']))echo ' value="'.$_GET['album'].'" '; ?>><br />
<input value="Submit" type="submit">
</form>
</p>
<hr />

It falls through till the part where it needs to get uploaded to the Database...
Then it triggers the SQL error saying: Query is empty.

Thank you for you time!

generally better to store image file in the *file* system not the db. — , Dec 30 '11 at 22:39
@Dagon: It would be a good idea to store images in the db, for instance, if you need images to obey transaction isolation or rollback, or go away atomically when you delete the corresponding row, or get included in database backups. — Bill Karwin, Dec 30 '11 at 22:52

score 2 · Accepted Answer · answered Dec 30 '11 at 22:50

2

Keep in mind the maximum length for a BLOB data type is 64KB. It's pretty common for images to be larger, so perhaps you should use MEDIUMBLOB.

See http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html for more details.

answered Dec 30 '11 at 22:50

Bill Karwin

538,548
86
673
828

dar7yl · Answer 2 · 2012-01-03T01:49:57.793

1

Here's your problem:

  $fp      = fopen($tmpName, 'r');
  $content = fread($fp, filesize($tmpName));
  $content = addslashes($content);

You are interpreting the file contents as text, not as binary data.

You must present the data to the mysql server in binary format. Don't addslashes to it. (redundant enough?)

(edit) Try this code:

  $content = file_get_contents($tmpName);
  $data = unpack("H*hex", $content );
  $content = '0x'.data['hex'];

edited Jan 03 '12 at 01:49

answered Dec 30 '11 at 23:11

dar7yl

3,727
25
20

the addslashes was from a tutorial i read, would mysql_real_escape_string($content) be better? – Flying Dutch Boy Jan 02 '12 at 15:39
1

Yes, addslashes is not as good. You can read an explanation from PHP security expert Chris Shiflett here: http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string – Bill Karwin Jan 02 '12 at 17:57

score 0 · Answer 3 · answered Dec 30 '11 at 22:45

As I can't test this just now I'm not 100% sure, but I'm pretty sure $_POST['file[]'] and $_FILES['file[]'] aren't working. $_POST['file'] and $_FILES['file'] should. I assume you have an input somewhere like

<input type="file" name="file[]" />

which would allow you to upload multiple files which should be stored in $_FILES['file'], which itself will then be an array. The [] indicate that it's going to be a multi-valued key, which means you need to access it as an array in the PHP site.

From what I understand from your post you're learning this, so please allow me to advise you not to store the images in the database unless you have a good reason to do so. There are various reasons why you shouldn't, which I don't want to go in to here. You can find a decent old school tutorial on file uploads on tizag.com, http://www.tizag.com/phpT/fileupload.php.

I hope this helps. Good luck and enjoy :)

score 0 · Answer 4 · edited May 23 '17 at 12:33

0

You seem to be missing code to connect to the database.

$db = mysql_connect("localhost", "mysql_user", "mysql_password");

The resulting database handle should be passed as the second argument to mysql_query:

$result = mysql_query($query, $db);

To make it easier to troubleshoot, write MySQL error information to the webpage:

echo "<br>Error " . mysql_errno($db) . ": " . mysql_error($db) . "<br>";

Here's an answer I wrote earlier with a simple example of how to store an image in MySQL from PHP.

edited May 23 '17 at 12:33

Community

1
1

answered Dec 30 '11 at 22:45

Andomar

232,371
49
380
404

The connection material is handled in an other section. it also uses $db_selected = mysql_select_db(...) to select the right database. but i should indeed print error messages – Flying Dutch Boy Jan 02 '12 at 15:41

score 0 · Answer 5 · answered Dec 31 '11 at 03:11

Why not store the reference information in the DB and file in a folder on the server.

for example

fotonr        int(10)                       Index number
album         varchar(20)                   Grouping catagory(not needed)
Lid           int(4)                        Index number of the member that placed it
type          varchar(20)                   To store what kind of image it is
image         varchar(64)                   Path to The image itself

This way if you need to delete an image you know the path to the image and can simply unlink, accessing images and queries on the database would be faster without blob.

Problems with storing images in a mySQL database

5 Answers5