3

According to this thread I should use fopen. I have done this but in my example I'm using bindValue instead of bindParam.

$query->bindValue(':'.$key, fopen($value, "rb"), PDO::PARAM_LOB);

$value contains the path to the file (/var/www/html/....jpg).

I tried this but when I open SQLiteManager with the specific entry I get [Exception ... "Component returened failure code 0x8052000b (NS_ERROR_FILE_CORRUPTED).

Why doesn't this work?

This is how the rest of my implementation looks like.

Edit:

file_get_contents brings me the same error.

Perhaps it is a problem with the php modules?

SELECT sqlite_version() brings me 3.7.7.1

phpinfo() gives me

Configure Command: '--without-sqlite'

additional .ini files parsed: /etc/php.d/pdo_sqlite.ini, /etc/php.d/sqlite.ini

SQLite:

PECL Module version 2.0-dev $Id: sqlite.c,v 1.166.2.13.2.9 2007/05/19 17:58:22 iliaa Exp $

SQLite Library 2.8.17

SQLite Encoding UTF-8

PDO drivers mysql, odbc, sqlite, sqlite2

pdo_sqlite

PECL Module version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 2007/03/23 14:30:00 wez Exp $

SQLite Library 3.3.6

PDO:

PDO drivers mysql, odbc, sqlite, sqlite2

Edit 2:

This is how I can display the BLOB images. getimage.php

header("Content-type: image/jpg");

require_once('class.DatabaseQuery.php');

$db = new DatabaseQuery();
$name = sqlite_escape_string($_GET['name']);
$sql = "SELECT image FROM $name WHERE _id=:id;";

$id = $_GET['id'];
$result = $db->ExecuteQuery($sql, array("id"=>$id));

echo $result[0]['image'];

Display images: <img src='getimage.php?name=$printname&id=" . $row['_id'] . "' heigth='75' width='75' alt='Test' />

So I can display the images. But how can I insert an image in the database?

Community
  • 1
  • 1
testing
  • 19,681
  • 50
  • 236
  • 417
  • 1
    You don't know why I'm needing this. I want to fill a SQLite database with content (in this case also images). The database is for a mobile client which only downloads one file (the database). This is my handicap and I have to do this. – testing Jan 31 '12 at 15:24
  • If you just want to download a single file, why not make it a zip archive containing the db and an image directory? – Rob Agar Jan 31 '12 at 15:54
  • 1
    Different clients can have different versions, so I would need to create different versions of image directories too. Additionally, the implementation of different clients has to be changed too. So currently I want to keep the solution of storing images in the database. It is possible because SQLiteManager can store an image as BLOB. Why this shouldn't be possible by a PHP script? – testing Jan 31 '12 at 16:04
  • You may be getting this error if the database was created with sqlite2 and you're opening with sqlite3. – Alex Jasmin Jan 31 '12 at 17:38
  • @Col. Shrapnel Regarding the linked post. I'm not sure about its ridiculousness. I explained what the OP was doing wrong and gave a bit of code to correctly insert a blob. You can make an argument against keeping images in the db but how does that make my post ridicule? – Alex Jasmin Jan 31 '12 at 17:42
  • @testing Are you using both the pdo_sqlite (SQLite 3) and sqlite (2) extensions on the same database file. You may want to use `new PDO("sqlite2:filename") when using PDO if you do so. – Alex Jasmin Jan 31 '12 at 18:08
  • @AlexandreJasmin: How do I find out with wich version the database was created? `SELECT sqlite_version()` brings me 3.7.7.1. I only use this call for openening the connection: `$this->_handle = new PDO("sqlite:"."database.db");`. This works for adding entries except of BLOB images. If I try your code I get `Call to a member function prepare() on a non-object` on `prepare`, `setAttribute` ... – testing Feb 01 '12 at 08:21
  • @AlexandreJasmin well it seems I was wrong. I had no idea that PDO can bind a file pointer. my apologies. – Your Common Sense Feb 01 '12 at 08:27
  • 1
    Warning, you are vurnerable to SQL injection. `getimage.php?name=1; DROP TABLE xxx;--` – Benoit Feb 01 '12 at 16:54
  • @Benoit: Thanks for your warning! But currently I only solved it partially. How can I prepare a statement in the `FROM` part of the `SELECT`? I tried this and get the error `General error: 1 near ":name": syntax error` – testing Feb 01 '12 at 17:14
  • @testing i don't think you can bind anywhere else than in the WHERE clause. just use an SQL escaper. also, seems like [ruby supports sqlite3/blobs](http://sqlite-ruby.rubyforge.org/sqlite3/faq.html#538670656). i don't think the php api supports it. – Janus Troelsen Feb 01 '12 at 18:21
  • @user309483: How does such a SQL escaper looks like? A simple `str_replace` of single and double quotes? – testing Feb 01 '12 at 23:18
  • 1
    @testing: http://php.net/manual/en/function.sqlite-escape-string.php – Janus Troelsen Feb 01 '12 at 23:26

1 Answers1

0

Probably you will avoid a lot of headaches if you encode your file and save it in a text field.

$dataToSave = base64_encode(file_get_contents('filename'));

To read you will need a base64_decode.

dvicino
  • 1,469
  • 1
  • 11
  • 19
  • I also tried to get the image into the database by using `base64_encode` and `bin2hex`. The images in the database are somehow hex decoded and saved as `BLOB`. I would have to ask the developers of the clients how they deal with the images now and if they could switch to `base64_encode`. In the meantime I managed to display the images from the database. So I can display BLOB images, I can even input BLOB images with SQLiteManager. It shouldn't be that hard to insert an image with PHP. But if I don't get this managed your answer would be the only solution I think. – testing Feb 01 '12 at 16:45