On Sun, Jan 21, 2018 at 11:48 PM, Clemens Ladisch <clem...@ladisch.de> 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 > 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
