Re: [sqlite] sqlite command line tool fails to dump data

[Simon Slavin]

On 22 Jan 2018, at 4:46pm, J Decker  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite command line tool fails to dump data

[J Decker]

On Sun, Jan 21, 2018 at 11:48 PM, Clemens Ladisch 
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 ;"

5.1  

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

 Format

  ::=

  | 

  ::=

  | 

  ::=

  | 
  | 

  ::=

  | 

  ::=
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

  ::=
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

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

  ::=

  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 

  ::= !! space character in character set in use
  ::= "
  ::= %
  ::= &
  ::= '
  ::= (
  ::= )
  ::= *
  ::= +
  ::= ,
  ::= -
  ::= .
  ::= /
  ::= :
  ::= ;
  ::= <
  ::= =
  ::= >
  ::= ?
  ::= [
  ::= ]
  ::= _
  ::= |

 General Rules

 1) There is a one-to-one correspondence between the symbols con-
tained in  and the symbols
contained in  such that, for
all i, the symbol defined as the i-th alternative for  corresponds to the symbol defined as
the i-th alternative for .


> Regards,
> Clemens
> ___
> 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] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite command line tool fails to dump data

[J Decker]

On Sun, Jan 21, 2018 at 7:42 AM, Clemens Ladisch  wrote:

> J Decker wrote:
> > insert into test (a,b) values ( ?,? )
> >   bind 'hello\0world.' 'te\0st'
>
>  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
> 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] sqlite command line tool fails to dump data

[Clemens Ladisch]

J Decker wrote:
> insert into test (a,b) values ( ?,? )
>   bind 'hello\0world.' 'te\0st'

 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users