SQLite unrecognized token exception in Insert

Question

I get unrecognized token error when I try to include the Api_key column and its value in my insert query, otherwise without it, it works fine.

here's the code:

public void InsertResult(String apikey,String auditid,String crit_id, int current_chap)
{
    String s="INSERT OR IGNORE INTO Results(AuditID,CriteriaID,ChapterID,Api_key) VALUES("+auditid+","+crit_id+","+current_chap+","+apikey+")";

    sp.execSQL(s);
}

Here's my logcat:

10-11 22:45:09.655: E/AndroidRuntime(8124): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.oxtro.trustea/com.oxtro.trustea.ChapterActivity}: android.database.sqlite.SQLiteException: unrecognized token: "3249f6dc" (code 1): , while compiling: INSERT OR IGNORE INTO Results(AuditID,CriteriaID,ChapterID,Api_key) VALUES(1,13,13,3249f6dc-c3ca-4c8d-a4de-df1834c579c4)

Answer 1

You should put tick marks around your non-numeric strings.

String s="INSERT OR IGNORE INTO Results(AuditID,CriteriaID,ChapterID,Api_key) VALUES("+auditid+","+crit_id+","+current_chap+",`"+apikey+"`)";

Note the ` marks around "apikey"

SQLite was seeing the - and getting confused why it wasn't in a string.

Answer 2

Don't ever hardcode strings in your SQL statements.

User inputted strings create a SQL injection vulnerability.

Arbitrary strings needs to be parsed fro special characters.

SQL APIs normally provide bind methods to allow you to safelly insert arbitrary data in you database.

In Android SQLite, for INSERT you may use:

public void InsertResult(String apikey,String auditid,String crit_id, int current_chap)
{
    ContentValues cv=new ContentValues();
    cv.put("AuditID", auditid);
    cv.put("CriteriaID", crit_id);
    cv.put("ChapterID", current_chap);
    cv.put("Api_key", apikey);
    sp.insert("Results", null, cv);
}