php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Doc Bug #62361 SQLite3::escapeString is not binary safe
Submitted: 2012-06-19 05:28 UTC Modified: 2016-06-27 14:13 UTC
Votes:7
Avg. Score:3.6 ± 0.7
Reproduced:3 of 3 (100.0%)
Same Version:1 (33.3%)
Same OS:1 (33.3%)
From: lgynove at 163 dot com Assigned: cmb (profile)
Status: Closed Package: SQLite related
PHP Version: 5.3.14 OS: *
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: lgynove at 163 dot com
New email:
PHP Version: OS:

 

 [2012-06-19 05:28 UTC] lgynove at 163 dot com 
Description:
------------
this bug in sqlite3,not sqlite.
use picture file (*.jpg) is not work
use *.txt is work ok

if use sqlite function (sqlite_escape_string) is work ok!


Test script:
---------------
$db = new sqlite3(dirname(__FILE__) . '/test.sqlite');

$str = file_get_contents('d:/www/test.jpg');
echo strlen($str),"\n";
$str = $db->escapeString($str);
echo strlen($str),"\n";

$str = file_get_contents('d:/www/test.txt');
echo strlen($str),"\n";
$str = $db->escapeString($str);
echo strlen($str),"\n";

Expected result:
----------------
5000
5000
35
35

Actual result:
--------------
5000
4
35
35

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-06-23 07:33 UTC] lgynove at 163 dot com
-Package: SQLite +Package: SQLite related
 [2012-06-24 21:05 UTC] felipe@php.net 
In fact actually escapeString() method is not binary-safe.
 [2012-06-24 21:05 UTC] felipe@php.net
-Status: Open +Status: Verified
 [2012-06-27 13:42 UTC] ab@php.net 
That's not a php bug i'd say. We rely here on the functionality of http://www.sqlite.org/c3ref/mprintf.html using %q format option. And what their manual says

---
The %q option works like %s in that it substitutes a nul-terminated string from the argument list. But %q also doubles every '\'' character. %q is designed for use inside a string literal. By doubling each '\'' character it escapes that character and allows it to be inserted into the string.
---

Escaping '\'' and '\0' can be of course easily implemented. But, as sqlite3 itself has no other string formatting options, I'd really doubt the usefulness and correctness of such an implementation. Furthermore, if such a data would be selected back from the db, some code would be needed to restore all that escaped '\0' bytes and whatever else.

It might make sense to use base64 or alike to insert binary data into sqlite3 i think.
 [2012-06-27 13:57 UTC] felipe@php.net
-Status: Verified +Status: Open
 [2012-06-27 13:57 UTC] felipe@php.net 
But we have implemented an auxiliar escaping routine to escape the binary ones, as pointed out by the reporter.
 [2012-06-27 14:41 UTC] ab@php.net 
Ah, now I see what you mean. php_sqlite_encode_binary in the PECL code, strange it wasn't moved into sqlite3.
 [2012-06-27 16:44 UTC] ab@php.net 
Ok, after digging into the subject i've found sqlite3_bind_blob() here http://www.sqlite.org/c3ref/bind_blob.html . This functionality fully replaces sqlite2's sqlite_encode_binary() in sqlite3. As I can see, it's also implemented and available in PHP http://de2.php.net/manual/de/sqlite3stmt.bindparam.php . 

It looks pretty much like if we want to have the old behaviour, we should take encode.c from PECL. A sticky point here - I'm not sure that the encoding algorithms are equivalent in both 2 and 3. So we would need also something like ->unescapeString() to get the data back. That could be useful in some cases but anyway redundant in sqlite3.

What do you think?
 [2012-06-29 08:35 UTC] ab@php.net
-Status: Open +Status: Analyzed
 [2012-11-02 11:26 UTC] daniel dot kinzler at wikimedia dot de 
The same problem exists with the SQLite driver for PDO, see bug 63419
 [2013-10-26 01:58 UTC] yohgaki@php.net
-Operating System: windows xp +Operating System: *
 [2013-10-26 01:58 UTC] yohgaki@php.net 
I've made bug 63419 'feedback'.

Even if we made "escape"/"quote" binary safe, it may not work as expected. I think SQLite3 users should use bind blob.

Is anyone verified manual escaping/quoting works for blob?
 [2016-06-27 14:05 UTC] cmb@php.net
-Summary: SQLite3::escapeString +Summary: SQLite3::escapeString is not binary safe -Assigned To: +Assigned To: cmb
 [2016-06-27 14:05 UTC] cmb@php.net 
> Even if we made "escape"/"quote" binary safe, it may not work as
> expected. I think SQLite3 users should use bind blob.

ACK

> Is anyone verified manual escaping/quoting works for blob?

Do mean escaping by SQLite3::escapeString()? This is not binary
safe[1], what has to be documented, so I'm changing to doc bug.

[1] <https://3v4l.org/hPH7B>
 [2016-06-27 14:09 UTC] cmb@php.net
-Type: Bug +Type: Documentation Problem
 [2016-06-27 14:12 UTC] cmb@php.net 
Automatic comment from SVN on behalf of cmb
Revision: http://svn.php.net/viewvc/?view=revision&amp;revision=339508
Log: Fix #62361: SQLite3::escapeString is not binary safe
 [2016-06-27 14:13 UTC] cmb@php.net
-Status: Analyzed +Status: Closed
 [2016-06-27 14:13 UTC] cmb@php.net 
This bug has been fixed in the documentation's XML sources. Since the
online and downloadable versions of the documentation need some time
to get updated, we would like to ask you to be a bit patient.

Thank you for the report, and for helping us make our documentation better.
 [2020-02-07 06:07 UTC] phpdocbot@php.net 
Automatic comment on behalf of cmb
Revision: http://git.php.net/?p=doc/en.git;a=commit;h=7b918120f83a3ce4ef6a6d48a880f0e07b343964
Log: Fix #62361: SQLite3::escapeString is not binary safe
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Tue Apr 23 17:01:31 2024 UTC