CODEWITHSUNDEEP
javaandroidsqlite
SoulRayder
SoulRayder

Reputation: 5166

Replacing single quotes with double quotes in Android database insertion statement

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?

Upvotes: 0

Views: 1647

Answers (4)

MiguelAngel_LV
MiguelAngel_LV

Reputation: 1258

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);

Upvotes: 2

laalto
laalto

Reputation: 152807

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.

Upvotes: 3

Phantômaxx
Phantômaxx

Reputation: 38098

You forgot:

  1. To double the string apostrophes (since a apostrophes are the SQL string delimiters).
  2. 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("'", "''") + "')");

Upvotes: 2

Lars Blumberg
Lars Blumberg

Reputation: 21361

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

Upvotes: 3

Related Questions