I have a database with a table name containing a quote and a space.
Let's say it's called “Ben's table”
I have created it with:
CREATE TABLE "Ben's table" ([column_spec]);
I tried the following in the SQLite shell:
.import 'file_name.txt' "Ben's table"
But that does not work. So I had to dig a bit into shell.c to
understand, and I found the following:
* inside the do_meta_command function, the arguments to dot-commands
are tokenized with the following rules:
* The tokenizer skips all whitespace character when finding the
start for the next token.
* When ' or " is found where a token should start, the token will be
the portion of text between that delimiter and its next occurrence.
There is no possibility to escape the delimiter.
The two delimiters are discarded.
* If any other character is found where a token should start, the
token ends at whitespace.
* Backslashes get special processing except for tokens that were
delimited by the single quote.
* inside the same do_meta_command method, when processing the import
command, the name of the table is appended to some queries this way:
sqlite3_snprintf(nByte+20, zSql, "INSERT INTO %s VALUES(?", zTable);
So, my .import command was tokenized that way:
Token 1: file_name.txt
Token 2: Ben's table <--- note: double quotes gone.
And the SQL query that was forged was incorrect:
INSERT INTO Ben's table VALUES(?...
The only way I found in order to have my import succeed was:
.import 'file_name.txt' "[Ben's table]"
which I find cumbersome.
Are there any plans to improve the tokenizer to enable escaping the
delimiter (doubling the single quote or backslash-escaping the double
quote), and to escape the table name in the dot-commands?
Potential problem: how to use .backup or .restore with a file name that
contains both ' " and space?
Thanks.
--
Benoit Mortgat
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
