Insert Blobs in MySql databases with php

Question

I am trying to store an image in the DataBase, for some reason it doesn't seem to work. Here's the structure of my table.

mysql> describe ImageStore;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| ImageId | int(11)  | NO   | PRI | NULL    |       |
| Image   | longblob | NO   |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

And here is my query which inserts the image or at least thats what it should:

//Store the binary image into the database
                $tmp_img = $this->image['tmp_name'];
                $sql = "INSERT INTO ImageStore(ImageId,Image)               
                VALUES('$this->image_id','file_get_contents($tmp_image)')";
                mysql_query($sql);

If I print the value of file_get_contents($tmp_image), then there is a tons of data on the screen. But this value doesn't get stored in the database and that is the issue that I'm facing.

score 46 · Accepted Answer · edited May 23 '17 at 12:26

Problem

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','file_get_contents($tmp_image)')";

This creates a string in PHP named $sql. Forget about MySQL for a minute, because you're not executing any query yet. You're just building a string.

The magic of PHP means that you can write a variable name — say, $this->image_id — inside the double quotes and the variable still gets magically expanded.

This functionality, known as "variable interpolation", does not occur for function calls. So, all you're doing here is writing the string "file_get_contents($tmp_image)" into the database.

Solution (1)

So, to concatenate the result of calling file_get_contents($tmp_image), you have to jump out of the string and do things explicitly:

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','" . file_get_contents($tmp_image) . "')";

(You can see even just from the syntax highlighting how this has worked.)

Solution (2)

Now the problem you have is that if the binary data contains any ', your query is not valid. So you should run it through mysql_escape_string to sanitize it for the query operation:

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','" . mysql_escape_string(file_get_contents($tmp_image)) . "')";

Solution (3)

Now you have a really big string, and your database is getting bulky.

Prefer not storing images in databases, where you can help it.

@LightnessRacesinOrbit. Re "prefer not storing large files in db".. well that depends on *what* db. — Pacerier, Jul 11 '12 at 17:14

score 4 · Answer 2 · answered Aug 13 '11 at 19:07

4

To expand on Tomalak's comment, you can't run a function inside of quotes.

Try:

$sql = "INSERT INTO ImageStore(ImageId,Image)               
        VALUES('{$this->image_id}','".file_get_contents($tmp_image)."')";

answered Aug 13 '11 at 19:07

Jess

8,628
6
49
67

score 3 · Answer 3 · answered Aug 13 '11 at 19:08

3

try this:

$tmp_img = $this->image['tmp_name'];
$sql = "INSERT INTO ImageStore(ImageId,Image)               
  VALUES('$this->image_id','" . addslashes(file_get_contents($tmp_image)) . "')";
mysql_query($sql);

answered Aug 13 '11 at 19:08

classic

544
2
7

4

"Please note that use of addslashes() for database parameter escaping can be cause of security issues on most databases." - [PHP Manual](http://php.net/manual/en/function.addslashes.php) – Tamás Bolvári Nov 19 '14 at 13:48
PDO:: uses prepare(), but it should use also addslashes() something like, so we shall prefer the PHP.Native solution that is addslashes(). It works prefectly. – jacouh Feb 17 '21 at 13:02

score 2 · Answer 4 · edited Oct 19 '12 at 10:02

2

As mentioned you are just saving the string "file_get_contents($tmp_image)" into the db but you need to run the function file_get_contents instead
dont forget to hash the image using a hashing algorithm such as base64_encode before saving it to the db.

edited Oct 19 '12 at 10:02

amesh

1,311
3
21
51

answered Aug 13 '11 at 19:15

Shahrokhian

1,100
13
28

images may contents some illegal words (such as -- , " , ' and so on...) and this may cause problem while we are executing the query – Shahrokhian Aug 14 '11 at 10:57
without hashing or any other checks this may cause sql injection attacks – Shahrokhian Aug 14 '11 at 10:58
2

You don't need hashing at all. Just escape single quotes and backslashes to keep from breaking out of string mode. – Lightness Races in Orbit Aug 14 '11 at 13:03
You are right , but there is another option , we can send and recive images like a string to and from the browser , most browser can convert a base64 string to image , but for sql injections and custom errors i trust your solution – Shahrokhian Aug 14 '11 at 19:21

Insert Blobs in MySql databases with php

4 Answers4

Problem

Solution (1)

Solution (2)

Solution (3)

Linked

Related