Can't retrieve images from DB

Question

I built a simple form for an image upload into the DB along with a description. The image and description are stored on the database but I am having a hard time retrieving/rendering (?) the images. I presume that the db connection is ok due to I can actually upload things. I will below leave you with the code and some print screens:

The table:

enter image description here

upload.php

<form action="imageUpload.php" method="post" enctype="multipart/form-data">
  <label for="userFile">Upload your file: </label>
  <input type="file" size="40" name="userFile" id="userFile"/><br />
  <br />
  <label for="altText">Description of image</label>
  <textarea class="ckeditor" name="altText" id="altText"/></textarea><br />
  <br />
  <input type="submit" class="pdf" value="Save!" />
</form>

imageUpload.php

<?php
                if ( !isset($_FILES['userFile']['type']) ) {
                   die('<p><strong>Du har inte laddat upp någon bild!</strong></p></body></html>');
                }
            ?>
                Your image:<br /><br />
                Temporary name: <?php echo $_FILES['userFile']['tmp_name'] ?><br />
                Original name: <?php echo $_FILES['userFile']['name'] ?><br />
                Size: <?php echo $_FILES['userFile']['size'] ?> bytes<br />
                Type: <?php echo $_FILES['userFile']['type'] ?></p>

            <?php
                require '../scripts/common.php';
                // Validate uploaded image file
                if ( !preg_match( '/gif|png|x-png|jpeg/', $_FILES['userFile']['type']) ) {
                   die('<p>Bara gif, png eller jpg/jpeg filer är accepterade!</p></body></html>');
                } else if ( strlen($_POST['altText']) < 9 ) {
                   die('<p>Please write more then 9 characters!</p></body></html>');
                } else if ( $_FILES['userFile']['size'] > 5000000 ) {
                   die('<p>Your image is too big!</p></body></html>');
                // Connect to database
                } else if ( !($link=mysql_connect($host, $username, $password)) ) {
                   die('<p>Could not connect to DB</p></body></html>');
                } else if ( !(mysql_select_db($dbname)) ) {
                   die('<p>Error when connecting to DB</p></body></html>');
                // Copy image file into a variable
                } else if ( !($handle = fopen ($_FILES['userFile']['tmp_name'], "r")) ) {
                   die('<p>Could not open temp file!!</p></body></html>');
                } else if ( !($image = fread ($handle, filesize($_FILES['userFile']['tmp_name']))) ) {
                   die('<p>Error when reading the temp file!</p></body></html>');
                } else {
                   fclose ($handle);
                   // Commit image to the database
                   $image = mysql_real_escape_string($image);
                   $alt = htmlentities($_POST['altText']);
                   $query = 'INSERT INTO image (type,name,alt,img) VALUES ("' . $_FILES['userFile']['type'] . '","' . $_FILES['userFile']['name']  . '","' . $alt  . '","' . $image . '")';
                   if ( !(mysql_query($query,$link)) ) {
                      die('<p>Could not save info on the DB!</p></body></html>');
                   } else {
                      die('<p>Your info has been saved!</p></body></html>');
                   }
                }
            ?>

getImage.php

<?php
    require '../scripts/common.php';
    $link = mysql_connect($host, $username, $password);
    mysql_select_db($dbname);
    $query = 'SELECT type,img FROM image WHERE id="' . $_GET['id'] . '"';
    $result = mysql_query($query,$link);
    $row = mysql_fetch_assoc($result);
    header('Content-Type: ' . $row['type']);
    echo html_entity_decode($row['img']);
?>

showimage.php

<?php
                require '../scripts/common.php';
                if ( !($link=mysql_connect($host, $username, $password)) ) {
                   die('<p>Kunde inte koppla med databasen!</p></body></html>');
                } else if ( !(mysql_select_db($dbname)) ) {
                   die('<p>Fel att läsa databasen!</p></body></html>');
                } else {
                   $query = "SELECT id,name,alt FROM image";
                   if ( !($result = mysql_query($query,$link)) ) {
                      die('<p>Kunde inte läsa databasen!</p></body></html>');
                   } else {
                      for ( $i = 0 ; $i < mysql_num_rows($result) ; $i++ ) {
                        $row = mysql_fetch_assoc($result);
                        echo '<article class="span12 post"> 
                                <div class="mask3 span3"> 
                                    <img src="getImage.php?id=' . $row['id'] . '" alt="' . $row['alt'] . '" title="' . $row['name']  .'"/>    
                                </div>
                                <div class="inside">
                                  <div class="span8 entry-content">
                                    <div class="span12">
                                        ' . $row['alt'] . '
                                    </div>
                                  </div>
                                </div>
                              </article>';
                      }
                   }
                }
            ?>

Final result at showimage.php:

enter image description here

So, any suggestions on how do I make the images appear?

Just as a general tip you should avoid using the mysql_* style functions and either use mysqli or pdo_mysql, as they have been deprecated. And they help avoid SQL injection attacks, which your script can currently be affected by. At the very least you should use mysql_real_escape_string() on any input before using it in a query — Aaron Foley, May 30 '14 at 16:17
You might also consider whether you need to store the images in the db at all (rather than a reference to them); http://stackoverflow.com/questions/527801/php-to-store-images-in-mysql-or-not has some interesting points — ChrisW, May 30 '14 at 16:27

spencer7593 · Answer 1 · 2014-05-30T17:50:57.733

1

I suspect there's an issue with "updload" of the image. The call to mysql_real_escape_string is scanning for "characters" that need to be escaped, and inserting a backslash character before any "unsafe" character.

If $image is binary data (I don't see any base64 or hexadecimal encoding/decoding going on), I suspect that you don't really want to alter the binary data.

Given what you have already got, converting the binary data into hex format might work for the INSERT (as long as the length of the SQL text doesn't exceed max_allowed_packet).

... , img ) VALUES ( ... , x'FFD8FFE00004A464946000102' )

Another option is to use the MySQL LOAD_FILE function, to read in the contents of a file located on the MySQL server host. Again, max_allowed_packet limits the size of the file that can be loaded this way.

... , img ) VALUES ( ... , LOAD_FILE('/tmp/img.jpg') )

edited May 30 '14 at 17:50

answered May 30 '14 at 16:24

spencer7593

106,611
15
112
140

If you remove the mysql_real_escape_string(), you would need to do proper checking of the file type and not rely on the $_FILES information as it can be faked and used for SQL injection. – Aaron Foley May 30 '14 at 16:27
@Kalitz: I'm not sure how "proper checking of the file type" will prevent SQL Injection. I am sure, however, that running `mysql_real_escape_string` on binary content has the potential to alter the binary content, and that binary content should NOT be included in the SQL text. The make the binary content "safe" for inclusion in a SQL statement, the two options I outlined in my answer are appropriate. – spencer7593 May 30 '14 at 16:30
OP also needs to avoid using `html_entity_decode()` when outputting the binary file. – Marcus Adams May 30 '14 at 16:47

score 0 · Answer 2 · answered May 30 '14 at 16:29

0

If the images are blobs or plain binary data use data URIS- https://developer.mozilla.org/en-US/docs/Web/HTTP/data_URIs

answered May 30 '14 at 16:29

automaton

1,091
1
9
23

That link appears to be broken – Aaron Foley May 30 '14 at 16:29
very strange. had to paste the link in full instead. – automaton May 30 '14 at 16:34

Anthony Rutledge · Answer 3 · 2014-05-30T17:08:23.317

Wild guesses here. Trying to figure it out and provide information that might help solve the problem. I have never put binary files into MySQL (so I'm not aware of escaping requirements with BLOBs, if any?????)

(Make sure it's not a CSS thing. :-))

<?php
    require '../scripts/common.php';
    $link = mysql_connect($host, $username, $password);
    mysql_select_db($dbname);
    $query = 'SELECT type,img FROM image WHERE id="' . $_GET['id'] . '"';
    $result = mysql_query($query,$link);
    $row = mysql_fetch_assoc($result);
    header('Content-Type: ' . $row['type']);
    echo html_entity_decode($row['img']);   //Is enough being echoed here?
?>

 // html_entity_decode() on a BLOB? [PHP Manual: html_entity_decode()][1]

<div class="mask3 span3"> 
    <img src="getImage.php?id=' . $row['id'] . '" alt="' . $row['alt'] . '" title="' . $row['name']  .'"/>    
</div>

If enough is being echoed in the last line of getImage.php. Is the file name required at all? If you are using the <base> HTML tag, you might not have to specify the full url.

score 0 · Answer 4 · answered May 30 '14 at 17:10

0

If inside getImage.php you echo row['img'] instead of html_entity_decode($row['img']); I believe it will work.

answered May 30 '14 at 17:10

Almis

3,684
2
28
58

Can't retrieve images from DB

4 Answers4