How to escape the apostrophe in Java?

Question

I am getting the following error message:

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.kite.aomp/com.kite.aomp.MainActivity}: android.database.sqlite.SQLiteException: near "s": syntax error (code 1): , while compiling: INSERT INTO DBExample VALUES('(四月は君の嘘) Shigatsu wa Kimi no Uso OST Collection - Watashi no Uso~PianoSolo','~Alex's ShigatsuOST~','1');

My code is:

    String CREATE_USER_TABLE = "CREATE TABLE DBExample " + "(" + "Title" + " VARCHAR," 
        + "Artist" + " VARCHAR," + "Numb" + " VARCHAR" + ");";

    ContentResolver musicResolver = getContentResolver();
    Uri musicUri = android.provider.MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;
    String selection = MediaStore.Audio.Media.IS_MUSIC + "!= 0";
    String sortOrder = MediaStore.Audio.Media.TITLE + " ASC";
    Cursor musicCursor = musicResolver.query(musicUri, null, selection, null, sortOrder);

    SQLiteDatabase db = openOrCreateDatabase(DBNAME, MODE_PRIVATE, null);
    db.execSQL(CREATE_USER_TABLE);
    if (musicCursor != null && musicCursor.moveToFirst())
    {
        int titleColumn = musicCursor.getColumnIndex
        (android.provider.MediaStore.Audio.Media.TITLE);
        int artistColumn = musicCursor.getColumnIndex
        (android.provider.MediaStore.Audio.Media.ARTIST);

        do {

            String thisTitle = musicCursor.getString(titleColumn);
            String thisArtist = musicCursor.getString(artistColumn);

            String soj = "INSERT INTO DBExample VALUES" + "('"
                + thisTitle + "','" + thisArtist + "'," + "'1'" + ");";

            db.execSQL(soj);

        }
        while (musicCursor.moveToNext());
    }
    db.close();

Could you please help me to find a solution for the above problem?

user_10498828 · Answer

Your problem is exactly the reason why you should not use this method of inserting data.
The recommended way is the method insert() like this:

ContentValues cv = new ContentValues(); 
cv.put("Title", thisTitle);
cv.put("Artist", thisArtist);
cv.put("Numb", "1");
db.insert("DBExample", null, cv);

By using a ContentValues object you don't have to worry about the data type of the values and escaping special characters like single quotes.
But if you insist on using your method, you must know that you take the risk of sql injection.

How to escape the apostrophe in Java?

1 Answers