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 Developer Edit
 [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

Pull Requests

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] [email protected] 
In fact actually escapeString() method is not binary-safe.
 [2012-06-24 21:05 UTC] [email protected]
-Status: Open +Status: Verified
 [2012-06-27 13:42 UTC] [email protected] 
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] [email protected]
-Status: Verified +Status: Open
 [2012-06-27 13:57 UTC] [email protected] 
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] [email protected] 
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] [email protected] 
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] [email protected]
-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] [email protected]
-Operating System: windows xp +Operating System: *
 [2013-10-26 01:58 UTC] [email protected] 
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] [email protected]
-Summary: SQLite3::escapeString +Summary: SQLite3::escapeString is not binary safe -Assigned To: +Assigned To: cmb
 [2016-06-27 14:05 UTC] [email protected] 
> 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] [email protected]
-Type: Bug +Type: Documentation Problem
 [2016-06-27 14:12 UTC] [email protected] 
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] [email protected]
-Status: Analyzed +Status: Closed
 [2016-06-27 14:13 UTC] [email protected] 
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] [email protected] 
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-2025 The PHP Group
All rights reserved. 		Last updated: Sun Jul 20 12:00:03 2025 UTC