Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

It's not that it's impossible.
https://github.com/mackyle/sqlite/compare/master...d3x0r:AllowInlineNulChars

Improves handling in TCL, sqlite shell, and passing complete composed SQL
strings to prepare...

Which is why I know if you don't use the broken C-flavored(tainted) SQL
String functions, storing and retrieiving STRINGS with NUL characters is
easily handled which is why I started with my first response of 'what
tool are you actually using?'

In the storage, both the length and data are stored. (have been since 3.0
or before)

On Thu, Apr 19, 2018 at 4:11 PM, J Decker  wrote:

>
>
> On Thu, Apr 19, 2018 at 4:07 PM, Keith Medcalf 
> wrote:
>
>>
>> And what makes you think a "javascript string" is a "C string"?  While
>> the "string" part may be the same, "javascript" certainly does not equal
>> "C".  Just like you do not have issues with embedded zero-bytes in "pascal
>> strings".  Note that "pascal" != "C" even though "string" == "string".
>>
>>
> by the same reasoning that you apply saying SQL strings are C strings.
>
>
>> Note that the sqlite3_value_text returns the data (including embedded
>> zero bytes), but not the length.
>
>
> sqlite3_value_bytes() returns the number of bytes.
>
> 
>
>> If you pass the data returned thereby to a function expecting a C string
>> (zero terminated), it will terminate at the first zero byte encountered.
>> If you retrieve the length and the data separately and construct
>> pascal-style strings and pass them to functions expecting "pascal" style
>> strings, then the embedded zero is just "string data" (NB:  pascal is used
>> only as an example -- many X strings contain an embedded length for any
>> given value of X -- C strings do not).
>>
>> 
>
> Obviously "javascript" strings contain a length indicator and are not
>> zero-terminated.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>>
>> >-Original Message-
>> >From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
>> >Sent: Thursday, 19 April, 2018 16:41
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] SQLite3 - Search on text field with \0 binary
>> >data
>> >
>> >On Thu, Apr 19, 2018 at 3:37 PM, J Decker  wrote:
>> >
>> >>
>> >>
>> >> On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf
>> >
>> >> wrote:
>> >>
>> >>>
>> >>> Actually, nothing in the C or C++ world will "go past" the NULL
>> >byte
>> >>> since the very definition of a C string is a "bunch-o-bytes that
>> >are
>> >>> non-zero followed by one that is".
>> >>>
>> >>> And sory for the double response; but if C/C++ couldn't handle a
>> >NUL
>> >character (the character is 1 L) then spidermonkey/chakra/V8 would
>> >have
>> >problems with NUL characters in javascript strings.  But it doesn't.
>> >Why
>> >is that?
>> >
>> >
>> >> that doesnt' mean you can use a custom token structure that
>> >contains both
>> >> the pointer and length of the data. (which it already has)
>> >> sure, using standard C api - strlen, etc sure... but sqlite uses a
>> >custom
>> >> function internally sqlite3stlren30  which can easily be extended
>> >to take
>> >> the length of the string; but wait, if it was saved, it wouldn't
>> >need to be
>> >> called, and a overall performance gain is created.
>> >>
>> >> the biggest problem is really the internal function
>> >'(something)printf'
>> >> which returns a char *, and has no space to return the length, like
>> >> snprintf would.
>> >>
>> >> and I can easily put nuls into a string
>> >>
>> >> char buf[256];
>> >> int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0
>> >);
>> >> and the length returned would be 27.
>> >>
>> >>
>> >>> If you want to embed non UTF8 text you should be using a BLOB not
>> >TEXT.
>> >>> Text means "an array of non-zero characters terminated by a zero
>> >byte" and
>> >>> a BLOB means a "bag-o-bytes" of a specific size.
>> >>>
>> >>> Blob means binary; havihng to deal with a binary structure to
>> >convert to
>> >> a string and back is ridiculous when the interface already supports
>> >storing
>> >> and getting strings with \0 in them.
>> >>
>> >>
>> >>> Things meants to work on C "strings" should always stop at the
>> >zero
>> >>> terminator.  Failure to do so can lead to AHBL.
>> >>>
>> >>>
>> >> So don't use the standard library.  That was one of the first
>> >htings I
>> >> created for my MUD client; a smart text string class.  (I say class
>> >in the
>> >> generic term, not the literal, since it was written in C)
>> >>
>> >>
>> >>> (Note, this applies to "wide" (as in word) and "fat" (as in double
>> >word)
>> >>> and obese (as in quad word) strings as well.  They are a sequence
>> >of
>> >>> words/double-words/quad-words/ten-words (whatever) that are non-
>> >zero
>> >>> followed by one that is zero -- and the narrow/wide/fat/obese
>> >string ends
>> >>> at the 

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 4:07 PM, Keith Medcalf  wrote:

>
> And what makes you think a "javascript string" is a "C string"?  While the
> "string" part may be the same, "javascript" certainly does not equal "C".
> Just like you do not have issues with embedded zero-bytes in "pascal
> strings".  Note that "pascal" != "C" even though "string" == "string".
>
>
by the same reasoning that you apply saying SQL strings are C strings.


> Note that the sqlite3_value_text returns the data (including embedded zero
> bytes), but not the length.


sqlite3_value_bytes() returns the number of bytes.



> If you pass the data returned thereby to a function expecting a C string
> (zero terminated), it will terminate at the first zero byte encountered.
> If you retrieve the length and the data separately and construct
> pascal-style strings and pass them to functions expecting "pascal" style
> strings, then the embedded zero is just "string data" (NB:  pascal is used
> only as an example -- many X strings contain an embedded length for any
> given value of X -- C strings do not).
>
> 

Obviously "javascript" strings contain a length indicator and are not
> zero-terminated.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> >Sent: Thursday, 19 April, 2018 16:41
> >To: SQLite mailing list
> >Subject: Re: [sqlite] SQLite3 - Search on text field with \0 binary
> >data
> >
> >On Thu, Apr 19, 2018 at 3:37 PM, J Decker  wrote:
> >
> >>
> >>
> >> On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf
> >
> >> wrote:
> >>
> >>>
> >>> Actually, nothing in the C or C++ world will "go past" the NULL
> >byte
> >>> since the very definition of a C string is a "bunch-o-bytes that
> >are
> >>> non-zero followed by one that is".
> >>>
> >>> And sory for the double response; but if C/C++ couldn't handle a
> >NUL
> >character (the character is 1 L) then spidermonkey/chakra/V8 would
> >have
> >problems with NUL characters in javascript strings.  But it doesn't.
> >Why
> >is that?
> >
> >
> >> that doesnt' mean you can use a custom token structure that
> >contains both
> >> the pointer and length of the data. (which it already has)
> >> sure, using standard C api - strlen, etc sure... but sqlite uses a
> >custom
> >> function internally sqlite3stlren30  which can easily be extended
> >to take
> >> the length of the string; but wait, if it was saved, it wouldn't
> >need to be
> >> called, and a overall performance gain is created.
> >>
> >> the biggest problem is really the internal function
> >'(something)printf'
> >> which returns a char *, and has no space to return the length, like
> >> snprintf would.
> >>
> >> and I can easily put nuls into a string
> >>
> >> char buf[256];
> >> int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0
> >);
> >> and the length returned would be 27.
> >>
> >>
> >>> If you want to embed non UTF8 text you should be using a BLOB not
> >TEXT.
> >>> Text means "an array of non-zero characters terminated by a zero
> >byte" and
> >>> a BLOB means a "bag-o-bytes" of a specific size.
> >>>
> >>> Blob means binary; havihng to deal with a binary structure to
> >convert to
> >> a string and back is ridiculous when the interface already supports
> >storing
> >> and getting strings with \0 in them.
> >>
> >>
> >>> Things meants to work on C "strings" should always stop at the
> >zero
> >>> terminator.  Failure to do so can lead to AHBL.
> >>>
> >>>
> >> So don't use the standard library.  That was one of the first
> >htings I
> >> created for my MUD client; a smart text string class.  (I say class
> >in the
> >> generic term, not the literal, since it was written in C)
> >>
> >>
> >>> (Note, this applies to "wide" (as in word) and "fat" (as in double
> >word)
> >>> and obese (as in quad word) strings as well.  They are a sequence
> >of
> >>> words/double-words/quad-words/ten-words (whatever) that are non-
> >zero
> >>> followed by one that is zero -- and the narrow/wide/fat/obese
> >string ends
> >>> at the zeo value).
> >>>
> >>>
> >> utf8everywhere.org
> >> No reason to use wide char.
> >>
> >>
> >> get good, son. (sorry if that's overly offensive)
> >>
> >> ---
> >>> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven
> >>> says a lot about anticipated traffic volume.
> >>>
> >>
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users 

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[Keith Medcalf]

And what makes you think a "javascript string" is a "C string"?  While the 
"string" part may be the same, "javascript" certainly does not equal "C".  Just 
like you do not have issues with embedded zero-bytes in "pascal strings".  Note 
that "pascal" != "C" even though "string" == "string".

Note that the sqlite3_value_text returns the data (including embedded zero 
bytes), but not the length.  If you pass the data returned thereby to a 
function expecting a C string (zero terminated), it will terminate at the first 
zero byte encountered.  If you retrieve the length and the data separately and 
construct pascal-style strings and pass them to functions expecting "pascal" 
style strings, then the embedded zero is just "string data" (NB:  pascal is 
used only as an example -- many X strings contain an embedded length for any 
given value of X -- C strings do not).

Obviously "javascript" strings contain a length indicator and are not 
zero-terminated.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of J Decker
>Sent: Thursday, 19 April, 2018 16:41
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLite3 - Search on text field with \0 binary
>data
>
>On Thu, Apr 19, 2018 at 3:37 PM, J Decker  wrote:
>
>>
>>
>> On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf
>
>> wrote:
>>
>>>
>>> Actually, nothing in the C or C++ world will "go past" the NULL
>byte
>>> since the very definition of a C string is a "bunch-o-bytes that
>are
>>> non-zero followed by one that is".
>>>
>>> And sory for the double response; but if C/C++ couldn't handle a
>NUL
>character (the character is 1 L) then spidermonkey/chakra/V8 would
>have
>problems with NUL characters in javascript strings.  But it doesn't.
>Why
>is that?
>
>
>> that doesnt' mean you can use a custom token structure that
>contains both
>> the pointer and length of the data. (which it already has)
>> sure, using standard C api - strlen, etc sure... but sqlite uses a
>custom
>> function internally sqlite3stlren30  which can easily be extended
>to take
>> the length of the string; but wait, if it was saved, it wouldn't
>need to be
>> called, and a overall performance gain is created.
>>
>> the biggest problem is really the internal function
>'(something)printf'
>> which returns a char *, and has no space to return the length, like
>> snprintf would.
>>
>> and I can easily put nuls into a string
>>
>> char buf[256];
>> int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0
>);
>> and the length returned would be 27.
>>
>>
>>> If you want to embed non UTF8 text you should be using a BLOB not
>TEXT.
>>> Text means "an array of non-zero characters terminated by a zero
>byte" and
>>> a BLOB means a "bag-o-bytes" of a specific size.
>>>
>>> Blob means binary; havihng to deal with a binary structure to
>convert to
>> a string and back is ridiculous when the interface already supports
>storing
>> and getting strings with \0 in them.
>>
>>
>>> Things meants to work on C "strings" should always stop at the
>zero
>>> terminator.  Failure to do so can lead to AHBL.
>>>
>>>
>> So don't use the standard library.  That was one of the first
>htings I
>> created for my MUD client; a smart text string class.  (I say class
>in the
>> generic term, not the literal, since it was written in C)
>>
>>
>>> (Note, this applies to "wide" (as in word) and "fat" (as in double
>word)
>>> and obese (as in quad word) strings as well.  They are a sequence
>of
>>> words/double-words/quad-words/ten-words (whatever) that are non-
>zero
>>> followed by one that is zero -- and the narrow/wide/fat/obese
>string ends
>>> at the zeo value).
>>>
>>>
>> utf8everywhere.org
>> No reason to use wide char.
>>
>>
>> get good, son. (sorry if that's overly offensive)
>>
>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven
>>> says a lot about anticipated traffic volume.
>>>
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 3:37 PM, J Decker  wrote:

>
>
> On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf 
> wrote:
>
>>
>> Actually, nothing in the C or C++ world will "go past" the NULL byte
>> since the very definition of a C string is a "bunch-o-bytes that are
>> non-zero followed by one that is".
>>
>> And sory for the double response; but if C/C++ couldn't handle a NUL
character (the character is 1 L) then spidermonkey/chakra/V8 would have
problems with NUL characters in javascript strings.  But it doesn't.  Why
is that?


> that doesnt' mean you can use a custom token structure that contains both
> the pointer and length of the data. (which it already has)
> sure, using standard C api - strlen, etc sure... but sqlite uses a custom
> function internally sqlite3stlren30  which can easily be extended to take
> the length of the string; but wait, if it was saved, it wouldn't need to be
> called, and a overall performance gain is created.
>
> the biggest problem is really the internal function '(something)printf'
> which returns a char *, and has no space to return the length, like
> snprintf would.
>
> and I can easily put nuls into a string
>
> char buf[256];
> int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0 );
> and the length returned would be 27.
>
>
>> If you want to embed non UTF8 text you should be using a BLOB not TEXT.
>> Text means "an array of non-zero characters terminated by a zero byte" and
>> a BLOB means a "bag-o-bytes" of a specific size.
>>
>> Blob means binary; havihng to deal with a binary structure to convert to
> a string and back is ridiculous when the interface already supports storing
> and getting strings with \0 in them.
>
>
>> Things meants to work on C "strings" should always stop at the zero
>> terminator.  Failure to do so can lead to AHBL.
>>
>>
> So don't use the standard library.  That was one of the first htings I
> created for my MUD client; a smart text string class.  (I say class in the
> generic term, not the literal, since it was written in C)
>
>
>> (Note, this applies to "wide" (as in word) and "fat" (as in double word)
>> and obese (as in quad word) strings as well.  They are a sequence of
>> words/double-words/quad-words/ten-words (whatever) that are non-zero
>> followed by one that is zero -- and the narrow/wide/fat/obese string ends
>> at the zeo value).
>>
>>
> utf8everywhere.org
> No reason to use wide char.
>
>
> get good, son. (sorry if that's overly offensive)
>
> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf  wrote:

>
> Actually, nothing in the C or C++ world will "go past" the NULL byte since
> the very definition of a C string is a "bunch-o-bytes that are non-zero
> followed by one that is".
>
> that doesnt' mean you can use a custom token structure that contains both
the pointer and length of the data. (which it already has)
sure, using standard C api - strlen, etc sure... but sqlite uses a custom
function internally sqlite3stlren30  which can easily be extended to take
the length of the string; but wait, if it was saved, it wouldn't need to be
called, and a overall performance gain is created.

the biggest problem is really the internal function '(something)printf'
which returns a char *, and has no space to return the length, like
snprintf would.

and I can easily put nuls into a string

char buf[256];
int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0 );
and the length returned would be 27.


> If you want to embed non UTF8 text you should be using a BLOB not TEXT.
> Text means "an array of non-zero characters terminated by a zero byte" and
> a BLOB means a "bag-o-bytes" of a specific size.
>
> Blob means binary; havihng to deal with a binary structure to convert to a
string and back is ridiculous when the interface already supports storing
and getting strings with \0 in them.


> Things meants to work on C "strings" should always stop at the zero
> terminator.  Failure to do so can lead to AHBL.
>
>
So don't use the standard library.  That was one of the first htings I
created for my MUD client; a smart text string class.  (I say class in the
generic term, not the literal, since it was written in C)


> (Note, this applies to "wide" (as in word) and "fat" (as in double word)
> and obese (as in quad word) strings as well.  They are a sequence of
> words/double-words/quad-words/ten-words (whatever) that are non-zero
> followed by one that is zero -- and the narrow/wide/fat/obese string ends
> at the zeo value).
>
>
utf8everywhere.org
No reason to use wide char.


get good, son. (sorry if that's overly offensive)

---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[Keith Medcalf]

Actually, nothing in the C or C++ world will "go past" the NULL byte since the 
very definition of a C string is a "bunch-o-bytes that are non-zero followed by 
one that is".

If you want to embed non UTF8 text you should be using a BLOB not TEXT.  Text 
means "an array of non-zero characters terminated by a zero byte" and a BLOB 
means a "bag-o-bytes" of a specific size.

Things meants to work on C "strings" should always stop at the zero terminator. 
 Failure to do so can lead to AHBL.

(Note, this applies to "wide" (as in word) and "fat" (as in double word) and 
obese (as in quad word) strings as well.  They are a sequence of 
words/double-words/quad-words/ten-words (whatever) that are non-zero followed 
by one that is zero -- and the narrow/wide/fat/obese string ends at the zeo 
value).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of J Decker
>Sent: Thursday, 19 April, 2018 14:09
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLite3 - Search on text field with \0 binary
>data
>
>are you testing this in the sqlite3 shell or in your own program?
>You should have no issue getting thisdata,
>
>sqlite3_column_text followed by sqlite3_column_bytes  (the byte count
>is
>set when the data is fetched in some format).
>
>without modification, you can either excape \0 with'||char(0)||'
>...
>
>"My data \0with binary".
>"My data "||char(0)||" with binary".
>
>or use a parameter binding to get the value stored in the database.
>
>The row with a nul should be stored in the database; but the command
>line
>shell will not return the right values for selects; it will truncate
>that
>returned values at the \0.
>
>
>On Thu, Apr 19, 2018 at 5:27 AM, MARCHAND Loïc
>
>wrote:
>
>> I index a file in a SQLite DB.
>> I create my table with this:
>> CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY
>(RecordID))
>>
>> I read a file, and for each line I add a row on the table. Each
>line can
>> have binary data at end. It's not a problem for many chars, but \0
>char
>> make a problem.
>> If I have a line like this : "My data \0with binary".
>> When I try to get data after the \0 not worked (SELECT substr(Data,
>11, 5)
>> FROM Record return an empty string or SELECT substr(Data, 4, 10)
>FROM
>> Record return data)
>>
>> When I try to search a data (SELECT Data FROM Record WHERE Data
>LIKE
>> '%binar%') return 0 rows returned.
>>
>> How can I solve this problem ? I try to replace \0 by an other char
>> sequence, but it's not a good idea because can I have this sequence
>in my
>> file.
>> Thank you
>> Loïc
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 1:49 PM, David Raymond 
wrote:

> After a little testing, of the core functions:
>
> Affected by \x00:
> substr
> like
> length
> quote
> replace when you're trying to replace the \x00
>
> Not affected by \x00:
> plain ol select
> instr
> lower
> upper
> trim
> ltrim
> rtrim
> replace when you're not replacing the \x00
> (works and replaces bits after the \x00 as well)
> ||
>
> At the moment I can't find anything in the documentation that covers this.
>
>
length has a note about terminating at nul.

I do know it's not an issue with the SQL standard; only implementations of
the standard.

The SQL standard is quite clear...

As mentioned here...
http://sqlite.1065341.n5.nabble.com/sqlite-command-line-tool-fails-to-dump-data-tp100196p100218.html




http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

spaces are used to separate syntactic elements. Multiple spaces and
 line breaks are treated as a single space. Apart from those symbols

 to which special functions were given above,
*other characters and  character strings in a formula stand for
themselves. In *addition,
 if the symbols to the right of the definition operator in a produc-

 tion consist entirely of BNF symbols, then those symbols stand for
 themselves and do not take on their special meaning.


For every portion of the string enclosed in square brackets,
either delete the brackets and their contents or change the
brackets to braces.


(from SQL 92) By this, I shouldn't also be able to use ~, `, Γειά σου Κόσμ,
Привет мир, or any other UNICODE character. (that is if you say things not
listed are " cannot contain embedded ;"


There is no specification that \0 means anything (in SQL).


So instead of like maybe you could use instr() instead?
> And it looks like messing with cast might do it to make substr work
>
> select cast(substr(cast(Data as blob), 11, 5) as text) from Record;
> select Data from Record where instr(Data, 'binar');
>
> Seems a little odd, as even when I declare the Data field as blob to begin
> with substr still doesn't work, but calling on the cast value does.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of MARCHAND Loïc
> Sent: Thursday, April 19, 2018 8:27 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] SQLite3 - Search on text field with \0 binary data
>
> I index a file in a SQLite DB.
> I create my table with this:
> CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID))
>
> I read a file, and for each line I add a row on the table. Each line can
> have binary data at end. It's not a problem for many chars, but \0 char
> make a problem.
> If I have a line like this : "My data \0with binary".
> When I try to get data after the \0 not worked (SELECT substr(Data, 11, 5)
> FROM Record return an empty string or SELECT substr(Data, 4, 10) FROM
> Record return data)
>
> When I try to search a data (SELECT Data FROM Record WHERE Data LIKE
> '%binar%') return 0 rows returned.
>
> How can I solve this problem ? I try to replace \0 by an other char
> sequence, but it's not a good idea because can I have this sequence in my
> file.
> Thank you
> Loïc
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[David Raymond]

After a little testing, of the core functions:

Affected by \x00:
substr
like
length
quote
replace when you're trying to replace the \x00

Not affected by \x00:
plain ol select
instr
lower
upper
trim
ltrim
rtrim
replace when you're not replacing the \x00
(works and replaces bits after the \x00 as well)
||

At the moment I can't find anything in the documentation that covers this.

So instead of like maybe you could use instr() instead?
And it looks like messing with cast might do it to make substr work

select cast(substr(cast(Data as blob), 11, 5) as text) from Record;
select Data from Record where instr(Data, 'binar');

Seems a little odd, as even when I declare the Data field as blob to begin with 
substr still doesn't work, but calling on the cast value does.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MARCHAND Loïc
Sent: Thursday, April 19, 2018 8:27 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SQLite3 - Search on text field with \0 binary data

I index a file in a SQLite DB.
I create my table with this:
CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID))

I read a file, and for each line I add a row on the table. Each line can have 
binary data at end. It's not a problem for many chars, but \0 char make a 
problem.
If I have a line like this : "My data \0with binary".
When I try to get data after the \0 not worked (SELECT substr(Data, 11, 5) FROM 
Record return an empty string or SELECT substr(Data, 4, 10) FROM Record return 
data)

When I try to search a data (SELECT Data FROM Record WHERE Data LIKE '%binar%') 
return 0 rows returned.

How can I solve this problem ? I try to replace \0 by an other char sequence, 
but it's not a good idea because can I have this sequence in my file.
Thank you
Loïc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[Eduardo Morras]

On Thu, 19 Apr 2018 09:37:20 -0700
"dmp"  wrote:

> Currently I trying to complete a plugin for Ajqvue
> that transfers data from others databases to SQLite.

Hi, I made an odbc virtual table to achive this. It's closed source but
the ¿difficulty? to develop one from scratch is easy-medium.

Pass the connection string, user and password, and all queries to vtab
are redirected to the other dbms. I began with the csv file virtual
table and went from there.

Check SQL-MED standard and postgresql docs for syntax.


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[Simon Slavin]

On 19 Apr 2018, at 1:27pm, MARCHAND Loïc  wrote:

> CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID))
> 
> I read a file, and for each line I add a row on the table. Each line can have 
> binary data at end. It's not a problem for many chars, but \0 char make a 
> problem.
> If I have a line like this : "My data \0with binary".

Although this may not be causing your problem, you should declare "Data BLOB" 
not "Data TEXT".  Although SQLite itself doesn't care, some SQLite libraries 
may interpret TEXT as meaning that you want the data handled as C text, with \0 
meaning a terminator.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

are you testing this in the sqlite3 shell or in your own program?
You should have no issue getting thisdata,

sqlite3_column_text followed by sqlite3_column_bytes  (the byte count is
set when the data is fetched in some format).

without modification, you can either excape \0 with'||char(0)||'...

"My data \0with binary".
"My data "||char(0)||" with binary".

or use a parameter binding to get the value stored in the database.

The row with a nul should be stored in the database; but the command line
shell will not return the right values for selects; it will truncate that
returned values at the \0.


On Thu, Apr 19, 2018 at 5:27 AM, MARCHAND Loïc 
wrote:

> I index a file in a SQLite DB.
> I create my table with this:
> CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID))
>
> I read a file, and for each line I add a row on the table. Each line can
> have binary data at end. It's not a problem for many chars, but \0 char
> make a problem.
> If I have a line like this : "My data \0with binary".
> When I try to get data after the \0 not worked (SELECT substr(Data, 11, 5)
> FROM Record return an empty string or SELECT substr(Data, 4, 10) FROM
> Record return data)
>
> When I try to search a data (SELECT Data FROM Record WHERE Data LIKE
> '%binar%') return 0 rows returned.
>
> How can I solve this problem ? I try to replace \0 by an other char
> sequence, but it's not a good idea because can I have this sequence in my
> file.
> Thank you
> Loïc
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 - Search on text field with \0 binary data

[MARCHAND Loïc]

I index a file in a SQLite DB.
I create my table with this:
CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID))

I read a file, and for each line I add a row on the table. Each line can have 
binary data at end. It's not a problem for many chars, but \0 char make a 
problem.
If I have a line like this : "My data \0with binary".
When I try to get data after the \0 not worked (SELECT substr(Data, 11, 5) FROM 
Record return an empty string or SELECT substr(Data, 4, 10) FROM Record return 
data)

When I try to search a data (SELECT Data FROM Record WHERE Data LIKE '%binar%') 
return 0 rows returned.

How can I solve this problem ? I try to replace \0 by an other char sequence, 
but it's not a good idea because can I have this sequence in my file.
Thank you
Loïc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Are you getting spam when you post to sqlite-users ?

[Keith Medcalf]

Richard,

The ARIN PPML has the same spammer harvesting their list to get subscriber 
email addresses and replying with the same type of "spam" ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
>Sent: Wednesday, 18 April, 2018 12:11
>To: SQLite mailing list
>Subject: Re: [sqlite] Are you getting spam when you post to sqlite-
>users ?
>
>On 4/18/18, Dennis Clarke  wrote:
>>
>>> "Banishing" means configuring IP filters on the server to silently
>>> discard any and all IP packets that originate from the targeted
>range
>>> of IP addresses.
>>
>> This is the best method that I have ever used and I can tell you
>that
>> your ipfilter rules can get quite long using this approach.  In
>truth
>> I banish ALL registered subnets from certain areas of the world
>> and then life goes on.  Entire continents blocked.
>>
>
>This technique works to prevent me from getting the spam, because all
>my email goes through the machine doing the blocking - sqlite.org.
>Unfortunately, the email for the other 2000+ people on this mail list
>goes through different servers which are not blocking the misbehaving
>IP address, and so they are still seeing the spam.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[dmp]

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?

> You can post on this mailing list or send your reply directly to me.

1. Which JDBC are you using?

Current Xerial SQLite JDBC
https://github.com/xerial/sqlite-jdbc

Others that I have tried are a Werner-JDBC and Zentus-JDBC. The
Xerial if I remember was derived from one of those. The most
current functional up to date is I believe Xerial.

2. For how long have you been using it?

My Java project, Ajqvue, began support for SQLite in 2014 and
that is when most of the research was done on finding a decent
functional JDBC.

3. What issues you had with this driver?

Ajqvue is a generic GUI for connecting to several databases to
include SQLite. That means I need to connect to a database and
display any table data and provide functionality for import/export
of data. The application also comes with plotting, analysis,
and profiling capabilities.

SQLite was one of the easiest database to support because it
only has four data types. Most of all my problems with JDBCs
have arrive with data types, mainly temporal ones, Date, Time,
and the dreaded Timestamp. Currently that is one of the main
issues I have with the current Xerial JDBC.

https://github.com/danap/ajqvue/issues/2

See:

https://github.com/xerial/sqlite-jdbc/issues
https://github.com/xerial/sqlite-jdbc/issues/88

Overall:
  * Does anyone respond to issues? :) Thats about it!

4. What advise do you have for avoiding problems in this driver?

Since my main experience with a driver is through a user GUI
and I'm not using in production I'm sure others could answer
this better. Currently I trying to complete a plugin for Ajqvue
that transfers data from others databases to SQLite. So I'm
sure I may come across some other discrepancies.

https://github.com/danap/db_to_filememorydb

From my perspective your database table schemas should be
based on the SQLite four data types, NO others. Of course
since I was instructed in structured programmer, I really
don't think a specified data type, can be any desired object
and the database really doesn't care. It really confuses me!

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLiteProviderServices Access

[Efe Burak]

Hi can we make SQLiteProviderServices public? I need to initialize the
database from the code and was surprised to find the class is internal
sealed. Or I can use any other alternatives to initializing the db without
an App.Config. I have no access to App.Config being used in the
application.

This has been suggested before but doesn't seem to have gone anywhere.

http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-February/051519.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Richard Hipp]

On 4/19/18, Quan Yong Zhai  wrote:
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
>
> Before:
>wordcount --all :memory: sqlite3.c
>   2.406 wordcount --insert
>   2.296 wordcount --insert --without-rowid
>
> After:
> wordcount --all :memory: sqlite3.c
>   1.701 wordcount --insert
>   3.547 wordcount --insert --without-rowid
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.

That's a good testing idea.  Thank you.  I will make a similar change
and investigate the cause of the slowdown, and hopefully fix the
problem.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Quan Yong Zhai]

Dear Richard,

I modified the wordcount.c in SQLite/test directory, to use the new upsert 
command:

   INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE SET 
cnt=cnt+1

Before:

   wordcount --all :memory: sqlite3.c

  2.406 wordcount --insert

  2.296 wordcount --insert --without-rowid

After:

wordcount --all :memory: sqlite3.c

  1.701 wordcount --insert

  3.547 wordcount --insert --without-rowid



As you can see, it’s very strangely ,in the table with rowid, the upsert 
improved a lot, but in the table without rowidd, it’s slower than the origin 
sql.





Sent from Mail for Windows 10




From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Thursday, April 19, 2018 6:29:55 PM
To: General Discussion of SQLite Database
Subject: [sqlite] UPSERT available in pre-release

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqldiff wish list

[Dominique Devienne]

Hi,

First, thanks for the tool. It's a useful one.

But here's a quick wish-list, after using the tool in a real use case:
1) a --version switch, to know which SQLite version is statically compiled
inside sqldiff
2) in-row difference: Within a row-pair, generate a minimal UPDATE and
omits all same-value columns.
3) ability to ignore some columns when computing the diff.
4) ability to use a UNIQUE INDEX as a 3rd alternative to rowid and PRIMARY
KEY row-pairing.

And here are my rationals for the above.

1) no symbols are exported. I don't remember when I got that tool. Would be
nice to have IMHO.

2) I have blobs in my tables, and small diffs in other columns still want
to update the large blobs. Makes for a much larger diff than necessary, and
also makes it hard to see what really needs changing.

3) 4) these two are linked. I have randomly generated surrogate keys (i.e.
Guids used as PRIMARY KEY, for compact FKs of composite NKs), and would
like to use the natural key (NK) instead, for row-pairing, and ignore the
differences in the PK.

These would be IMHO generally useful features, that could benefit many.
I might come around to implementing some eventually, but I wouldn't be
surprised
if someone beat me to it, and I'm not sure Richard would accept patches
either.

Thanks, --DD

PS: Hmmm, I realize that my child table NKs still reference the parent
table Guid PKs, so ignoring the PK in favor of the NK / UNIQUE INDEX would
work only for "top-level" tables... Bummer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Petite Abeille]

> On Apr 19, 2018, at 1:06 PM, Richard Hipp  wrote:
> 
> We are open to adding MERGE INTO at some point in the future.

Excellent! 

>  But the UPSERT syntax is both easier to understand

Debatable.

> and easier to implement,

Possibly.

> and we prefer to follow PostgreSQL syntax whenever possible.  See
> https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for
> PostgreSQL's rationale for rejecting MERGE.

Let’s agree to disagree on that long running opinion piece.

MERGE, as per SQL:2003 & SQL:2008 & co. is the way to go.

The situation remind me of the introduction of recursive common table 
expression (CTE) in SQLite, which at first you wanted to implement solely in 
terms of Oracle’s 'CONNECT BY’ syntax, but ultimately saw the benefit of 
embracing the full-fledged CTE syntax instead.

And I’m personally very grateful for that thought process which gifted SQLite a 
kickass CTE implementation. Thanks for that!

Hopefully, the UPSERT vs. MERGE conversation will move that way as well: MERGE 
FTW! :)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Richard Hipp]

On 4/19/18, Petite Abeille  wrote:
>
>
>> On Apr 19, 2018, at 12:29 PM, Richard Hipp  wrote:
>>
>> The latest pre-release snapshot [1]
>
> Link missing?

[1] https://sqlite.org/download.html

>
>> contains support for UPSERT
>> following the PostgreSQL syntax.
>>  The documentation is still pending.
>> Nevertheless, early feedback is welcomed.  You can respond either to
>> this mailing list, or directly to me.
>
> Postgres UPSERT?!?
>
> Wouldn’t a standard ANSI MERGE be more appropriate?
>

We are open to adding MERGE INTO at some point in the future.  But the
UPSERT syntax is both easier to understand and easier to implement,
and we prefer to follow PostgreSQL syntax whenever possible.  See
https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for
PostgreSQL's rationale for rejecting MERGE.

MySQL also has UPSERT but no MERGE.  The MySQL UPSERT syntax is
similar, but omits the constraint-target clause following the ON
CONFLICT.  So if there are multiple uniqueness constraints, you never
know which one will receive the UPSERT in MySQL.  That seemed
problematic, so we dropped support for the MySQL syntax during
development.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Shevek]

Opinion: Of all the DBMS's UPSERT/MERGE semantics, postgresql's is the 
least useful because it's very limited: It can only do a check against a 
constraint, and the cost of evaluating that constraint has to be carried 
by all other statements which mutate the table. Oracle/Teradata MERGE is 
a far more useful semantics because it's defined more like a self-join, 
where the constraint is specified in the statement, not the DBMS.


On 04/19/2018 11:29 AM, Richard Hipp wrote:

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Petite Abeille]

> On Apr 19, 2018, at 12:29 PM, Richard Hipp  wrote:
> 
> The latest pre-release snapshot [1]

Link missing?

> contains support for UPSERT
> following the PostgreSQL syntax.
>  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.

Postgres UPSERT?!?

Wouldn’t a standard ANSI MERGE be more appropriate?

https://en.wikipedia.org/wiki/Merge_(SQL)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT available in pre-release

[Richard Hipp]

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Any operation to trigger osMunmap?

[Hick Gunter]

For PSS to become smaller, pages have to be either unmapped or evicted from 
main memory by the OS.

Probably the only way to force SQLite to unmap the memory is to set a new 
mmap_size (possibly after closing and reopening the database connection, which 
will cause a boatload of IO that most certainly will overwhelm any performance 
gains).

The OS will typically evict pages from main memory only if it needs fresh 
memory and none is available in free memory. Under high enough memory pressure, 
unix based OSses will kill the process they think is hogging the most memory 
(this could be a completel y unrelated process, or possible the process that 
has gone haywire and is requesting insane amounts of memory), repeatedly, until 
the pressure subsides, while overall performance declines.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nick
Gesendet: Donnerstag, 19. April 2018 04:07
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Any operation to trigger osMunmap?

So the PSS will not decrease even if the db becomes smaller after some 
DELETE/vacuum operations?

I think it is a better way to free the mmap memory after every query 
automatically inside sqlite. Why not?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users