Resolved Strange PDO quote behavior (sqlite, deflate)

[Daniel Evans]

Hello,

I'm using PDO to access a sqlite 3 database. Because space is getting very limited and the database is still growing, I've thought about using compression to allow for more data. This would work for me for two reasons: first, I only have to write and read the HTML from the db and not search it, which can just as easily be done compressed, and secondly, the HTML chunks (mostly 30-100k) can be reduced in size by more than half without creating too high a server load.

Now I've compressed a ~23kb string with gzdeflate($content,6) to less than 10kb, which is fine. But as soon as I use quote() in preparation of inserting the compressed data into the db, I experience strange behavior. Suddenly, the string is cut off and instead of 10kb, it can be anywhere from 10 bytes to 5kb.

PHP Code:

$content = file_get_contents("sample.html"); // 23430 chars
$db = new PDO("sqlite:data.sqlite");
$content2 = $db->quote($content); // 23470 chars
$content3 = gzdeflate($content,6); // 9731 chars
$content4 = $db->quote($content3); // 859 chars
$content5 = mysql_escape_string($content3); // 9989 chars 


This is my simplified sample code to illustrate the problem. You can see the string length of the variables $content to $content5 after each action. It appears to me that PDO::quote, in contrast to the mysql escape string function, does not work on binary data. I've tried different html files, I've tried different gzdeflate compression ratios and it's always the same. Depending on the HTMl file used and the compression ratio, the length of the quoted string varies, but it is always cut off!

I have yet to try this on the web-server, so it is a problem on my Windows home PC, which is running an unmodified xampp (php 5.4.7 with pdo_sqlite.dll 5.4.7 and sqlite library 3.7.7.1).

Does anyone have a solution for me or is that generally not possible? Should I use the deprecated mysql_escape_string(), which I must, because I can't use mysql_real_escape_string() without a mysql db connection.

I am aware that I could write the compressed HTML in separate files and only save the file name/path in the db. Maybe I'll try that if nothing else works, but I'd really prefer to avoid the creation of thousands of files and keep it all in one database.

[Daniel Evans]

Okay, looks like I was a bit stupid ;) The following code accepts binary content thanks to the Large Objects parameter:

PHP Code:

$query = $db->prepare("INSERT INTO ....");
$query->bindValue(1, $content, PDO::PARAM_LOB);
$query->execute();