使用SQLCipher加密数据库

时间:2024-02-29 21:14:02
   Xcode中集成了免费的sqlite,但是不提供加密的模块,突然有一天,蛋疼的客户要求把数据进行加密,于是乎就寻找使用简单并且可以把数据迁移过度到加密数据库的框架。
  SQLCipher是第三方的开源框架,实现对sqlite的加密,官网链接:http://sqlcipher.net。下面开始下载并导入框架。(使用命令行下载)
一、使用SQLCipher需要3个文件:sqlcipher,openssl-xcode,openssl-1.0.0e
  下载 openssl-xcode
  
cd ~/Documents/code/SQLCipherApp
git clone https://github.com/sqlcipher/openssl-xcode.git

  下载 sqlcipher

cd ~/Documents/code/SQLCipherApp
git clone https://github.com/sqlcipher/sqlcipher.git

   下载 openssl-1.0.0e

curl -o openssl-1.0.0e.tar.gz http://www.openssl.org/source/openssl-1.0.0e.tar.gz
//解压
tar xzf openssl-1.0.0e.tar.gz

   把这三个目录拷贝到工程目录中

二.配置Xcode

  1、打开Xcode 的设置页,进入locations ->source trees  ,点击+号添加项目 ,settingname 和 display name 均设为  “OPENSSL_SRC”  path设置为你工程目录下openssl-1.0.0e的所在路径。比如我的路径是:/Users/henry/Documents/工程公用/SQLCipherApp/openssl-1.0.0e

  

     2、添加项目的引用 ,将文件里的openssl.xcodeproj 和sqlcipher.xcodeproj (分别在openssl-xcode文件和sqlcipher文件下)添加到你的主工程下,建立引用
     3、配置编译库,进入项目的工程TARGETS,进入build phases ->target dependencies,添加图中的两个项目
  
 
link binary with libraries添加这两个库
 
 
 
三、下面举个使用的例子
首先需要引入头文件 import<sqlite3.h>
 
//打开数据库的函数
  +(BOOL) OpenDB {
    NSArray *pathArr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docPaths = [pathArr objectAtIndex:0];
    NSString *dbPath = [docPaths stringByAppendingFormat:@"/myDB.db"];
    int status = sqlite3_open([dbPath UTF8String], &GB_DB);

    if (status != SQLITE_OK)
    {
        LOG_CINFO(@"打开数据库出错!");
        DB_Opened = NO;
        return DB_Opened;
    }
    DB_Opened = YES;
sqlite3_stmt *statement;
#if UseASE
    //验证sql语句是否成功
    const char *key = [[GlobalData GetInstance].GB_DBKey UTF8String];
    sqlite3_key(GB_DB, key, strlen(key));
#endif
    if(sqlite3_prepare_v2(GB_DB, [sql UTF8String], -1, &statement, nil) != SQLITE_OK)
    {
        LOG_CINFO(@"创建表格失败!");
        return  NO;
    }

    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    //[MyDataBase CloseDB];
    if (success != SQLITE_DONE)
    {
        LOG_CINFO(@"在创建表格的过程中出错,创建没有进行完!");
        return NO;
    }
        return YES;
}
//创建需要的数据表
+ (void)CreateNeedTable {
    @autoreleasepool {

        //插入用户信息表
        LocalDataBase *userTb = [LocalDataBase GetTableWithType:@"user" HasUser:NO];
            //先创建
               [userTb CreateTableWithKeys:[NSArray arrayWithObjects:@"userid", nil] OtherNeeds:[NSArray arrayWithObjects:@"siteid",@"username",@"password",@"mobilePhone",@"name", nil] Data:nil];
}

 

//根据参数创建表,keys是主键,needs是那些非主键,但是必须需要的,如需要用它来排序,搜索的字段,data为数据项的整个data形式,用data是为了减少字段数
-(BOOL) CreateTableWithKeys:(NSArray *)keys OtherNeeds:(NSArray *)needs Data:(NSString *)data {
    //如果有信息,说明注册成功了已经
    if(self.myTableInfo)
    {
        return YES;
    }

    //首先确保数据库是打开的
    if (![LocalDataBase OpenDB])
    {
        return NO;
    }

    //把所有非数据的字段写到一个数组中

    NSMutableArray *allArr = [NSMutableArray arrayWithCapacity:10];
    if ([keys count] > 0)
    {
        [allArr addObjectsFromArray:keys];
    }

    if ([needs count] > 0)
    {
        [allArr addObjectsFromArray:needs];
    }


    if (self.hasUser)
    {
        [allArr insertObject:@"userName" atIndex:0];
    }

    int keysCount = [allArr count];

    if (self.myTableInfo == nil)
    {
        self.myTableInfo = [NSMutableArray arrayWithCapacity:keysCount+1];
    }
    //插入数据
    for(int i = 0; i < keysCount;++i)
    {
        NSString *key = [allArr objectAtIndex:i];
        [self.myTableInfo addObject:[NSDictionary dictionaryWithObjectsAndKeys:@"text",@"type", key,@"key", nil]];
    }


    //创建sql语句
    NSMutableString *cSql = [NSMutableString stringWithFormat:@"create table if not exists %@ (",self.myTableName];

    //把非数据类型的字段加入sql语句
    for(NSString *key in allArr)
    {
        [cSql appendFormat:@"%@ text,",key];
    }

    //把数据类型的字段加入Sql语句
    if (data != nil)
    {
        [cSql appendFormat:@"%@ blob,",data];
        [self.myTableInfo addObject:[NSDictionary dictionaryWithObjectsAndKeys:@"blob",@"type", data,@"key", nil]];
    }

    //添加主键
    [cSql appendString:@"primary key("];

    int keyCount = [keys count];
    if (keyCount > 0)//有多个主键的情况
    {
        for(int i = 0; i < keyCount - 1; ++i)
        {
            NSString *key = [keys objectAtIndex:i];
             [cSql appendFormat:@"%@,",key];
        }

        if(self.hasUser)
        {
            [cSql appendString:@"userName,"];
        }
        [cSql appendFormat:@"%@)",[keys objectAtIndex:keyCount - 1]];
    }
    else
    {
//        if (keyCount == 1)//只有一个主键的情况
//        {
//            [cSql appendFormat:@"%@)",[keys objectAtIndex:0]];
//        }
//        else
        {
            if(self.hasUser)
            {
                [cSql appendString:@"userName)"];
            }
        }
    }

    [cSql appendString:@")"];

    LOG_CINFO(@"========sql 语句 创建表=========");
    LOG_CINFO(cSql);

    {
        NSMutableDictionary *dic = (NSMutableDictionary *)[GlobalFunc ParseDicFromFile:@"dbInfo.plist"];
        if (dic == nil)
        {
            dic = [NSMutableDictionary dictionaryWithCapacity:1];
        }
        [dic setObject:self.myTableInfo forKey:self.myTableName];
        [GlobalFunc WriteDicToFile:dic FileName:@"dbInfo.plist"];
    }

    return [LocalDataBase CreateTableWithSql:cSql];
}

 

//向表中插入数据
-(BOOL) InsertDataWithDic:(NSDictionary *)dic Replace:(BOOL) replace {
    // 打开数据库
    if (!DB_Opened)
    {
        if(![LocalDataBase OpenDB])
        {
            LOG_CINFO(@"插入数据失败,打开数据库出错!");
            return NO;
        }
    }

    NSMutableDictionary *tmpDic = [NSMutableDictionary dictionaryWithDictionary:dic];
    if (self.hasUser)
    {
        [tmpDic setObject:[GlobalData GetInstance].GB_UserName forKey:@"userName"];
    }

    NSMutableArray *allKeys = [NSMutableArray arrayWithArray:[tmpDic allKeys]];


    NSMutableString *cSql = nil;
    //生成插入语句
    if (replace)
    {
        cSql = [NSMutableString stringWithFormat:@"insert or REPLACE into %@(",self.myTableName];
    }
    else
    {
        cSql = [NSMutableString stringWithFormat:@"insert into %@(",self.myTableName];
    }

    int keysCount = [allKeys count];

    if (keysCount > 0)
    {
        for(int i = 0; i < keysCount-1;++i)
        {
            [cSql appendFormat:@"%@,",[allKeys objectAtIndex:i]];
        }

        [cSql appendFormat:@"%@)",[allKeys objectAtIndex:keysCount -1]];
    }
    else
    {
        return NO;
    }

    [cSql appendString:@" values("];


    for(int i = 0; i<keysCount -1; ++i)
    {
        [cSql appendString:@"?,"];
    }
    [cSql appendString:@"?)"];

    LOG_CINFO(@"========sql 语句 插入表=========");
    LOG_CINFO(cSql);

    //测试sql 语句是否正确
    sqlite3_stmt *statement;

    const char *insertStatement = [cSql UTF8String];
#if UseASE
    //验证sql语句是否成功
    const char *key = [[GlobalData GetInstance].GB_DBKey UTF8String];
    sqlite3_key(GB_DB, key, strlen(key));
#endif
    if(sqlite3_prepare_v2(GB_DB, insertStatement, -1, &statement, NULL) != SQLITE_OK)
    {
        LOG_CINFO(@"向表格中插入数据失败,可能Sql语句不正确!");
        [GlobalFunc ShowNormalAlert:[NSString stringWithFormat:@"向表格中插入数据失败,可能Sql语句不正确,表名为%@",self.myTableName]];
        return  NO;
    }

    for(int i = 0; i < keysCount;++i)
    {
        NSString *key = [allKeys objectAtIndex:i];

        id value = [tmpDic objectForKey:key];

        //如果是Data类型
        if ([value isKindOfClass:[NSData class]])
        {
            sqlite3_bind_blob(statement,  i+1, [value bytes], [value length], NULL);
        }
        else//是字符串类型
        {
            sqlite3_bind_text(statement, i+1, [value UTF8String], -1, NULL);
        }
    }

    int success = sqlite3_step(statement);
    // 释放资源
    sqlite3_finalize(statement);

    if (success == SQLITE_ERROR)
    {
        LOG_CINFO(@"向表格中插入数据失败,未知原因提前结束!");
        [GlobalFunc ShowNormalAlert:[NSString stringWithFormat:@"向表格中插入数据失败,未知原因提前结束,表名为%@",self.myTableName]];
        return NO;
    }
    LOG_CINFO(@"向表格中插入数据成功!");
    return YES;

}

 

//更新表字段,key是要更新的字段名称,newValue是更新后要设计的值,where是条件(sql语句中的),condition是满足更新的条件,use是否使用用户名为条件
-(BOOL) UpdateRecordWithKey:(NSString *)key Value:(NSString *)newValue Where:(NSString *)where Condition:(NSString *)condition UseUser:(BOOL)use {
    if(![LocalDataBase OpenDB])
    {
        return NO;
    }
    @try
    {
        NSString *tmpUpdateSql = nil;

        if (use && self.hasUser)
        {
            tmpUpdateSql =  [NSString stringWithFormat:@"UPDATE %@ SET %@ = ? where %@ = ? and userName = ?",self.myTableName,key,where];
        }
        else
        {
            tmpUpdateSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = ? where %@ = ?",self.myTableName,key,where];//@"UPDATE tb_bulletlist SET has_read = ? where bulletin_code = ? and user_name=?";
        }
        sqlite3_stmt *statement;

        LOG_CINFO(@"========sql 语句 更新表=========");
        LOG_CINFO(tmpUpdateSql);
#if UseASE
        //验证sql语句是否成功
        const char *key = [[GlobalData GetInstance].GB_DBKey UTF8String];
        sqlite3_key(GB_DB, key, strlen(key));
#endif
        if(sqlite3_prepare_v2(GB_DB, [tmpUpdateSql UTF8String], -1, &statement, nil) != SQLITE_OK)
        {
            LOG_CINFO(@"更新数据失败!");
             [GlobalFunc ShowNormalAlert:[NSString stringWithFormat:@"更新数据失败,表名为%@",self.myTableName]];
            return  NO;
        }

        sqlite3_bind_text(statement, 1, [newValue UTF8String], -1, NULL);
        sqlite3_bind_text(statement, 2, [condition UTF8String], -1, NULL);

        if (use && self.hasUser)
        {
            sqlite3_bind_text(statement, 3, [[GlobalData GetInstance].GB_UserName UTF8String], -1, NULL);
        }
        int success = sqlite3_step(statement);

        sqlite3_finalize(statement);
        //[MyDataBase CloseDB];
        if (success != SQLITE_DONE)
        {
            LOG_CINFO(@"更新数据失败,未知原因提前结束!");
             [GlobalFunc ShowNormalAlert:[NSString stringWithFormat:@"更新数据失败,未知原因提前结束,表名为%@",self.myTableName]];
            return NO;
        }

    }
    @catch (NSException *e)
    {
       // LOG_CERR(e);
    }

    return YES;
}

 

//根据传入的关键字和关键字的值,得到一条记录,如果不存在这条记录,返回为nil,估也可用来判断是否存在某条记录,use是否使用用户名为条件
-(NSMutableDictionary *) GetOneRecordWithKeys:(NSArray *)keys Values:(NSArray *)values UseUser:(BOOL)use {
    if ([keys count] != [values count])
    {
//        [GlobalFunc ShowNormalAlert:[NSString stringWithFormat:@"GetOneRecordWithKeys 数据查询参数keys与values个数不一致,表名为%@",self.myTableName]];

        return nil;
    }

    // 打开数据库
    if (!DB_Opened)
    {
        if(![LocalDataBase OpenDB])
        {
            LOG_CINFO(@"查询数据失败,打开数据库出错!");
            return nil;
        }
    }

    NSMutableString *cSql = [NSMutableString stringWithFormat:@"select * from %@ where ",self.myTableName];
    if (use && self.hasUser)
    {
        [cSql appendFormat:@"userName = \'%@\' and ",[GlobalData GetInstance].GB_UserName];
    }

    int keyCount = [keys count];
    for (int i = 0; i < keyCount; ++i)
    {
        if (i == 0)
        {
            [cSql appendFormat:@"%@ = \'%@\'",[keys objectAtIndex:i],[values objectAtIndex:i]];
        }
        else
        {
            [cSql appendFormat:@" and %@ = \'%@\'",[keys objectAtIndex:i],[values objectAtIndex:i]];
        }

    }

    NSArray *tmpArr = [self GetDataArrWithSql:cSql];

    if ([tmpArr count] == 0)
    {
        return nil;
    }
    else
    {
        return [tmpArr objectAtIndex:0];
    }
}

 

  如果在编译时提示:No architectures to compile for (ARCHS=armv6,armv7, VALID_ARCHS=armv7 armv7s则将在Bulid Settings选项下面的Architectures和Valid Architectures里面都改成一样(例如:都填写 armv6 armv7),问题解决。 对于警告 :warning: implicit declaration of function \'sqlite3_key\' is invalid in C99 只需要将Bulid Settings选项下的C Language Dialect 改为:C89[-std-c89] 就可以,即使用c89标准

或者去掉项目中的arm64

 以上只是贴出代码的一部分,可能看起来有些吃力,稍后会把一个完整的使用数据库的类整理出来。

本文参考:http://blog.csdn.net/kuai0705/article/details/8931996