P.316 Uploading File Named 'picture' And Storing In Mediumblob

[bnorcom]

Instead of moving the file to a folder I created an images table to store the image's attributes similar to what is on page 317.  The transfer takes place to the temporary server folder and my code is:

$image = $_FILES['picture'];

$image_filename = $image['name'];

$image_data = file_get_contents($image['tmp_name']);

The MySQL query (other fields not included here) uses:

$sql = "INSERT INTO images (filename, image_data) VALUES ('{$image_filename}', '{$image_data}')";

The error says "syntax error on line 1."  The binary value of the 80X60 image is there in the input when the error data is displayed, and if removed from the sql, the error goes away and the other fields are inserted properly (demonstrating that the overall syntax is okay).  It's like it is a corrupted image, but it's a Photoshop jpg.  I also tried sql strings with concatenations, plus the parameterized prepare method.  It's as if the MySQL processor at my Web host isn't set up for this approach and has no further explanation.  The Web host surely has up-to-date MySQL and their ini settings permit uploads.  When I ask questions like this I'm lucky if they even understand what I'm asking, and will usually say "we don't debug people's code."  Your book doesn't cover storing the raw-data part but it would be the next step if you had enough pages.  Thanks for the help.

[bnorcom]

I found an answer on the Internet.  It said if any of the data contains a quote character it will terminate the input and the remainder of the data will be used as sql instructions.  So I implemented mysqli_real_escape_string on the blob and it took out that corruption and stored it properly.  I was just assuming that a jpeg was automatically okay.  But I suppose that even file_get_contents can insert extraneous stuff. 

[Larry]

Thanks for sharing your answer. file_get_contents() returns a string, and like any string going into a database, you'll need to make sure it's safe to use in a query. However, it'd arguably be better if you stored the file--which is binary data--in a binary format. This means using a binary file reading function and storing it in a binary database column. 

 

However, it'd arguably be better not to store the file in the database. A database is fundamentally a collection of files on the server already. By reading in a file and storing it in the database, you're just adding a couple of layers of complexity for a similar result. Unless you need that file itself to benefit from being in a database, I would generally avoid doing so. The most common reason to store files in the database that I know of is if you need a replication benefit (i.e., to have the file stored in copied databases across multiple servers).