column为TEXT类型时，数据插入和更新时遇到的问题

• TEXT 类型的column不包含单引号时

  正常sql语句，因TEXT类型的column不包含单引号，所以SQL语句执行正常

INSERT INTO demo (uid,name,age,weight,registered,desc) VALUES (1,'GG',29,74.800000,0,'iOS 开发工程师')

• TEXT 类型的column包含单引号时

  当TEXT类型的column包含单引号时，实际执行的语句

INSERT INTO demo (uid,name,age,weight,registered,desc) VALUES (1,''GG',29,74.800000,0,'iOS 开发工程师')

遇到的问题

2021-02-19 14:07:23.151251+0800 SQLite3Demo[6126:469210] [logging] near "GG": syntax error in "INSERT INTO demo (uid,name,age,weight,registered,desc) VALUES (1,''GG'',29,74.800000,0,'iOS 开发工程师')"
2021-02-19 14:07:23.151336+0800 SQLite3Demo[6126:469210] DB Error: 1 "near "GG": syntax error"
2021-02-19 14:07:23.151428+0800 SQLite3Demo[6126:469210] DB Query: INSERT INTO demo (uid,name,age,weight,registered,desc) VALUES (1,''GG'',29,74.800000,0,'iOS 开发工程师')

• 解决方案

  改进后的语句（创建SQL语句时，将TEXT类型文本中的单引号替换为双引号）

INSERT INTO demo (uid,name,age,weight,registered,desc) VALUES (1,'''GG',29,74.800000,0,'iOS 开发工程师')

解决方案实例

+ (NSString *)createDemoTableSQL{
    return @"CREATE TABLE IF NOT EXISTS demo(uid INT PRIMARY KEY NOT NULL,name TEXT NOT NULL,age INT,weight REAL,registered BLOB,desc TEXT)";
}
/// 插入一条数据
/// @param model model description
+ (BOOL)insertWithModel:(DemoModel *)model{
    NSString *sqlPre = @"INSERT INTO demo (uid,name,age,weight,registered,desc) VALUES ";
    /// 注意：
    ///   1. 字符串类型的value需要使用单引号引起来，否则会报错
    ///   2. 需要将字符串类型的单引号，变为两个单引号
    NSString *toSaveName = [model.name stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    NSString *toSaveDesc = [model.desc stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    NSString *valuesStr = [NSString stringWithFormat:@"(%ld,'%@',%ld,%f,%d,'%@')",model.uid,toSaveName,model.age,model.weight,model.registered,toSaveDesc];
    NSString *sql = [sqlPre stringByAppendingString:valuesStr];
    NSLog(@"%@",sql);
    BOOL res = [db executeUpdate:sql];
    return res;
}