Syntax Error in SQLite (Insert Statement)

Question

I am using the Android's SQLite storage option to store some references of a book. the table is created, but it gives an error when Insert statement executes.

Here's the query string:

writeableDatabase.execSQL("INSERT INTO " + BookSave.TABLE_NAME +
                                " VALUES( " + book.getTitle().toString() +
                                "," + book.getAuthor().toString() + ","
                                + book.getPathOfCover().toString() + " );");

Here's the logcat:

near "Devices": syntax error
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime: FATAL EXCEPTION: Thread-2698
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime: Process: dreamnyc.myapplication, PID: 23973
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime: android.database.sqlite.SQLiteException: near "Devices": syntax error (code 1): , while compiling: INSERT INTO book VALUES( Electronic Devices & Circuits,Jacob Millman & Christos C. Halkias,/storage/emulated/0/Android/data/dreamnyc.myapplication/files/MillmanHalkias-ElectronicDevicesCircuits/OEBPS/images/leaf-image0000.jpg );
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:891)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:502)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at dreamnyc.myapplication.MainActivity$2.run(MainActivity.java:189)
02-27 19:21:04.555 23973-24058/dreamnyc.myapplication E/AndroidRuntime:     at java.lang.Thread.run(Thread.java:818)

To avoid SQL injection use parameter binding. Anyway your query does not have `'` around string literal. `INSERT INTO book VALUES( Electronic Devices & Circuits,Jacob Millman & Christos C. Halkias,/storage/emulated/0/Android/data/dreamnyc.myapplication/files/MillmanHalkias-ElectronicDevicesCircuits/OEBPS/images/leaf-image0000.jpg ` — Lukasz Szozda, Feb 27 '16 at 13:55
**[How do I use prepared statements in SQlite in Android?](http://stackoverflow.com/questions/433392/how-do-i-use-prepared-statements-in-sqlite-in-android)** — Lukasz Szozda, Feb 27 '16 at 13:57
Bound parameters are markers (**?**), replaced by a string array values. — Phantômaxx, Feb 27 '16 at 13:57
Don't type raw sql strings with string concatenation. Use the `insert` method like so. http://stackoverflow.com/a/6251275/2308683 — OneCricketeer, Feb 27 '16 at 13:58
use this: writeableDatabase.execSQL("INSERT INTO " + BookSave.TABLE_NAME + " VALUES( '" + book.getTitle().toString() + "','" + book.getAuthor().toString() + "','" + book.getPathOfCover().toString() + "' );"); — saber safavi, Feb 27 '16 at 14:01

score 1 · Accepted Answer · answered Feb 27 '16 at 14:02

The problem is you need quotes around the values if they are of type varchar:

writeableDatabase.execSQL("INSERT INTO " + BookSave.TABLE_NAME +
                                " VALUES( '" + book.getTitle().toString() +"'," 
                                +"'"+ book.getAuthor().toString() + "',"
                                +"'"+ book.getPathOfCover().toString() + "');");

The real problem is you should be using parametrized queries to avoid these type of errors but also to prevent sql injections.

for example:

ContentValues values = new ContentValues();
values.put(KEY_TITLE, book.getTitle().toString());
values.put(KEY_AUTHOR, book.getAuthor().toString());
values.put(KEY_PATH_COVER, book.getPathOfCover().toString());
writeableDatabase.insert(TABLE_NAME, null, values);

KEY_ are the column name as strings

Syntax Error in SQLite (Insert Statement)

1 Answers1