9

I'm trying to insert into a table with this field TITLE varchar(200) NOT NULL the next sentence:

INSERT OR IGNORE INTO EXAMPLETABLE VALUES ("1-Preludi \"Obrint Pas 2011\"", ...

The problem is that SQLite missunderstand the \" escape caracter , and i don't know how to put it to resolve the problem (Maybe a character code?) in my Android program.

A.Quiroga
  • 5,704
  • 6
  • 37
  • 58

5 Answers5

17

Just working with sqlite and direct sql insert commands from the command line, I find that double quoting escapes itself. With your example

INSERT OR IGNORE INTO EXAMPLETABLE VALUES ("1-Preludi ""Obrint Pas 2011""", ...
Nicholas Adams
  • 435
  • 6
  • 9
3

To insert values into a database you should always use SQLiteStatement (HowTo) as they escape your values and prevent your app from being targeted with SQL-Injections.

Also, if you're inserting multiple values (say in a loop), executing a precompiled statement with different bound values is much faster than a normal insert-statement.

Community
  • 1
  • 1
Lukas Knuth
  • 25,449
  • 15
  • 83
  • 111
  • 1
    Actually, you should use the SQLite interface supplied especially by Android and pass the values as ContentValues instead, using (for instance) the SQLiteDatabase#insert(String, String, ContentValues) method. – Jens Oct 04 '11 at 11:36
  • 1
    @Jens You're right, in Android the insert-params are escaped. But most of the people only supply the raw-query and leave the params with `Null`. That's why I suggest a normal PreparedStatement. – Lukas Knuth Oct 04 '11 at 11:39
  • 1
    Ah, you are referring to using SQLiteStatement:s, not actual standard Java PreparedStatements. – Jens Oct 04 '11 at 11:44
  • 1
    @Jens yep, I was going to, but I got the wrong class from the docs. My mistake. – Lukas Knuth Oct 04 '11 at 11:48
2

Try something similar:

ContentValues values = new ContentValues();
values.put("YOURCOLUMNNAME", "1-Preludi \"Obrint Pas 2011\"");
// ... other stuff you want to insert ...
SQLiteDatabase db = ... ; // Usually obtained using a SQLiteOpenHelper
long id = db.insertWithOnConflict("EXAMPLETABLE", BaseColumns._ID, values, SQLiteDatabase.CONFLICT_IGNORE);
Jens
  • 16,853
  • 4
  • 55
  • 52
2

Use This To Insert Data with any special charecter

ContentValues initialValues = new ContentValues();
initialValues.put("Id", id);
initialValues.put("Name", Name);
// ...
myDataBase.insert("EXAMPLETABLE", null, initialValues);
Lukas Knuth
  • 25,449
  • 15
  • 83
  • 111
Sunil_Suthar
  • 1,094
  • 1
  • 10
  • 20
1

Try this:

INSERT OR IGNORE INTO EXAMPLETABLE VALUES ('1-Preludi "Obrint Pas 2011"', ...
Michell Bak
  • 13,182
  • 11
  • 64
  • 121
  • 1
    +1 I've tried but i have problems with ' :D , then this solution doesn't run but its easy one if you only have problems with " . – A.Quiroga Oct 04 '11 at 12:12
  • If your inputs have mixed single/double quotes, this will break. – alex Jul 04 '18 at 22:39