android - Android SQLite 在带有转义双引号的 TEXT 列中插入 JSON

Question

我在一个 Android 应用程序中使用 SQLite，其中存储了一个记录用户情绪的表。表架构如下所示

CREATE TABLE moods
(
 dow INTEGER,
 tsn INTEGER,
 lato INTEGER,
 agitation TEXT DEFAULT '{}',
 preoccupancy TEXT DEFAULT '{}',
 tensity TEXT DEFAULT '{}',
 taps TEXT DEFAULT '{}'     
);

与 Postgres 不同，SQLite 没有专用的jsonb存储类型。去引用

向后兼容性限制意味着 SQLite 只能存储 NULL、整数、浮点数、文本和 BLOB 值。无法添加第六个“JSON”类型。

SQLite JSON1 扩展文档

在 SQLite 命令行处理器上，我可以使用一个简单的 INSERT 语句来填充这个表，比如这个

INSERT INTO moods (dow,tsn,lato,agitation,preoccupancy,tensity,taps) 
VALUES(1,0,20,'{"A2":1}','{"P4":2}','{"T4":3}','{"M10":4}');

因为它接受字符串的单引号和双引号。

为了在我的 Hybrid Android 应用程序中完成相同的操作，我执行以下操作

String ag = JOString("A" + DBManage.agitation,1);
String po = JOString("P" + (DBManage.preoccupancy + 15),1);
String te = JOString("T"  + DBManage.tensity,1); 

它返回诸如{"P4":2}等之类的字符串。我的下一步如下

ContentValues cv = new ContentValues();
cv.put("dow",0);
cv.put("tsn",1);
cv.put("lato",2);
cv.put("agitation",ag);
cv.put("preoccupancy",po);
cv.put("tensity",te);
long rowid = db.insert("moods",null,cv);

哪个有效。但是，在检查存储的值后，我发现实际上输入的是

{"dow":0,"tsn":5,"lato":191,"tensity":"{\"T0\":1}","agitation":" 
{\"A1\":1}","preoccupancy":"{\"P15\":1}","taps":"{}"}]

除非我在这里误解了某些东西，否则底层ContentValues.put或SQLiteDatabase.insert实现已经自行转义了引用的字符串。也许这是意料之中的，因为沿着这SQLiteDatabase.insert条路线走的好处ContentValues是可以防止 SQL 注入攻击。然而，在目前的情况下，它是一种障碍，而不是一种帮助。

除了通过执行原始 SQL 之外，execSQL还有另一种方法可以将 JSON 导入 SQLite 数据库表吗？

---

我应该补充一点，只需用单引号替换双引号

String ag = JOString("A" + DBManage.agitation,1);
ag = ag.replaceAll("\"","'");

将无法工作，因为随后尝试从表中提取 JSON

SELECT ifnull((select json_extract(preoccupancy,'$.P4') from moods where dow = '1' AND tsn = '0'),0);

将导致SQLite JSON1 扩展发出格式错误的JSON错误。

标签： androidjsonsqlitesqlite-json1