storing string with ' in sqlite

Question

i have a problem with sqlite. in my app i get voice recognition to edit text and then storing this string in sqlite. its work very good, the problem is when i try to say word like "don't" and try to save it to sqlite, the app crash becouse its exeption for sqlite. any solution?

this is the on activity result for voice recognition:

protected void onActivityResult(int requestCode, int resultCode, Intent data) {
    super.onActivityResult(requestCode, resultCode, data);
    switch (requestCode) {
    case REQ_CODE_TAG_INPUT: {
        if (resultCode == RESULT_OK && null != data) {
            ArrayList<String> result = data
                    .getStringArrayListExtra(RecognizerIntent.EXTRA_RESULTS);
            if (!result.get(0).equals("delete all")) {
                et1.setText(et1.getText().toString() + " " + result.get(0));
            } else {
                et1.setText("");
            }
        }
        break;
    }

and this is the save method for sqlite:

if (!et1.getText().toString().equals("")
            && !et2.getText().toString().equals("")) {
        tag = et1.getText().toString();
        des = et2.getText().toString();
        Notes.getInstance().getmNotes()
                .add(new MyNote(tag, des, date, 1, id));
        MyOH oh = new MyOH(getApplicationContext());
        mDb = oh.getWritableDatabase();
        if (MainActivity.state == 1) {
            mDb.execSQL("UPDATE note SET tag='" + tag + "',des='" + des
                    + "',time='" + date + "',noteid=" + id
                    + " WHERE noteid = " + id);
        } else {
            mDb.execSQL("INSERT INTO note VALUES ('" + tag + "','" + des
                    + "','" + date + "'," + id + ")");
        }

this is the exeption:

Caused by: android.database.sqlite.SQLiteException: near "t": syntax error (code 1): , while compiling: INSERT INTO note VALUES ('No Tag','don't','28/11/2014',-133663026

thanks for helping!!

http://stackoverflow.com/questions/12615113/how-to-escape-special-characters-like-in-sqlite-in-android [Answered here][1] — user2570812, Nov 28 '14 at 20:24

Phantômaxx · Answer 1 · 2014-11-29T19:48:11.997

2

You could simply double the '.
I.e.: don''t.
Like so: des = des.replace("'", "''");.

Or, better, you could use bound parameters in your SQL commands.
Like so:

if (MainActivity.state == 1)
{
    mDb.execSQL("UPDATE note SET tag = ?, des = ?, time = ?, noteid = ? WHERE noteid = ?", new String[]{tag, des, date, id, id});
}
else
{
    mDb.execSQL("INSERT INTO note (tag, des, time, noteid) VALUES (?, ?, ?, ?)", new String[]{tag, des, date, id});
}

Bound parameters prevent your code from SQL injection and do automatic type casting for you.
Not only: bound parameters will also prevent all those string concatenations, which are a performance sucker.

edited Nov 29 '14 at 19:48

answered Nov 28 '14 at 20:29

Phantômaxx

37,901
21
84
115

1

Bound parameters all the way. Also that much string concatenation is a dog on performance. – Chris Thompson Nov 28 '14 at 20:31
True. String concatenation is a snail. Editing my answer to add your observation. – Phantômaxx Nov 28 '14 at 20:32
ohhh you save me with the idea with bound parameters!! thanks a lot men!! (the replace didn't work for me) – AviRosenblum Nov 28 '14 at 20:38
So, you can accept my answer, in order to remove your question from the Unanswered Questions Queue. Anyway, glad I could help you. – Phantômaxx Nov 28 '14 at 21:57

storing string with ' in sqlite

1 Answers1