SQLite and Quotes

Databases
1

There has been some discussion about the proper or not so proper use of single and double quotes in SQLite.
It turns out there is at least one circumstance where it makes a HUGE difference

take for example a situation where you want to read a table, and create a NEW field to use as a LABEL for the data.
and the label is going to be the name of the field.

SELECT "myField" as myLabel,myfield

DOES NOT WORK… you get two fields, and they both contain the data values for the field ‘myField’
in this case you MUST use SINGLE quotes
you would get something like

SELECT 'myField' as myLabel,myfield

here you would get

If you use DOUBLE quotes and the value inside the quotes does NOT resolve to a named field in the table, you get the string you wanted… but if it does, it resolves to the data value

2

http://sqlite.org/faq.html#q24

3

However… due to the fact that it is not “strict”, it causes people to get in to a habit that is not good…

because

SELECT "myFieldx" as myLabel,myfield

DOES work… leading some people to believe that is “ok” to continue this practice.

4

I would suggest working with preparedstatements, even with SQLite… Solves a lot of issues.

5

Doesn’t solve that one… especially when the SQL statement is entered manually by a user, and not internally generated by the app