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: xxxxxx", 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
