安卓SQLite INSERT

Question

我正在使用SQLite数据库上的execSQL。 SQL INSERT字符串是

INSERT INTO Tasks 
(_id, Aircraft, Station, Discrepancy,DateCreated, CreatedBy, Status, DateClosed, ClosedBy, ArrivalFlightID, RecordChangedByUI)  
 VALUES 
('271104','   ','ORD','Critical Flight (0496/28)','9/4/2011 6:57:00 PM','SYSTEM','NEW','','null','0','N')

表格是...

"create table Tasks 
(_id integer primary key, "
+ "Aircraft text null, Station text null, Discrepancy text null, DateCreated text null, CreatedBy text null, Status text null, DateClosed text, ClosedBy text null, ArrivalFlightID text null, RecordChangedByUI text null);";

它抛出了一个异常 "Empty bindArgs"

有人可以告诉我我错在哪里吗？

- user533844

---

请发布包含您的rawQuery()调用的代码。 - SBerg413

this.database.execSQL(sql, null); - user533844

String sql = "INSERT INTO Tasks (_id, Aircraft, Station, Discrepancy,DateCreated, CreatedBy, Status, DateClosed, ClosedBy, ArrivalFlightID, RecordChangedByUI) " VALUES ('" + tasks[i]._id + "','" + tasks[i].Aircraft + "','" + tasks[i].Station + "','" + tasks[i].Discrepancy + "','" + tasks[i].DateCreated + "','" + tasks[i].CreatedBy + "','" + tasks[i].Status + "','" + tasks[i].DateClosed + "','" + tasks[i].ClosedBy + "','" + tasks[i].ArrivalFlightID + "','N')"; this.database.execSQL(sql, null); - user533844

将翻译后的文本放在可读的位置上方！ - Jon Willis

Answer 1

你不能将null作为第二个参数传入。如果你不使用它，只需忽略它，它就能正常工作：

String sql = "INSERT INTO Tasks (_id, Aircraft, Station, Discrepancy,DateCreated, CreatedBy, Status, DateClosed, ClosedBy, ArrivalFlightID, RecordChangedByUI) " VALUES ('" + tasks[i]._id + "','" + tasks[i].Aircraft + "','" + tasks[i].Station + "','" + tasks[i].Discrepancy + "','" + tasks[i].DateCreated + "','" + tasks[i].CreatedBy + "','" + tasks[i].Status + "','" + tasks[i].DateClosed + "','" + tasks[i].ClosedBy + "','" + tasks[i].ArrivalFlightID + "','N')"; 
this.database.execSQL(sql); 

然而，上面的示例存在漏洞 - SQL查询很容易被注入。所有传递到查询中的字符串都应通过DatabaseUtils.sqlEscapeString(task[i].something)进行转义。

- Sebastian Nowak

---

如果 task[i].Station 包含一个单引号怎么办？ - Massimo Fazzolari

所有字符串都应通过DatabaseUtils.sqlEscapeString(task [i] .something)进行转义。我刚刚从作者的评论中复制了SQL查询。我将把它添加到答案中。 - Sebastian Nowak

Answer 2

尝试执行database.insert或insertOrThrow。它要求将每个字段显式添加到ContentValues对象中，但是这样做会更加简洁。

ContentValues insertValues = new ContentValues();
insertValues.put("_id", tasks[i]._id);
... // other fields
long rowId = this.database.insert(DATABASE_TABLE, null, insertValues);

- adamcodes