特殊字符JSON iOS SQLite [英] Special Characters JSON iOS SQLite

问题描述

我有一个问题。
我从JSON获取信息并且他返回无效字符。
JSON给我这个：27 /当我需要这个：27。我理解这是一个特殊字符的编码但是当我使用NSString上的值在SQLite表中创建一个Insert时，我不能使用27 /因为插入格式是这样的：INSERT INTO FORMATOS（ID，NOMBRE）VALUES （17，27）。

I have one question. I'm getting information from JSON and he returns me invalid character. JSON give me this: "27/"" when I need this: 27". I understand this is a encode for special characters but when I use the value on NSString to make an Insert in a SQLite table, I can't use 27/" cause the insert format is this: INSERT INTO FORMATOS (ID, NOMBRE) VALUES ("17", "27"").

我需要在SQLlite中正确插入信息的方法是什么？

What method I need to Insert information correctly in the SQLlite?

for (int i = 0; i<idFormato.count; i++) {
            NSString *idStr = [idFormato objectAtIndex:i];
            NSString *nameStr = [nameFormato objectAtIndex:i];

            insertSQL = [NSString stringWithFormat:@"INSERT INTO FORMATOS (ID, NOMBRE) VALUES (\"%@\", \"%@\")", idStr, nameStr];

            //Char constant with the query encoded un UTF
            const char *insert_stmt = [insertSQL UTF8String];
            //Execute query
            sqlite3_prepare_v2(dieneDB, insert_stmt, -1, &statement, NULL);

            //Check if Statment is dne correctly
            if(sqlite3_step(statement) == SQLITE_DONE){
                NSLog(@"Guardado Formatos correctamente");
            }

JSON：

[

    {"ID_FORMATO_INT":"17","NOMBRE_FORMATO_STR":"2,5\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:17:55","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"18","NOMBRE_FORMATO_STR":"4\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:18:20","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"19","NOMBRE_FORMATO_STR":"4,7\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:20:07","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"20","NOMBRE_FORMATO_STR":"5,5\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:20:15","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"21","NOMBRE_FORMATO_STR":"9,7\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:20:42","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"22","NOMBRE_FORMATO_STR":"7,9\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:21:04","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"23","NOMBRE_FORMATO_STR":"11\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:22:40","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"24","NOMBRE_FORMATO_STR":"13\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:22:44","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"25","NOMBRE_FORMATO_STR":"15\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:22:49","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"26","NOMBRE_FORMATO_STR":"21,5\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:23:11","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null},{"ID_FORMATO_INT":"27","NOMBRE_FORMATO_STR":"27\"","ID_USUARIO_ALTA_INT":"3","FECHA_ALTA_FORMATO_DAT":"2014-09-18 07:23:14","ID_USUARIO_MOD_INT":null,"FECHA_MOD_FORMATO_DAT":null}

]

推荐答案

问题在于你用 stringWithFormat 构建SQL。这是一种易受此类问题影响的做法。相反，在SQL中使用？占位符，然后使用 sqlite3_bind_text 将值绑定到？ 占位符。请参阅 sqlite3_bind_text（）帮助有关详细信息。

The issue is that you're building your SQL withstringWithFormat. That is a practice that is susceptible to this sort of problem. Instead, use ? placeholders in your SQL and then use sqlite3_bind_text to bind values to the ? placeholders. See the sqlite3_bind_text() help for more information.

例如，您可能：

const char *insert_stmt = "INSERT INTO FORMATOS (ID, NOMBRE) VALUES (?, ?)";

if (sqlite3_prepare_v2(dieneDB, insert_stmt, -1, &statement, NULL) != SQLITE_OK) {  // prepare SQL
    NSLog(@"prepare error: %s", sqlite3_errmsg(dieneDB));
} else {
    if (sqlite3_bind_text(statement, 1, idStr, -1, NULL) != SQLITE_OK) {            // bind 1
        NSLog(@"bind idStr error: %s", sqlite3_errmsg(dieneDB));
    } else if (sqlite3_bind_text(statement, 2, nameStr, -1, NULL) != SQLITE_OK) {   // bind 2
        NSLog(@"bind nameStr error: %s", sqlite3_errmsg(dieneDB));
    } else if (sqlite3_step(statement) != SQLITE_DONE) {                            // perform SQL
        NSLog(@"step error: %s", sqlite3_errmsg(dieneDB));
    } else {
        NSLog(@"Guardado Formatos correctamente");
    }

    sqlite3_finalize(statement);
}

我刚输入此内容，请原谅任何拼写错误，但希望它说明这个想法。

I just typed this in, so please forgive any typos, but hopefully it illustrates the idea.

注意，我还（a）检查所有这些退货代码; （b）记录错误（如有）; （c）完成后完成陈述。

Note, I also (a) check all of these return codes; (b) log the error if any; and (c) finalize the statement when done.

这篇关于特殊字符JSON iOS SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！