2

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?

Improve this question
5
  • Don't use blobs, instead store images in a folder and save filename.extension in SQL, so it wont increase your DB size Commented Mar 31, 2013 at 7:47
  • what char set do you get? Commented Mar 31, 2013 at 7:51
  • 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{ï¿ Commented Mar 31, 2013 at 7:57
  • please post your versions of php, cake and sqlite. Commented Mar 31, 2013 at 8:06
  • I don't think mysql_real_escape_string() is required here? Commented Mar 31, 2013 at 8:17

2 Answers 2

Reset to default
8

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

People, keep in mind $db->escapeString is not Binary Safe as I thought it was, so thank you for this solution!
2

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

  1. You mention CakePHP, but there's no CakePHP involved in your question
  2. 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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.