Android / SQLite - backticks, single quotes or double quotes?

Question

Suppose I had a method like this...

long getNumItemsFromDb() {
SQLiteDatabase db = dbhelper.getReadableDatabase();
try {
    String query = "SELECT " + COL_NAME +
                    " FROM " + TABLE_NAME +
                    " WHERE " + COL_NAME + " = ?";
    String[] args = new String[] {"whatever"};
    return DatabaseUtils.longForQuery(db, query, args);
} finally {
    db.close();
}

}

...but it's possible that, for example, String COL_NAME = "select"; and String TABLE_NAME = "from"; - which is going to break the query. So, I'd obviously need to surround those values in my query String with either backticks, single quotes or double quotes - but which of these is the best practice for Android / SQLite?

NB - I have simplified my query String above to make this question simpler and more to the point. So, in reality, I do need to create the SQL manually like this rather than using one of the helper methods in Android.

NB2 - I have seen similar questions here and here but the questions/answers do not address SQLite and Android.

score 3 · Answer 1 · edited May 23 '17 at 10:32

3

I'd say go with the standard.

The ANSI standard is double quotes for quoting fields/identifiers, and that works well on SQLite.

Note that some other RDBMS's may need some help to follow the standard, but following it will allow your SQL to run unchanged on as many RDBMS's/platforms as possible.

edited May 23 '17 at 10:32

Community

1
1

answered Jul 04 '14 at 13:42

Joachim Isaksson

176,943
25
281
294

I use [some thing] (braces) in Access, SQL Server and SQLite without any problem. – Phantômaxx Jul 04 '14 at 14:31
@FrankN.Stein Yes, square brackets work in some RDBMS', but they're not standardised so will not work in - for example - Oracle, MySQL or PostgreSQL. – Joachim Isaksson Jul 04 '14 at 14:36
1

... just the ones I never tested them on... ;) – Phantômaxx Jul 04 '14 at 14:43

score 0 · Answer 2 · answered Jul 04 '14 at 13:54

If you were to use androids class SQLitedatabase you could just simply use the query method and not worry if your selection string contains any of the identifiers.

Though when using FTS tables with MATCH identifier I noticed that you must surround the searchable string with percent signs like this: "%"+str+"%", otherwise it only matches up until a space.

Android / SQLite - backticks, single quotes or double quotes?

2 Answers2