Re: [sqlite] Stricter parsing rules

[Dan Freundel]

If it was called "PRAGMA strict_mode" or even "PRAGMA disable_dbl_quot_lit" you 
could reverse the check and then the default behavior would remain the same.

--- On Fri, 11/16/12, NSRT Mail account. <joecool2...@yahoo.com> wrote:

From: NSRT Mail account. <joecool2...@yahoo.com>
Subject: Re: [sqlite] Stricter parsing rules
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Friday, November 16, 2012, 1:44 AM

Hi Dan,

I had some free time and looked into your request. Bear in mind I don't really 
know what I'm doing, but I managed to whip up this: 
http://paste.nachsoftware.com/SQLite3/BrksDfe9d421e8ed00d34f0fc50a59956af21byK

Results:
> ./sqlite3
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT "cows";
Error: no such column: cows
sqlite> PRAGMA dblquoted_str_lit=1;
sqlite> SELECT "cows";
cows
sqlite> PRAGMA dblquoted_str_lit=0;
sqlite> SELECT "cows";
Error: no such column: cows


I believe this is what you wanted. I imagine the naming would be improved if 
this code became mainline, and the ifdefs would be handled better, but I guess 
this is pretty much what you wanted.

Note: This changes the default behavior of SQLite. A proper patch of course 
would keep the default behavior. Although I couldn't figure out the pragma 
system enough to see how to specify what default pragma settings are.




 From: Dan Freundel <defender1...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
Sent: Thursday, November 15, 2012 7:35 PM
Subject: Re: [sqlite] Stricter parsing rules
 
I agree as well. I tested out the original poster's patch and it works great. 
Any way this can be made into a "PRAGMA strict_mode" so that it would be 
usage-specific rather than compile-specific?
I asked about this on stackoverfolw as 
well:http://stackoverflow.com/questions/13383763/is-there-a-way-disable-lax-quoting-rules-in-sqlite/

--- On Thu, 11/15/12, John Gabriele <jmg3...@gmail.com> wrote:

From: John Gabriele <jmg3...@gmail.com>
Subject: Re: [sqlite] Stricter parsing rules
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Thursday, November 15, 2012, 1:56 PM

On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 14 Nov 2012, at 9:46pm, BareFeetWare <list@barefeetware.com> wrote:
>
>> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
>> errors. I'd really like to see the rules tightened to remove ambiguous input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in 
> SQLite4.

Me four. The OP's practice of always using double-quotes for
identifiers seems like a sound one, and it would be useful if sqlite
could tell me if the thing I *think* is an identifier is actually not.

---John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[NSRT Mail account.]

Hi Dan,

I had some free time and looked into your request. Bear in mind I don't really 
know what I'm doing, but I managed to whip up this: 
http://paste.nachsoftware.com/SQLite3/BrksDfe9d421e8ed00d34f0fc50a59956af21byK

Results:
> ./sqlite3
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT "cows";
Error: no such column: cows
sqlite> PRAGMA dblquoted_str_lit=1;
sqlite> SELECT "cows";
cows
sqlite> PRAGMA dblquoted_str_lit=0;
sqlite> SELECT "cows";
Error: no such column: cows


I believe this is what you wanted. I imagine the naming would be improved if 
this code became mainline, and the ifdefs would be handled better, but I guess 
this is pretty much what you wanted.

Note: This changes the default behavior of SQLite. A proper patch of course 
would keep the default behavior. Although I couldn't figure out the pragma 
system enough to see how to specify what default pragma settings are.




 From: Dan Freundel <defender1...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
Sent: Thursday, November 15, 2012 7:35 PM
Subject: Re: [sqlite] Stricter parsing rules
 
I agree as well. I tested out the original poster's patch and it works great. 
Any way this can be made into a "PRAGMA strict_mode" so that it would be 
usage-specific rather than compile-specific?
I asked about this on stackoverfolw as 
well:http://stackoverflow.com/questions/13383763/is-there-a-way-disable-lax-quoting-rules-in-sqlite/

--- On Thu, 11/15/12, John Gabriele <jmg3...@gmail.com> wrote:

From: John Gabriele <jmg3...@gmail.com>
Subject: Re: [sqlite] Stricter parsing rules
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Thursday, November 15, 2012, 1:56 PM

On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 14 Nov 2012, at 9:46pm, BareFeetWare <list@barefeetware.com> wrote:
>
>> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
>> errors. I'd really like to see the rules tightened to remove ambiguous input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in 
> SQLite4.

Me four. The OP's practice of always using double-quotes for
identifiers seems like a sound one, and it would be useful if sqlite
could tell me if the thing I *think* is an identifier is actually not.

---John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[NSRT Mail account.]

Hello Everyone,

I'm glad to see I'm not the only one who wants this.

I'm thinking perhaps I wasn't clear enough in my first e-mail that I created a 
patch for the time being.
You can download the patch, along with already modified source, static 
libraries and binaries for Linux i386, Linux AMD64, and Windows here:

http://nsrt.nachsoftware.org/sqlite3-3.7.14.1-without-dblquote-string.tar.bz2
467a12059d50850f394066195d70c659
c15bc452d9db38ac47a557d06f89ff0658220861


If you're compiling yourself, add -DSQLITE_OMIT_DBLQUOTED_STRINGS to your flags 
to remove double quoted string literals from being supported.
I really wanted to know what people think of the patch itself.


@Tom
>this tolerance by SQLite for misquoted identifiers allows a lot of 
errors.
The case I'm dealing with here is actually misquoted string literals, which 
affects misspelled identifiers. I assume that's what you meant. While I see 
some wanting the ability to drop [] and `` from identifier support for the sake 
of purity, I don't know of a case where keeping it actually breaks any valid 
SQL.

@Simon
>Perhaps this and a couple of similar things will be fixed in SQLite4.

While I hope that too, I would like a fix to become mainline in SQLite3. As is, 
you can play with my patch right now in the latest version of SQLite3!

@Dave
>The behaviour of quoting with [identifier] or `identifier` seems to do what
you want, but, as the docs say, these are not standard SQL.

Nice idea! Definitely one to consider without any kind of patch. However if 
you're trying to write cross platform SQL, that can be a bit painful. :(

@Dan
>I tested out the original poster's patch and it works great. 
Any way this can be made into a "PRAGMA strict_mode" so that it would be 
usage-specific rather than compile-specific?

Thanks for testing it. I appreciate the feedback and glad to hear it works well 
for you.
I don't know a lot about SQLite's source code. I just really wanted this fixed, 
and thank God after 15 minutes of grepping the source, I happened upon the code 
which seems to control double quoted string literals. I don't really know 
SQLite's source code, let alone the pragma system, but if I have time, I'll see 
what I can do.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[Dan Freundel]

I agree as well. I tested out the original poster's patch and it works great. 
Any way this can be made into a "PRAGMA strict_mode" so that it would be 
usage-specific rather than compile-specific?
I asked about this on stackoverfolw as 
well:http://stackoverflow.com/questions/13383763/is-there-a-way-disable-lax-quoting-rules-in-sqlite/

--- On Thu, 11/15/12, John Gabriele <jmg3...@gmail.com> wrote:

From: John Gabriele <jmg3...@gmail.com>
Subject: Re: [sqlite] Stricter parsing rules
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Thursday, November 15, 2012, 1:56 PM

On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 14 Nov 2012, at 9:46pm, BareFeetWare <list@barefeetware.com> wrote:
>
>> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
>> errors. I'd really like to see the rules tightened to remove ambiguous input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in 
> SQLite4.

Me four. The OP's practice of always using double-quotes for
identifiers seems like a sound one, and it would be useful if sqlite
could tell me if the thing I *think* is an identifier is actually not.

---John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[John Gabriele]

On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin  wrote:
>
> On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:
>
>> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
>> errors. I'd really like to see the rules tightened to remove ambiguous input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in 
> SQLite4.

Me four. The OP's practice of always using double-quotes for
identifiers seems like a sound one, and it would be useful if sqlite
could tell me if the thing I *think* is an identifier is actually not.

---John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[Dave McKee]

The behaviour of quoting with [identifier] or `identifier` seems to do what
you want, but, as the docs say, these are not standard SQL.

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

It's not a great option, since it forces you to have nonstandard SQL, but
it's a potentially useful one to have on the table.

Dave.

sqlite> create table jam (valid int);
sqlite> insert into jam values (12);
sqlite> select [invalid] from [jam];
Error: no such column: invalid
sqlite> select `invalid` from jam;
Error: no such column: invalid
sqlite> select invalid from jam;
Error: no such column: invalid
sqlite> select "invalid" from jam;
invalid
sqlite> select 'invalid' from jam;
invalid




On Wed, Nov 14, 2012 at 10:22 PM, Simon Slavin  wrote:

>
> On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:
>
> > I agree. this tolerance by SQLite for misquoted identifiers allows a lot
> of errors. I'd really like to see the rules tightened to remove ambiguous
> input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in
> SQLite4.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[Simon Slavin]

On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:

> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
> errors. I'd really like to see the rules tightened to remove ambiguous input.

Me three.  Perhaps this and a couple of similar things will be fixed in SQLite4.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[BareFeetWare]

I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
errors. I'd really like to see the rules tightened to remove ambiguous input.

Thanks,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

On 15/11/2012, at 7:37 AM, NSRT Mail account.  wrote:

> However, we've been bitten a few times by SQLite's compatibility features 
> which relax rules, and create ambiguity.
> 
> When listing columns to retrieve in SELECT, or as part of a WHERE clause, we 
> always enclose our identifiers in double quotes. However, if a developer 
> accidentally misspells a column name, instead of failing with "Error: no such 
> column: xx", SQLite reinterprets the identifier as a string, and carries 
> on as if nothing happened. Sometimes such bugs go unnoticed for a while.
> 
> Is there any way to make SQLite's parsing stricter?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Stricter parsing rules

[NSRT Mail account.]

In accordance with SQL standards and SQLite developing guidelines, my entire 
team always uses double quotes around identifiers, and single quotes around 
string literals.

Some relevant documentation is here: http://www.sqlite.org/lang_keywords.html
"SQLite adds new keywords from time to time when it takes on new features.
So to prevent your code from being broken by future enhancements, you should
normally quote any identifier that is an English language word, even if
you do not have to." 


However, we've been bitten a few times by SQLite's compatibility features which 
relax rules, and create ambiguity.

When listing columns to retrieve in SELECT, or as part of a WHERE clause, we 
always enclose our identifiers in double quotes. However, if a developer 
accidentally misspells a column name, instead of failing with "Error: no such 
column: xx", SQLite reinterprets the identifier as a string, and carries on 
as if nothing happened. Sometimes such bugs go unnoticed for a while.

Is there any way to make SQLite's parsing stricter? Or are the only options to 
have code which fails silently, or unquoted identifiers which may break with an 
SQLite upgrade?
I could not find any compiling options or pragmas for this, so I looked through 
the SQLite source code to see if I could fix this myself.

In resolve.c, (which in the amalgamation for 3.7.14.1 at line 72340) I found 
this bit of code with a comment:
  /*
  ** If X and Y are NULL (in other words if only the column name Z is
  ** supplied) and the value of Z is enclosed in double-quotes, then
  ** Z is a string literal if it doesn't match any column names.  In that
  ** case, we need to return right away and not make any changes to
  ** pExpr.
  **
  ** Because no reference was made to outer contexts, the pNC->nRef
  ** fields are not changed in any context.
  */
  if( cnt==0 && zTab==0 && ExprHasProperty(pExpr,EP_DblQuoted) ){
    pExpr->op = TK_STRING;
    pExpr->pTab = 0;
    return WRC_Prune;
  }

I wrapped this in a define which omits double quotes strings by default and 
tested it.

Before:
sqlite> SELECT 'cows';
cows

sqlite> SELECT "cows";

cows

After:
sqlite> SELECT 'cows';
cows 
sqlite> SELECT "cows";
Error: no such column: cows

I wanted to know if
A) There is some existing options that I overlooked?
B) If not, is my "fix" correct, or will it break something else?
C) Can a stricter parsing option become part of the mainline codebase?

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users