unrecognized token error

[isrep]

Details for the issue

Selecting values in double quotes containing double hyphen substring returns "unrecognized token" error.

Please see example below.
--Error unrecognized token:
select "anything--something"

--no error
select 'anything--something'

Useful extra information

I'm opening this issue because:

• DB4S is crashing
• DB4S has a bug
• DB4S needs a feature
• DB4S has another problem

I'm using DB4S on:

• Windows: ( version: ___ )
• Linux: ( _distro:Ubuntu )
• Mac OS: ( version: ___ )
• Other: ___

I'm using DB4S version:

• 3.10.1
• 3.10.0
• 3.9.1
• Other: 8896ecc

I have also:

• Tried out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
• Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

[justinclift]

Ouch, that definitely sounds like a bug. Have you tried any of the commits prior to that, to see if that's the one which introduces it?

[isrep]

The same error with version 3.10.99 from 2017-12-09, I see on my system. Thank you for looking into it.

[justinclift]

Oops. Just realised something that should have been obvious to me right away before.

This is probably not a bug after all. With SQL, the single and double quote characters aren't the same thing.

• Single quote characters ' are used to start and end strings. eg:

SELECT foo FROM bar WHERE baz = 'stuff';

• Double quote characters " are used to start and end identifiers (such as field names). eg:

SELECT "my field with spaces in its name" FROM bar;

In the above example, the table bar contains a field called my field with spaces in its name.

Further reference info, in case it's useful:

    https://www.sqlite.org/lang_keywords.html

With your double quote example, the "unrecognized token" message is probably meaning that it's looking for a field called (literally) "anything--something", and not finding it. 😉

Does that help?

[isrep]

Thank you for pointing to SQLite documentation.
I have bumped into this while operating with strings already containing single quotes.
It seems that official sqlite3 command line tool does not produce "unrecognized token" error.
Thanks anyway.

sqlite3
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 'anything--something';
anything--something
sqlite> select "anything--something";
anything--something
sqlite>

sqlite> create table bar (c1 TEXT);
sqlite> insert into bar (c1) values ('TEXT1');
sqlite> insert into bar (c1) values ("TEXT2");
sqlite> insert into bar (c1) values ("TEXT2---something");
sqlite> SELECT "my field with spaces in its name" from bar;
my field with spaces in its name
my field with spaces in its name
my field with spaces in its name
sqlite> SELECT "my field with spaces in its name", c1 from bar;
my field with spaces in its name|TEXT1
my field with spaces in its name|TEXT2
my field with spaces in its name|TEXT2---something
sqlite>

[justinclift]

Hmmm, yeah you're right. I've just tried it here with the sqlite3 client (and older version, 3.7.17 on my CentOS 7 desktop), and a recent build of DB4S.

It looks like DB4S is getting misunderstanding the double quotes. For example, with this (from above):

insert into bar (c1) values ("TEXT2");

In the sqlite3 CLI it works, adding a new row into the table bar. DB4S gives this error instead:

unrecognized token: ""TEXT2": 

Looking at that error message, there's an extra " at the start of the TEXT2 string. Guessing here, but this looks like we're somehow getting the quotes wrong after all.

So... yep this does seem like a bug. Our Execute SQL tab should accept anything that the SQLite CLI does. Thanks for being persistent. 😀

[justinclift]

I'm kind of wondering if this is related to the regular expression crafted for #1004.

[MKleusberg]

Thanks for pointing this out, @isrep! I think I managed to tweak the regular expression to not screw up your SQL statement. Can you give tomorrow's nightly build a try and see if it's working for you? 😃

[MKleusberg]

Sorry, I'm going to revert my changes. So no need to test the nightly.

It's introducing new issues which I can't solve right now 😦 If anybody with leet regular expression skills wants to try themselves, please go ahead 😉

[justinclift]

@h4yn0nnym0u5e @revolter Any interest? 😄

[MKleusberg]

You can check out my fumblings in eae0730 and 29fa332.

We need to make sure it's working for all these statements:

SELECT "something--something" -- comment
SELECT 'something--something' -- comment
SELECT "something--some'thing" -- comment
SELECT 'something--some"thing' -- comment

And as a bonus maybe for these too:

SELECT `something--something` FROM table -- comment
SELECT [something--something] FROM table -- comment

And for these:

SELECT '/*test*/' -- comment
SELECT "/*test*/" -- comment
SELECT '/*te"st*/' -- comment
SELECT "/*te'st*/" -- comment
SELECT `/*test*/` FROM table -- comment
SELECT [/*test*/] FROM table -- comment

[mgrojo]

I'm going to close this issue because all of this is solved after #1455 since we no longer try to strip comments before passing SQL code to SQLite for execution.