带撇号的iPhone SQLite命令 [英] iPhone SQLite commands with apostrophes

问题描述

我正在为iPhone编写一个与SQLite数据库通信的应用程序，但遇到一个小问题.每当我尝试基于包含撇号的条件查询信息时，都不会返回任何结果....即使存在与所请求条件匹配的结果.让我给一些细节...

I'm writing an application for the iPhone that communicates with a SQLite database but I'm running into a small problem. Whenever I try to query information based on a condition that contains an apostrophe, no results are returned.... even if a result that matches the requested condition exists. Let me give some specifics...

行-第1列-第2 ---------

Row--Column1--Column2---------

1. 测试数据-001
2. 用户数据-002

Objective-C代码

---

//Create the sql statement
sqlite3_stmt *sqlStatement;

//Create the name of the category that will be passed in
NSString *categoryName = @"User's Data";

//Create the rest of the SQL query
NSString *sqlQuery = "SELECT * FROM theTableName WHERE Column1 = ?";

//If there are no errors in the SQL query
if (sqlite3_prepare_v2(theDatabase, sqlQuery, -1, &sqlStatement, nil) == SQLITE_OK)
{
    //Bind the category name to the sql statement
    sqlite3_bind_text(sqlStatement, 1, [categoryName UTF8String], -1, SQLITE_TRANSIENT);

    //While there are rows being returned
    while (sqlite3_step(sqlStatement) == SQLITE_ROW)
    {
        //Retrieve row data
    }
}
else
{
    //Save error message to the application log and terminate the app
    NSAssert1(0,@"Error: Failed to prepare the SQL statement with message '%s'.", sqlite3_errmsg(database));
}
//Reset the sql statement
sqlite3_reset(sqlStatement);

我对目标C还是陌生的，所以在编写此代码时，我的第一个念头是清理用户输入.但是，在进行了一些研究之后，我了解到sqlite3_bind调用可以为您进行必要的卫生处理.但是，每当代码运行时，就不会跳过while循环，因为没有行返回.它应该从数据库表返回第二行.如果我将完全相同的SQL查询复制/粘贴到SQL管理程序中(我使用 SQLite Manager )(当然还有必要的查询环境)，它会返回正确的数据.

I'm semi-new to objective C, so my first thought when writing this code was to sanitize the user inputs. But after doing some research, I read that the sqlite3_bind calls do the necessary sanitation for you. But whenever the code runs, the while loop is skipped right over because there are no rows being returned. It should return the second row from the database table. If I copy/paste the exact same SQL query into a SQL managing program (I use SQLite Manager) (and with the necessary query sanitation of course), it returns the correct data.

我花了很长时间尝试自己调试，甚至花费大量时间尝试在线搜索正在解释和解决的类似问题，但无济于事.到目前为止，我只是禁用了用户在iPhone的虚拟键盘上键入撇号的功能.但这是我希望在最终产品中包含的功能.有人可以给我提供任何有用的提示吗?任何帮助将不胜感激.

I've spent a long time trying to debug this myself and even a greater amount of time trying to search online for a similar problem being explained and resolved, but to no avail. As of now, I just disabled the user's ability to key in an apostrophe on the iPhone's virtual keyboard. But this is a feature I'd love to include in my finished product. Can anyone here offer me any helpful tips? Any kind of help would be greatly appreciated.

推荐答案

对于sqlite，您的请求将是(正如您看到的那样，错误地突出显示了它):

For sqlite your request will be (as you can see it is even wrong highlighted):

SELECT * FROM theTableName WHERE Column1 = User's data

它将等待结束的'符号

您应该以以下方式回显'符号:

You should echo ' symbol, for example in following way:

NSString *sqlQuery = [NSString stringWithFormat:@"SELECT * FROM tableName WHERE Column1=\"%@\"", categoryName];

在这种情况下，查询将是

In this case query will be

select * from theTableName where column1="User's data"

那是完全合法的查询.

在这种情况下，您不再需要绑定，最终代码将如下所示:

In this case you don't need binding any more and final code will look like:

if (sqlite3_prepare_v2(database, [sqlQuery UTF8String], -1, &sqlStatement, nil) == SQLITE_OK)
{
    //While there are rows being returned
    while (sqlite3_step(sqlStatement) == SQLITE_ROW)
    {
       //Retrieve row data
    }
}
else
{
   //Save error message to the application log and terminate the app
    NSAssert1(0,@"Error: Failed to prepare the SQL statement with message '%s'.", sqlite3_errmsg(database));
}

这篇关于带撇号的iPhone SQLite命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！