2018 April 24 SQLite

SQLite之二在iOS中的编码

SQLite在IOS中的编码

在iOS中使用SQLite3，首先要添加库文件libsqlite3.dylib和导入主头文件import
该框架所有API都是纯C语言的
所有的函数基本都已sqlite3_开头

创建、打开、关闭数据库

创建或打开数据库

 // path为：~/Documents/person.db
 sqlite3 *db = NULL;
 int result = sqlite3_open([path UTF8String], &db); 

代码解析
1. sqlite3_open():将根据文件路径打开数据库，如果不存在，则会创建一个新的数据库。如果result等于常量SQLITE_OK，则表示成功打开数据库
2. sqlite3 *db：一个打开的数据库实例
3. 数据库文件的路径必须以C字符串(而非NSString)传入
关闭数据库：sqlite3_close(db);

执行不返回数据的SQL语句

执行创表语句

 char *errorMsg = NULL;  // 用来存储错误信息
 char *sql = "create table if not exists t_person(id integer primary key autoincrement, name text, age integer);";
 int result = sqlite3_exec(db, sql, NULL, NULL, &errorMsg);

代码解析:
1. sqlite3_exec():可以执行任何SQL语句，比如创表、更新、插入和删除操作。但是一般不用它执行查询语句，因为它不会返回查询到的数据
2. sqlite3_exec()还可以执行的语句：
  1. 开启事务：begin transaction;
  2. 回滚事务：rollback;
  3. 提交事务：commit;

带占位符插入数据

SQL注入漏洞

以登录功能来说明注入漏洞
情况如下:
1. 用户输入账号和密码
  1. 账号: 123’ or 1 = 1 or ‘’ = ‘
  2. 密码: 123456
2. 拿到用户输入的账号和密码去数据库查询（查询有没有这个用户名和密码）(正常情况下就是拼接,组成SQL语句)
  1. 拼接后结果如下:
    select * from t_user where username = '123' or 1 = 1 or '' = '' and password = '456';
  2. 那么这个SQL语句是一直成立的,因为是or连接,1=1永远成立
  3. 这样一定会查询出数据,因此,为了避免SQL注入漏洞,不能简单的字符串拼接,需要使用占位符插入数据

带占位符插入数据

代码

 1.定义sql语句
  char *sql = "insert into t_person(name, age) values(?, ?);";
  // 2.定义一个stmt存放结果集
  sqlite3_stmt *stmt;
  // 3.检测SQL语句的合法性
  if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
  //SQLITE_OK: 代表合法
  // 4. 设置占位符的内容
  sqlite3_bind_text(stmt, 1, "母鸡", -1, NULL);
  sqlite3_bind_int(stmt, 2, 27);
  }
  5.执行SQL语句，从结果集中取出数据
  if (sqlite3_step(stmt) != SQLITE_DONE) {
  NSLog(@"插入数据错误");
  }
  //6. 销毁sqlite3_stmt *对象
  sqlite3_finalize(stmt);

代码解析
1. sqlite3_prepare_v2():
  1. 返回值等于SQLITE_OK，说明SQL语句已经准备成功，没有语法问题
  2. 参数:
    1. db : 查询哪个数据库
    2. zSql: sql语句
    3. nByte: sql语句的长度,只要写一个-1会自动计算
    4. ppStmt: 用来存放结果集
    5. pzTail: 不用直接设置为NULL
2. sqlite3_bind_类型()：
  1. 大部分绑定函数都只有3个参数(sqlite3_bind_int就是3个)
  2. 之前sql语句中的是?站位,因此这里要告诉站位的是啥
  3. 一定要放在执行(sqlite3_step)之前设置
  4. sqlite3_bind_text的参数
    1. 第1个参数是sqlite3_stmt *类型,对应sql语句检查的结果集
    2. 第2个参数指占位符的位置，第一个占位符的位置是1，不是0(从左至右,从1开始计算,第一个?代表1,第二个代表2…)
    3. 第3个参数指占位符要绑定的值
    4. 第4个参数指在第3个参数中所传递数据的长度，对于C字符串，可以传递-1代替字符串的长度
    5. 第5个参数是一个可选的函数回调，一般用于在语句执行后完成内存清理工作
3. sqlite_step()：
  1. 执行SQL语句，返回SQLITE_DONE代表成功执行完毕
  2. 返回SQLITE_ROW,代表成功查询到一行数据
4. sqlite_finalize()：
  1. 销毁sqlite3_stmt *对象
使用占位符插入数据,能够自动检测SQL注入漏洞,安全

查询数据

代码

 // 1.定义sql语句
 //只要这里写一个?占位:防止注入漏洞
 const char *sql = "select id, name, age from t_student where name = ?;";
    
 // 2.定义一个stmt存放结果集
 sqlite3_stmt *stmt = NULL;
    
 // 3.检测SQL语句的合法性
 
 int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);
 if (result == SQLITE_OK) {
     NSLog(@"查询语句是合法的");
        
     //4. 设置占位符的内容
       
     sqlite3_bind_text(stmt, 1, "jack", -1, NULL);
        
     // 5.执行SQL语句，从结果集中取出数据
     //注意:这样写是错误的!!!!!,必须放在while()中,否则会死循环
     //int stepResult = sqlite3_step(stmt);
     // while (stepResult == SQLITE_ROW) {}
     while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据
         //5.1获得这行对应的数据
           
         // 获得第0列的id
         int sid = sqlite3_column_int(stmt, 0);
            
         // 获得第1列的name
         const unsigned char *sname = sqlite3_column_text(stmt, 1);
            
         // 获得第2列的age
         int sage = sqlite3_column_int(stmt, 2);
            
         NSLog(@"%d %s %d", sid, sname, sage);
     }
 } else {
     NSLog(@"查询语句非合法");
 }
 sqlite3_finalize(stmt);

代码解析
1. sqlite3_step():
  1. 返回SQLITE_ROW代表遍历到一条新记录
2. ` sqlite3_column_类型()`:
  1. 用于获取每个字段对应的值，第2个参数是字段的索引，从0开始
  2. 参数:
    1. sqlite3_stmt*: 查询出来行的结果集
    2. iCol: 第几列的值(从0开始)

代码举例

例1:

 #import "ViewController.h"
 #import <sqlite3.h>
 @interface ViewController ()
 {
     sqlite3 *_db; // db代表着整个数据库，db是数据库实例
 }
 @end
        
 @implementation ViewController
        
 - (void)viewDidLoad {
     [super viewDidLoad];
     // 0.获得沙盒中的数据库文件名
     NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
     //1. 创建(打开)数据库（如果数据库文件不存在，会自动创建）
     int result = sqlite3_open(filename.UTF8String, &_db);
     if (result == SQLITE_OK) {
         NSLog(@"成功打开数据库");
         //2. 创建一张表
         const char *sql = "create table if not exists t_student (id integer primary key autoincrement, name text, age integer);";
         char *errorMesg = NULL;
         int result = sqlite3_exec(_db, sql, NULL, NULL, &errorMesg);
         if (result == SQLITE_OK) {
             NSLog(@"成功创建t_student表");
         } else {
             NSLog(@"创建t_student表失败:%s", errorMesg);
         }
     }else{
         NSLog(@"打开数据库失败");
     }
 }
 //插入数据(不带占位符)
 - (IBAction)insert:(id)sender {
     for (int i = 0; i<30; i++) {
         NSString *name = [NSString stringWithFormat:@"Jack-%d", arc4random()%100];
         int age = arc4random()%100;
         NSString *sql = [NSString stringWithFormat:@"insert into t_student (name, age) values('%@', %d);", name, age];
            
         char *errorMesg = NULL;
         int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errorMesg);
         if (result == SQLITE_OK) {
             NSLog(@"成功添加数据");
         } else {
             NSLog(@"添加数据失败:%s", errorMesg);
         }
     }
 }
 //删除/更新跟插入一样,只是SQL语句不同
 //查询上面已有了
 @end

例2: 直接封装成一个工具类

 //  学生数据的CRUD（增删改查）

 #import <Foundation/Foundation.h>
 @class IWStudent;
    
 @interface IWStudentTool : NSObject
    
 /**
  *  添加学生
  *
  *  @param student 需要添加的学生
  */
 + (BOOL)addStudent:(IWStudent *)student;
    
 /**
  *  获得所有的学生
  *
  *  @return 数组中装着都是IWStudent模型
  */
 + (NSArray *)students;
    
 /**
  *  根据搜索条件获得对应的学生
  *
  *  @param condition 搜索条件
  */
 + (NSArray *)studentsWithCondition:(NSString *)condition;
    
 @end
    
 #import "IWStudentTool.h"
 #import "IWStudent.h"
 #import <sqlite3.h>
    
 @implementation IWStudentTool
    
 // static的作用：能保证_db这个变量只被IWStudentTool.m直接访问
 static sqlite3 *_db;
    
 + (void)initialize
 {
     // 0.获得沙盒中的数据库文件名
     NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
        
     // 1.创建(打开)数据库（如果数据库文件不存在，会自动创建）
     int result = sqlite3_open(filename.UTF8String, &_db);
     if (result == SQLITE_OK) {
         NSLog(@"成功打开数据库");
            
         // 2.创表
         const char *sql = "create table if not exists t_student (id integer primary key autoincrement, name text, age integer);";
         char *errorMesg = NULL;
         int result = sqlite3_exec(_db, sql, NULL, NULL, &errorMesg);
         if (result == SQLITE_OK) {
             NSLog(@"成功创建t_student表");
         } else {
             NSLog(@"创建t_student表失败:%s", errorMesg);
         }
     } else {
         NSLog(@"打开数据库失败");
     }
 }
    
 + (BOOL)addStudent:(IWStudent *)student
 {
     NSString *sql = [NSString stringWithFormat:@"insert into t_student (name, age) values('%@', %d);", student.name, student.age];
    
     char *errorMesg = NULL;
     int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errorMesg);
        
     return result == SQLITE_OK;
 }
    
 + (NSArray *)students
 {
     // 0.定义数组
     NSMutableArray *students = nil;
        
     // 1.定义sql语句
     const char *sql = "select id, name, age from t_student;";
        
     // 2.定义一个stmt存放结果集
     sqlite3_stmt *stmt = NULL;
        
     // 3.检测SQL语句的合法性
     int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);
     if (result == SQLITE_OK) {
         NSLog(@"查询语句是合法的");
         students = [NSMutableArray array];
            
         // 4.执行SQL语句，从结果集中取出数据
         while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据
             // 获得这行对应的数据
                
             IWStudent *student = [[IWStudent alloc] init];
                
             // 获得第0列的id
             student.ID = sqlite3_column_int(stmt, 0);
                
             // 获得第1列的name
             const unsigned char *sname = sqlite3_column_text(stmt, 1);
             student.name = [NSString stringWithUTF8String:(const char *)sname];
                
             // 获得第2列的age
             student.age = sqlite3_column_int(stmt, 2);
                
             // 添加到数组
             [students addObject:student];
         }
     } else {
         NSLog(@"查询语句非合法");
     }
        
     return students;
 }
    
 + (NSArray *)studentsWithCondition:(NSString *)condition
 {
     // 0.定义数组
     NSMutableArray *students = nil;
        
     // 1.定义sql语句
     //like: 模糊查询,包含
     // like '%内容%'
     const char *sql = "select id, name, age from t_student where name like ?;";
        
     // 2.定义一个stmt存放结果集
     sqlite3_stmt *stmt = NULL;
        
     // 3.检测SQL语句的合法性
     int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);
     if (result == SQLITE_OK) {
         NSLog(@"查询语句是合法的");
         students = [NSMutableArray array];
            
         // 填补占位符的内容(oc中的2个% 代表C语言中的一个%)
         NSString *newCondition = [NSString stringWithFormat:@"%%%@%%", condition];
 //        NSLog(@"%@", newCondition);
         sqlite3_bind_text(stmt, 1, newCondition.UTF8String, -1, NULL);
            
         // 4.执行SQL语句，从结果集中取出数据
         while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据
             // 获得这行对应的数据
                
             IWStudent *student = [[IWStudent alloc] init];
                
             // 获得第0列的id
             student.ID = sqlite3_column_int(stmt, 0);
                
             // 获得第1列的name
             const unsigned char *sname = sqlite3_column_text(stmt, 1);
             student.name = [NSString stringWithUTF8String:(const char *)sname];
                
             // 获得第2列的age
             student.age = sqlite3_column_int(stmt, 2);
                
             // 添加到数组
             [students addObject:student];
         }
     } else {
         NSLog(@"查询语句非合法");
     }
        
     return students;
 }
 @end

SQLite之二 在iOS中的编码

SQLite在IOS中的编码

创建、打开、关闭数据库

执行不返回数据的SQL语句

带占位符插入数据

SQL注入漏洞

带占位符插入数据

查询数据

代码举例

SQLite之二在iOS中的编码