ios - 如何从 sqlite 查询中的 NSString 中删除 '

Question

试图从这句话中删除“嘿，我明天会来”。也尝试过 stringByReplacingOccurrencesOfString 但它不起作用。请帮助找到解决方案，因为我已经在这上面浪费了半天多的时间。下面是查询和我尝试的结果加上输出，请检查

 NSMutableString *msql = [[NSMutableString alloc] initWithFormat:@"insert into %@ ", tableName];
        for (int i=0; i<[arrArguments count]; i++){
            NSMutableArray *marrArgum = [arrArguments objectAtIndex:i];

            if (i==0){
[msql appendFormat:@"SELECT '%@' AS 'id', '%@' AS 'UserID', '%@' AS 'MessageID', '%@' AS 'ActualMessage', '\%@\' AS 'MessageType', '%@' AS 'MessageIsOld', '%@' AS 'MessageSentBySelf', '%@' AS 'Timestamp', '%@' AS 'MessageRead', '%@' AS 'MessageToUser' ", [marrArgum objectAtIndex:0], [marrArgum objectAtIndex:1], [marrArgum objectAtIndex:2], [marrArgum objectAtIndex:3], [marrArgum objectAtIndex:4], [marrArgum objectAtIndex:5], [marrArgum objectAtIndex:6], [marrArgum objectAtIndex:7], [marrArgum objectAtIndex:8], [marrArgum objectAtIndex:9]];

}
            else{
                [msql appendFormat:@"UNION ALL SELECT '%@', '%@', '%@', '\%@\', '%@', '%@', '%@', '%@', '%@', '%@' ", [marrArgum objectAtIndex:0], [marrArgum objectAtIndex:1], [marrArgum objectAtIndex:2], [marrArgum objectAtIndex:3], [marrArgum objectAtIndex:4], [marrArgum objectAtIndex:5], [marrArgum objectAtIndex:6], [marrArgum objectAtIndex:7], [marrArgum objectAtIndex:8], [marrArgum objectAtIndex:9]];
            }

NSString *sql = [NSString stringWithFormat: @"%@", msql];
DBLog(@"INSERT SQL QUERY: %@", sql);
        METHOD_CALLER;
        int intQcheck = sqlite3_exec(sqliteDB, [sql UTF8String], NULL, NULL, &err);
        DBLog(@"intQcheck = %d", intQcheck);
        if (intQcheck!= SQLITE_OK) {
            if ([self targetAppIsUsingDevProfile])
                NSAssert(0, @"Error in Insertion Query(intQcheck=%d): %@", intQcheck, strMsql);
            success = NO;
        }

我尝试的是:

NSString *strMsql = [sql stringByReplacingOccurrencesOfString:@"'" withString:@"\'"];

但它不起作用。输出仍然与之前相同，即:

i’ll see u you

请指导。谢谢。

Answer 1

为什么应优先考虑准备好的陈述

当您自己将 SQL 查询创建为字符串时，它们几乎总是包含用户输入的一部分。攻击者可以利用这一点，例如，使用 ' 巧妙地更改查询的语义，从而获得对数据的未授权访问或破坏数据。

这称为 SQL 注入(inject)，是最严重的安全风险之一，请参见此处: https://www.owasp.org/images/7/72/OWASP_Top_10-2017_%28en%29.pdf.pdf

防御

The use of prepared statements with variable binding (aka parameterized queries) is how all developers should first be taught how to write database queries.

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28with_Parameterized_Queries.29

如何在 SQLite 和 iOS 中使用准备好的语句

有关准备好的语句，请参阅 https://www.sqlite.org/c3ref/stmt.html .

基本步骤是:

• 创建准备好的语句
• 将值绑定(bind)到参数
• 运行 SQL
• 销毁对象以避免资源泄漏

这是一个简单的例子:

-(BOOL)insertValue:(NSString *)value intoDB:(sqlite3 *)db {
    sqlite3_stmt *stmt = NULL;
    NSString *insertStmt = @"insert into SomeTable (sometext) values (?)";
    //create the prepared statement
    if(SQLITE_OK == sqlite3_prepare_v2(db, insertStmt.UTF8String, -1, &stmt, NULL)) {
        //bind values to parameters
        if(SQLITE_OK == sqlite3_bind_text(stmt, 1, value.UTF8String, -1, SQLITE_STATIC)) {
            //run the SQL
            if(SQLITE_DONE != sqlite3_step(stmt)) {
                return NO;
            }
        }
        else {
            return NO;
        }
        //destroy the object to avoid resource leaks
        sqlite3_finalize(stmt);
    }
    else {
        return NO;
    }
    return YES;
}

它将被调用为您的示例字符串i'll see u you，如下所示:

sqlite3 *db;
if(SQLITE_OK == sqlite3_open(dbURL.absoluteString.UTF8String, &db)) {
    NSString *someValue = @"i'll see u you";
    if (![self insertValue:someValue intoDB:db]) {
        NSLog(@"insert failed: '%s'", sqlite3_errmsg(db));
    }
    sqlite3_close(db);
}
else {
    NSLog(@"open db failed: '%s'", sqlite3_errmsg(db));
}

所以这里使用 ' 例如来自用户输入不会造成任何损害，因为我们使用准备好的语句。

演示

如果您查看数据库实用程序中的数据，则在此插入语句之后它看起来像这样: