【iOS开发-104】SQLite使用：注意查询时分步写while会出现死循环

（1）注意点：

在SELECT查询时，不能分步写成如下形式，否则会出现死循环：

int stepResult=sqlite3_step(statement);
while (stepResult==SQLITE_ROW) {

}

while (sqlite3_step(statement)==SQLITE_ROW) {
         
}

** ^If the SQL statement being executed returns any data,then [SQLITE_ROW]

** is returned each time a new row of data is ready for processing by the

** caller. The values may be accessed using the [column access functions].

** sqlite3_step() is called again to retrieve the next row of data.

#import "ViewController.h"
#import <sqlite3.h>

@interface ViewController ()
- (IBAction)insert:(id)sender;
- (IBAction)delete:(id)sender;
- (IBAction)update:(id)sender;
- (IBAction)select:(id)sender;
@end

@implementation ViewController

static sqlite3 *_db;

- (void)viewDidLoad {
    [self setupDatabaseAndTable];
    [super viewDidLoad];
    // Do any additional setup after loading the view,typically from a nib.
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

-(void)setupDatabaseAndTable{
    NSString *filename=[[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) lastObject] stringByAppendingPathComponent:@"iOS.sql"];
    NSLog(@"%@",filename);
    
    int openDBResult=sqlite3_open(filename.UTF8String,&_db);
    
    if (openDBResult==SQLITE_OK) {
        //创建表
        NSString *createTableSql=@"CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER);";
        char *error=nil;
        int createTableResult=sqlite3_exec(_db,createTableSql.UTF8String,NULL,&error);
        if (createTableResult==SQLITE_OK) {
            NSLog(@"创建表成功");
        }else{
            NSLog(@"创建表失败");
        }
        NSLog(@"打开数据库成功");
    }else{
        NSLog(@"打开数据库失败");
    }
}

- (IBAction)insert:(id)sender {
    for (int i=0; i<50; i++) {
        NSString *name=[NSString stringWithFormat:@"jack%d",arc4random()%100];
        int age=arc4random()%100;
        NSString *insertTableSql=[NSString stringWithFormat:@"INSERT INTO user(name,age) VALUES('%@',%d);",name,age];
        char *error=nil;
        int insertResult=sqlite3_exec(_db,insertTableSql.UTF8String,&error);
        if (insertResult==SQLITE_OK) {
            NSLog(@"%d %@ %d",i,age);
        }else{
            NSLog(@"插入数据失败");
        }
    }
}

- (IBAction)delete:(id)sender {
    NSString *deleteRowSql=@"DELETE FROM user WHERE id=1;";
    char *error=nil;
    int deleteResult=sqlite3_exec(_db,deleteRowSql.UTF8String,&error);
    if (deleteResult==SQLITE_OK) {
        NSLog(@"删除数据成功");
    }else{
        NSLog(@"删除数据失败");
    }
}

- (IBAction)update:(id)sender {
    NSString *updateRowSql=@"UPDATE user SET age=130 WHERE id=3;";
    char *error=nil;
    int updateResult=sqlite3_exec(_db,updateRowSql.UTF8String,&error);
    if (updateResult==SQLITE_OK) {
        NSLog(@"修改数据库成功");
    }else{
        NSLog(@"修改数据库失败");
    }
}

- (IBAction)select:(id)sender {
    NSString *selectRowSql=@"SELECT id,age FROM user WHERE id=5;";
    sqlite3_stmt *statement;
    int stmtResult=sqlite3_prepare_v2(_db,selectRowSql.UTF8String,-1,&statement,nil);
    if (stmtResult==SQLITE_OK) {
        NSLog(@"stmtResult成功");
        //以下语句分步写会出现死循环
        while (sqlite3_step(statement)==SQLITE_ROW) {
            NSLog(@"stepResult成功");
            int uid=sqlite3_column_int(statement,0);
            const unsigned char *uname=sqlite3_column_text(statement,1);
            int uage=sqlite3_column_int(statement,2);
            NSLog(@"%d %s %d",uid,uname,uage);
        }
    }else{
        NSLog(@"stmtResult失败");
    }
}
@end

（2）防止SQL注入的简单处理，利用sqlite3_bind_text(int)等进行变量绑定，这个语句会对输入的字符进行处理。

- (IBAction)select:(id)sender {
    NSString *selectRowSql=@"SELECT id,age FROM user WHERE name=?;";
    sqlite3_stmt *statement;
    int stmtResult=sqlite3_prepare_v2(_db,nil);
    if (stmtResult==SQLITE_OK) {
        NSLog(@"stmtResult成功");
        sqlite3_bind_text(statement,1,"jack1",NULL);
        //以下语句分步写会出现死循环
        while (sqlite3_step(statement)==SQLITE_ROW) {
            NSLog(@"stepResult成功");
            int uid=sqlite3_column_int(statement,uage);
        }
    }else{
        NSLog(@"stmtResult失败");
    }
}

- (IBAction)select:(id)sender {
    NSString *selectRowSql=@"SELECT id,age FROM user WHERE name LIKE ?;";
    sqlite3_stmt *statement;
    int stmtResult=sqlite3_prepare_v2(_db,"%%jack1%%",uage);
        }
    }else{
        NSLog(@"stmtResult失败");
    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!