如何在SQLite插入语句中正确转义单引号-iOS [英] How to properly escape single quotes in SQLite insert statement - iOS

问题描述

我在我的sqlite表中有一个插入语句，其中的值中包含撇号.

I have an insert statement for my sqlite table that contains apostrophes in the values.

NSString *sqlInsert = [NSString stringWithFormat:@"insert into myTable (_id, name, area, title, description, url) VALUES (%i, '%@', '%@', '%@', '%@', '%@')", tempObject.ID, tempObject.name, tempObject.area, tempObject.title, tempObject.description, tempObject.url];

该字符串的结果是这样

insert into myTable (_id, name, area, title, description, url) VALUES (0, 'John Smith', 'USA', 'Programmer', 'John Smith is a programmer. He programs a lot. John Smith's duty is to program. He loves it. His dog's name is Billy.', 'www.google.com')

当我尝试将其插入表中时，在"s"附近收到语法错误.我想适当地转义撇号.我将如何去做?谢谢，感谢您的见解！

When I try to insert that into the table, I get a syntax error near "s". I want to properly escape the apostrophes. How would I go about doing that? Thanks, any insight is appreciated!

编辑:我们已解决了问题.我们已经在字符串中输入了一些单引号(')，我们确实希望使用弯引号.卷曲的单引号不会与SQLite插入语句混淆.对于那些有此问题的人，您可以使用单引号而不是常规的单引号.在您的情况下，这可能是不可能的，但是对于某些情况，这可能是一个简单的解决方案.

We have solved our issue. We had entered some single quotes (') into our strings where we really wanted curly single quotes. The curly single quotes do not mess with the SQLite insert statements. For those who have this problem, you could possibly use single curly quotes instead of regular single quotes. This may not be possible in your circumstances, but for some it could be a simple solution.

推荐答案

问题是您的数据中包含单引号.您有两种选择可以克服这个问题.

The issue is your data contains single quote in it. You have two option to overcome this.

1. 使用 \'
转义'
2. 使用 sqlite3_bind_xxx()方法绑定值(我更喜欢这种方法).您可以这样做:

1. Escape ' with \'
2. Use sqlite3_bind_xxx() method for binding the values ( I prefer this method). You can do it like:

NSString *sqlInsert =  @"insert into myTable (_id, name, area, title, description, url) VALUES (?, ?, ?, ?, ?, ?)";
if (sqlite3_open([yourDBPath UTF8String], &yourDB) == SQLITE_OK)
{
   if (sqlite3_prepare_v2(yourDB, sql, 1, &stmt, NULL) == SQLITE_OK)
   {
      sqlite3_bind_int(stmt, 1, tempObject.ID);
      sqlite3_bind_text(stmt, 2, [tempObject.name UTF8String], -1, SQLITE_TRANSIENT);
      sqlite3_bind_text(stmt, 3, [tempObject.area UTF8String], -1, SQLITE_TRANSIENT);
      sqlite3_bind_text(stmt, 4, [tempObject.title UTF8String], -1, SQLITE_TRANSIENT);
      sqlite3_bind_text(stmt, 5, [tempObject.description UTF8String], -1, SQLITE_TRANSIENT);
      sqlite3_bind_text(stmt, 6, [tempObject.url UTF8String], -1, SQLITE_TRANSIENT);
      // Execute
   }
}

这篇关于如何在SQLite插入语句中正确转义单引号-iOS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！