Replacing single quotes with double quotes in Android database insertion statement

Question

Suppose I have an execquery statement like this:

db1.execSQL("insert into "+TABLE_NAME+" values('"name"')");

where name is a string variable which contains an apostrophe. For example:

name = "tom's database";

In this case, I get an SQLITEexception near this statement. I am certain that this is because of that single quote.

How to modify this such that the statement does not cause a crash and the name get stored in the db with the single quote intact?

I read online that every such single quote has to be prefixed by another single quote.

Can someone provide the code for the same?

`name = name.replace("'", "''");` Note: it's 1 apostropphe replaced by 2 apostrophes. — Phantômaxx, Mar 20 '14 at 11:56
Check this answer http://stackoverflow.com/a/20608753/3330969 — Lucifer, Mar 20 '14 at 12:05
possible duplicate of [How to escape unsupported character in SQLite on Android?](http://stackoverflow.com/questions/5432063/how-to-escape-unsupported-character-in-sqlite-on-android) — InnocentKiller, Mar 20 '14 at 12:07
Don't do such things manually, let Java handle it for you. Use [PreperedStatement](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) — Pshemo, Mar 20 '14 at 12:18

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

Duplicate question. Check How to escape unsupported character in SQLite on Android?

Use

String escapedName = DatabaseUtils.sqlEscapeString(name);
db1.execSQL("insert into "+TABLE_NAME+" values('" + escapedName + "')");

See http://developer.android.com/reference/android/database/DatabaseUtils.html#sqlEscapeString%28java.lang.String%29

edited May 23 '17 at 12:11

Community

1
1

answered Mar 20 '14 at 11:55

Lars Blumberg

19,326
11
90
127

score 3 · Answer 2 · answered Mar 20 '14 at 12:05

3

Escaping the special character in the string literal works but generally it's an error prone approach. It's better to use ? placeholder and bind arguments, like this:

db1.execSQL("INSERT INTO " + TABLE_NAME + " VALUES (?)", new String[] { name });

or use insert() with ContentValues which does essentially the same.

answered Mar 20 '14 at 12:05

laalto

150,114
66
286
303

@Iaalto: your knowledge of SQLite always surprises me for its depth. +1 – Phantômaxx Mar 20 '14 at 12:08

score 2 · Answer 3 · answered Mar 20 '14 at 12:02

You forgot:

To double the string apostrophes (since a apostrophes are the SQL string delimiters).
To add the + in the INSERT string to properly add the variable.

So, I'd change the above INSERT statement to:

db1.execSQL("INSERT INTO " + TABLE_NAME + " VALUES ('" + name.replace("'", "''") + "')");

score 2 · Answer 4 · edited Mar 20 '14 at 12:08

2

You can use "PrepareStatement" to avoid problems

SQLiteStatement p = db1.compileStatement("insert into "+TABLE_NAME+" values(?)");
p.bindString(1, name);
p.execute();

Other form:

ContentValues values = new ContentValues();
values.put("name", name);
db1.insert(TABLE_NAME, null, values);

edited Mar 20 '14 at 12:08

InnocentKiller

5,234
7
36
84

answered Mar 20 '14 at 12:06

MiguelAngel_LV

1,200
5
16

Replacing single quotes with double quotes in Android database insertion statement

4 Answers4