Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Aug 2010
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Strange PDO quote behavior (sqlite, deflate)

    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.
    Last edited by Daniel Evans; 10-15-2012 at 11:50 PM. Reason: found a solution

  • #2
    New to the CF scene
    Join Date
    Aug 2010
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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$contentPDO::PARAM_LOB);
    $query->execute(); 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •