sqlite command line tool fails to dump data

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite command line tool fails to dump data

J Decker
sqlite test.db
create table test (a,b)
insert into test (a,b) values ( ?,? )
  bind 'hello\0world.' 'te\0st'

.dump table

-- output

CREATE TABLE test (a,b);
INSERT INTO test VALUES('hello','te');
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite command line tool fails to dump data

Luuk
On 21-01-18 16:16, J Decker wrote:
> create table test (a,b)
> insert into test (a,b) values ( ?,? )
>   bind 'hello\0world.' 'te\0st'

luuk@opensuse:~/tmp> sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test(a,b);
sqlite> insert into test values ('hello\0world','te\0st');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(a,b);
INSERT INTO "test" VALUES('hello\0world','te\0st');
COMMIT;
sqlite>


who told you that 'bind' works on the shell??
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite command line tool fails to dump data

Clemens Ladisch
In reply to this post by J Decker
J Decker wrote:
> insert into test (a,b) values ( ?,? )
>   bind 'hello\0world.' 'te\0st'

<http://www.sqlite.org/c3ref/bind_blob.html> says:
| If a non-negative fourth parameter is provided to sqlite3_bind_text()
| or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter
| must be the byte offset where the NUL terminator would occur assuming
| the string were NUL terminated. If any NUL characters occur at byte
| offsets less than the value of the fourth parameter then the resulting
| string value will contain embedded NULs. The result of expressions
| involving strings with embedded NULs is undefined.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite command line tool fails to dump data

J Decker
On Sun, Jan 21, 2018 at 7:42 AM, Clemens Ladisch <[hidden email]> wrote:

> J Decker wrote:
> > insert into test (a,b) values ( ?,? )
> >   bind 'hello\0world.' 'te\0st'
>
> <http://www.sqlite.org/c3ref/bind_blob.html> says:
> | If a non-negative fourth parameter is provided to sqlite3_bind_text()
> | or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter
> | must be the byte offset where the NUL terminator would occur assuming
> | the string were NUL terminated.
>
> *If any NUL characters occur at byte| offsets less than the value of the
> fourth parameter then the resulting| string value will contain embedded
> NULs.*


So it's best used as data, and not keys
and I see unless custom aggregate()s or function()s ...


> The result of expressions
> | involving strings with embedded NULs is undefined.
>
>
A JSON dump which would be an addition to not break other compatibility
could store the correct value....
the .dump works from a sqlite_value which has text() and bytes() which
would encode in json with '\0' since it needs \, 0, " (n, f, t, b, u (for
very high characters) ) etc all escaped...
then it's also reading from a different command pipe (importing a file)


As a side question

insert into ? (?,?) values(?,?)
with bind ( 'ta\0le', '\0','\1', 'hello\0','\0world' )

bad things happen :)  but what if I ?


> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite command line tool fails to dump data

Clemens Ladisch
J Decker wrote:
>> *If any NUL characters occur at byte| offsets less than the value of the
>> fourth parameter then the resulting| string value will contain embedded
>> NULs.*
>
> So it's best used as data, and not keys
> and I see unless custom aggregate()s or function()s ...

If you want embedded NULs, use blobs.

> insert into ? (?,?) values(?,?)
> with bind ( 'ta\0le', '\0','\1', 'hello\0','\0world' )
>
> bad things happen :)  but what if I ?

In this case, the bad thing that happens is a syntax error; you cannot
use parameters for table/column names.

And SQL statements cannot contain embedded NULs; parsing stops at the
detected end of the string.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite command line tool fails to dump data

J Decker
On Sun, Jan 21, 2018 at 11:48 PM, Clemens Ladisch <[hidden email]>
wrote:

> J Decker wrote:
> >> *If any NUL characters occur at byte| offsets less than the value of the
> >> fourth parameter then the resulting| string value will contain embedded
> >> NULs.*
> >
> > So it's best used as data, and not keys
> > and I see unless custom aggregate()s or function()s ...
>
> If you want embedded NULs, use blobs.
>
> But it's not a blob, it's text that I'm saving.


> > insert into ? (?,?) values(?,?)
> > with bind ( 'ta\0le', '\0','\1', 'hello\0','\0world' )
> >
> > bad things happen :)  but what if I ?
>
> In this case, the bad thing that happens is a syntax error; you cannot
> use parameters for table/column names.
>
> And SQL statements cannot contain embedded NULs; parsing stops at the
> detected end of the string.
>
> by SQL you mean  PSSQL and Sqlite

MySQL https://dev.mysql.com/doc/refman/5.7/en/string-literals.html
TSQL
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql
Oracle can use Chr(0); but its tools can often be misleading

And sqlite internally has no problems storing and retrieving the data
faithfully; it's just the command line tool (sqlite3) and TCL tests that
have issues.

----

I can do this to insert NUL character...

sqlite test.db
create table test (a)
insert into test (a) values ( "test"||char(0)||"one" )
.dump test

So I CAN escape NUL chars in sqlite by replacing them with '||char(0)||'

------------
From the standard.
The stand makes no mention of NUL or \0 either to allow or disallow, so
it's undefined.... but it's not that 'cannot contain NUL'

create table [with\0nul] ( `col\0``umn` )

is easily parsable, and all that has to be done is keep the tokens as a
whole (string,length) and not fall back to strlen, and trust the original
character count given to prepare.


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 <unlisted characters>;"

5.1  <SQL terminal character>

         Define the terminal symbols of the SQL language and the elements of
         strings.

         Format

         <SQL terminal character> ::=
                <SQL language character>
              | <SQL embedded language character>

         <SQL embedded language character> ::=
                <left bracket>
              | <right bracket>

         <SQL language character> ::=
                <simple Latin letter>
              | <digit>
              | <SQL special character>

         <simple Latin letter> ::=
                <simple Latin upper case letter>
              | <simple Latin lower case letter>

         <simple Latin upper case letter> ::=
                    A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
              | P | Q | R | S | T | U | V | W | X | Y | Z

         <simple Latin lower case letter> ::=
                    a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
              | p | q | r | s | t | u | v | w | x | y | z

         <digit> ::=
              0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

         <SQL special character> ::=
                <space>
              | <double quote>
              | <percent>
              | <ampersand>
              | <quote>
              | <left paren>
              | <right paren>
              | <asterisk>
              | <plus sign>
              | <comma>
              | <minus sign>
              | <period>
              | <solidus>
              | <colon>
              | <semicolon>
              | <less than operator>
              | <equals operator>
              | <greater than operator>
              | <question mark>
              | <underscore>
              | <vertical bar>

         <space> ::= !! space character in character set in use
         <double quote> ::= "
         <percent> ::= %
         <ampersand> ::= &
         <quote> ::= '
         <left paren> ::= (
         <right paren> ::= )
         <asterisk> ::= *
         <plus sign> ::= +
         <comma> ::= ,
         <minus sign> ::= -
         <period> ::= .
         <solidus> ::= /
         <colon> ::= :
         <semicolon> ::= ;
         <less than operator> ::= <
         <equals operator> ::= =
         <greater than operator> ::= >
         <question mark> ::= ?
         <left bracket> ::= [
         <right bracket> ::= ]
         <underscore> ::= _
         <vertical bar> ::= |

 General Rules

         1) There is a one-to-one correspondence between the symbols con-
            tained in <simple Latin upper case letter> and the symbols
            contained in <simple Latin lower case letter> such that, for
            all i, the symbol defined as the i-th alternative for <simple
            Latin upper case letter> corresponds to the symbol defined as
            the i-th alternative for <simple Latin lower case letter>.


> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite command line tool fails to dump data

Simon Slavin-3


On 22 Jan 2018, at 4:46pm, J Decker <[hidden email]> wrote:

> create table [with\0nul] ( `col\0``umn` )

Could you not ?  In fact, could everybody not ?

[Goes to look for ice bag or strong alcohol, whichever appears first.]

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users