CODEWITHSUNDEEP
androidsqlite
erdomester
erdomester

Reputation: 11829

Android sqlite table name ' character

How is it possible to add a ' mark in an sqlite table name?

The user creates the table and I want to add the ability to use ' mark in the table name. 

e.g Dad's stuff

And what about other special characters like (_ - / , .) ?

I tried 

String newlist_listname = et_NewGroceryList.getText().toString();
unrecognized token: "'s_stuff

newlist_listname.replaceAll("'", "\'");
unrecognized token: "'s_stuff

newlist_listname.replaceAll("'", "\\'");
unrecognized token: "'s_stuff 

newlist_listname.replaceAll("'", "''");
syntax error: CREATE TABLE GL_Mom''s_stuff

newlist_listname = "'" + newlist_listname.replaceAll("'", "''") + "'";
syntax error: CREATE TABLE GL_'Mom''s_stuff'

newlist_listname = "'" + newlist_listname + "'";
syntax error: CREATE TABLE GL_'Mom's_stuff'

newlist_listname = "'" + newlist_listname.replaceAll("'", "\'") + "'";
syntax error: CREATE TABLE GL_'Mom's_stuff'

newlist_listname = "'" + newlist_listname.replaceAll("'", "\\'") + "'";
syntax error: CREATE TABLE GL_'Mom's_stuff'

newlist_listname = "'" + newlist_listname.replaceAll("'", "''") + "'";
syntax error: CREATE TABLE GL_'Mom''s_stuff'

newlist_listname = "\'" + newlist_listname.replaceAll("'", "\'") + "\'";
 syntax error: CREATE TABLE GL_'Mom's_stuff'

newlist_listname = "\\'" + newlist_listname.replaceAll("'", "\'") + "\\'";
unrecognized token: "\": CREATE TABLE GL_\'Mom's_stuff\'

newlist_listname = "''" + newlist_listname.replaceAll("'", "\'") + "''";
syntax error: CREATE TABLE GL_''Mom's_stuff''

(I also replace spaces with _)

Is there anything I have not tried?

Upvotes: 1

Views: 961

Answers (2)

user149341
user149341

Reputation:

I'm assuming this is for some sort of list-making application?

Don't create a table per list. That's bad database design. Create one table for all list entries, and use a column in that table to indicate which list they belong to -- something like this:

+-----------+----------+--------------+
| list      | position | item         |
+-----------+----------+--------------+
| Groceries | 1        | eggs         |
| Groceries | 2        | butter       |
| Groceries | 3        | milk         |
| Hitlist   | 1        | Guido        |
| Hitlist   | 2        | Franco       |
...

With an index on list, this is just as fast -- possibly faster -- than having a table per list.

Upvotes: 1

devunwired
devunwired

Reputation: 63293

When you want to use an actual single quote in a value, you have to escape it with another quote, so you would need to modify Dad's Stuff to be Dad''s Stuff before it is inserted into the SQLite query. In data values, Android does this for you when you supply your data to the whereArgs array in a insert(), update(), query(), or delete() method, but for a table name you will need to escape this yourself.

Something like String.replace("'","''") should do the trick. Not sure for table names, you may need to pre-wrap the name you pass in single quotes as well, i.e. 'Dad''s Stuff'.

Single quote (i.e. apostrophe) is really the only special case because it is the character that is used to escape all other characters in SQL statements. In other words, I can pass a string in a SQL statement like '(The_St/atement)' safely because the whole thing is in single quotes.

HTH

Upvotes: 0

Related Questions