PHP: Inserting Blob Image to SQLite table

Question

i'm trying to insert an image using (cakephp) to sqlite DB in a BLOB column.

i'm using this code

$insert = "INSERT INTO story (page, image) 
                    VALUES (:page, :image)";
        $stmt = $file_db->prepare($insert);

        // Bind parameters to statement variables
        $img = file_get_contents($this->request->data['image']['tmp_name']);
        $img = mysql_real_escape_string($img);
        $stmt->bindParam(':page', $this->request->data['page']);
        $stmt->bindParam(':image', $img);

           // Execute statement
          $stmt->execute();

and it's inserting but a characters set not a blob data as it suppose to be.. is there any other way to do it or else what is the wrong with my code?

Don't use blobs, instead store images in a folder and save `filename.extension` in SQL, so it wont increase your DB size — Mr. Alien, Mar 31 '13 at 07:47
i have to save it on DB :) cause this DB should be transferred to Android later on :) and the charset is something like that PNG\r\n\Z\n\0\0\0\rIHDR\0\0,\0\0ï¿½\0\0\0\Z/ï¿½ï¿½\0\0\0 pHYs\0\0\0\0\0ï¿½ï¿½\0\0\0 cHRM\0\0z%\0\0ï¿½ï¿½\0\0ï¿½ï¿½\0\0ï¿½ï¿½\0\0u0\0\0ï¿½`\0\0:ï¿½\0\0oï¿½_ï¿½F\0ï¿½ï¿½IDATxÚï¿½ï¿½ï¿½ï¿½u&\nï¿½}ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½Ld&ï¿½ï¿½0%\0\0ï¿½ï¿½8I\"EÊL[ï¿½eï¿½eWï¿½ï¿½ï¿½Uvï¿½ï¿½ï¿½ï¿½jï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½z]ï¿½ï¿½ï¿½ï¿½êª®zUï¿½Tï¿½mï¿½$Kï¿½HMï¿½(ï¿½ï¿½L\0ï¿½\0cNï¿½yï¿½ï¿½Fï¿½9gï¿½;ï¿½ï¿½sï¿½^ï¿½_.JHï¿½!ï¿½ï¿½9{ï¿ — Mohamed Emad Hegab, Mar 31 '13 at 07:57
I don't think `mysql_real_escape_string()` is required here? — thaJeztah, Mar 31 '13 at 08:17

score 7 · Accepted Answer · edited Dec 28 '22 at 15:45

This is an answer to the question in the title, as it ranks #1 for [php sqlite blob]. This doesn't use PDO as I had trouble with it. You have to use prepared statement, you usually can't execute a literal SQL insert as the size is too big with a blob. This is an update rather than an insert but it's basically the same.

// example variables
$row_id=1;
$image_filename="/home/mywebsite/public_html/images/an_image.png";
$sqlite_table_name="user_images";
$database_filename="database.sqlite";
// the blob field in the sqlite table is called ZIMAGE and the id field is ZID.

// Code
if (file_exists($image_filename)) {
    $db = new SQLite3($database_filename);
    $query = $db->prepare("UPDATE sqlite_table_name SET ZIMAGE=? WHERE ZID=?");
    $image=file_get_contents($image_filename);
    $query->bindValue(1, $image, SQLITE3_BLOB);
    $query->bindValue(2, $row_id, SQLITE3_TEXT);
    $run=$query->execute();
}

I don't think there's any hard rules in programming, I store images in blobs in Sqlite in certain situations. I also scrape webpages with regular expressions instead of Xpath! Whatever gets the job done.

People, keep in mind $db->escapeString is not Binary Safe as I thought it was, so thank you for this solution! — Highstrike, May 15 '15 at 10:10

score 2 · Answer 2 · edited May 23 '17 at 12:31

This is not an answer, but there's 'unclarity' in this question, too long to put it as a comment

You mention CakePHP, but there's no CakePHP involved in your question
You mention 'SQLite', but you're using mysql_real_escape?

Please make clear what database you intend to use.

Regarding MySQL and storing images inside the database;

mysql_ functions in PHP are deprecated and no longer maintained
try to determin if you really need to store your image data inside the database, generally, it's better to store the image itself in the file system and the name of the image in the database

Read this question for an answer on inserting images in mysql:

Insert Blobs in MySql databases with php

Read this question for an answer on inserting images in SQLite (Using PDO):

Remote image file to sqlite blob in PHP?

Here is a generic PDO step-by-step walk through for BLOBs;

http://www.phpeveryday.com/articles/PDO-Working-With-BLOBs-P554.html

PHP: Inserting Blob Image to SQLite table

2 Answers2