从主Activity跳转至其他页面时APP崩溃的SQLite问题排查

主Activity包含一个可添加不同订单的ArrayList，点击查看订单时APP崩溃，报错信息如下：

E/SQLiteLog: (1) unrecognized token: "4LIMIT" in "SELECT * FROM t_comanda WHERE id = 4LIMIT 1"
D/AndroidRuntime: Shutting down VM
E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.example.storder20, PID: 8462
    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.storder20/com.example.storder20.VeureActivity}: android.database.sqlite.SQLiteException: unrecognized token: "4LIMIT" (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM t_comanda WHERE id = 4LIMIT 1
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3449)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3601)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2066)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:223)
        at android.app.ActivityThread.main(ActivityThread.java:7656)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)

相关SQLite代码如下：

public Comanda veureComanda (int id){
        DbHelper dbHelper = new DbHelper(context);
        SQLiteDatabase db = dbHelper.getWritableDatabase();

    Comanda comanda = null;
    Cursor cursorComanda = null;

    cursorComanda = db.rawQuery("SELECT * FROM " + TABLE_COMANDA + " WHERE id = " + id + "LIMIT 1", null);

    if (cursorComanda.moveToFirst()){
        comanda = new Comanda();
        comanda.setId(cursorComanda.getInt(0));
        comanda.setNom(cursorComanda.getString(1));
        comanda.setComanda(cursorComanda.getString(2));
        comanda.setUbicació(cursorComanda.getString(3));
    }

    cursorComanda.close();

    return comanda;
}

报错核心是SQL语句拼接时id值和LIMIT关键字之间缺少空格，导致生成的SQL语句出现4LIMIT这种无效语法，SQLite无法解析。

修复方式1：补全空格 #

修改SQL语句的拼接部分，在id和LIMIT之间添加空格：

cursorComanda = db.rawQuery("SELECT * FROM " + TABLE_COMANDA + " WHERE id = " + id + " LIMIT 1", null);

修复方式2：使用参数化查询（推荐） #

参数化查询能避免SQL注入风险，同时彻底杜绝字符串拼接带来的语法错误：

cursorComanda = db.rawQuery("SELECT * FROM " + TABLE_COMANDA + " WHERE id = ? LIMIT 1", new String[]{String.valueOf(id)});

内容的提问来源于stack exchange，提问作者Agustí Prat