Objective-C sqlite 在 LIKE 子句中添加查询参数 [英] Objective-C sqlite adding query parameter inside of LIKE clause
问题描述
我通过 C API 在 Objective-C 中使用 sqlite
.我的应用程序的预期功能之一是,用户可以搜索人名,并且对于他们输入的每个新字符,都会执行使用 LIKE
的 SQL 查询以查找所有姓名符合匹配条件的人.我遇到的问题是在 LIKE 中参数化匹配,而没有按字面解释问号.也就是说,我相信该应用目前正在寻找其中包含 ?
的人名(即没有人).
I am using sqlite
in Objective-C via the C API. One of the intended features of my app is that the user can search for a person name and with each new character they enter, an SQL query using LIKE
is executed to find all people whose names qualify as a match. The issue I am running into is parameterizing the match inside the LIKE, without the question mark being interpreted literally. That is, I believe the app at present is looking for people's names that include ?
in them (which is nobody).
我的代码如下所示:
const char *sql = "SELECT rowid, name, email FROM person WHERE name LIKE '%?%'";
sqlite3_stmt *sqlStatement;
if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK) {
NSLog(@"Problem preparing getFilteredPeopleStubs statement.");
}
if(sqlite3_bind_text(sqlStatement, 1, [searchText UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK){
NSLog(@"Problem binding search text param.");
}
基本上,searchText
是我想要名字的来源,但目前我相信它只是在搜索名字包含的人?
,因为 '%?%'
中的单引号.是否有解决此问题的方法仍然可以让我使用参数化查询(防止 SQL 注入)并实现我所追求的目标?
Basically, searchText
is where I want the name to be coming from, but at the moment I believe it is just searching for people whose name contains ?
, because of the single quotations in '%?%'
. Is there a solution to this problem that still lets me use a parameterized query (protection against SQL injection) and achieves what I am after?
推荐答案
将 %
字符放入 searchText
中,就像(原谅双关语)这样:
Put the %
characters into searchText
, like (excuse the pun) this:
const char *sql = "SELECT rowid, name, email FROM person WHERE name LIKE ?";
sqlite3_stmt *sqlStatement;
if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK) {
NSLog(@"Problem preparing getFilteredPeopleStubs statement.");
}
NSString *bindParam = [NSString stringWithFormat:@"%%%@%%", searchText];
if(sqlite3_bind_text(sqlStatement, 1, [bindParam UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK){
NSLog(@"Problem binding search text param.");
}
这篇关于Objective-C sqlite 在 LIKE 子句中添加查询参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!