Basic use of SQLite-sample code

Basic use of SQLite

iwviewcontroller.m//01-sqlite Basic use////Created by Apple on 14-5-22.//Copyright (c) 2014 itcast. All rights reserved.//#import "IWViewController.h" #import <sqlite3.h> @interface Iwviewcontroller () {///member variable default =n ULL Sqlite3 *_db; The DB represents the entire database, DB is the DB instance}-(ibaction) insert;-(ibaction) update;-(ibaction) delete;-(ibaction) query;@        End@implementation iwviewcontroller-(void) viewdidload{[Super Viewdidload]; 0. Obtain the database file name in the sandbox nsstring *filename = [[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask,        YES) Lastobject] stringbyappendingpathcomponent:@ "Student.sqlite"];        NSLog (@ "filename=====%@", filename); 1. Create (Open) database (the database is automatically created if the database file does not exist) int result = Sqlite3_open (filename.    Utf8string, &_db);                if (result = = Sqlite_ok) {NSLog (@ "Successfully open database"); 2. CREATE Table const char *SQL = "CREATE table if not exists t_student (ID integer primary key autoincrement, name text, a                GE integer); "; //For the sake of rigor, usually the first place to pass the address is emptied.        First make sure that the variable is a null pointer, not a wild pointer.                char *ERRORMESG = NULL; SQLITE3_EXEC Execute SQL statement//parameter interpretation: First DB instance, second SQL statement, third SQLITE3_EXEC statement execution successful callback, fourth callback to pass something, fifth error message//C language Empty is uppercase NULL//Sqlite3_exec (< #sqlite3 *#>, < #const char *sql#>, < #int (*callback) (void *, int, char * *, Cha R * *) #>, < #void *#>, < #char **errmsg#>) int result = Sqlite3_exec (_db, SQL, NULL, NULL, &errorme        SG);        if (result = = Sqlite_ok) {NSLog (@ "Successfully created T_student table");     } else {NSLog (@ "Failed to create T_student table:%s", ERRORMESG);//%s is used to print the C-language string}} else {NSLog (@ "Open database Failed"); }}-(ibaction) insert{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, &AMP;ERRORMESG);        if (result = = Sqlite_ok) {NSLog (@ "Add data successfully");        } else {NSLog (@ "Add Data failed:%s", ERRORMESG); }}}-(Ibaction) query{//SQL injection Vulnerability/** login feature 1. User input account number and password * account number: 123 ' or 1 = 1 or ' = ' * password : 456654679 2. Get user input account number and password to database query (query there is no user name and password) SELECT * from t_user where username = ' 123 ' and password = ' 45               6 ';     SELECT * from t_user where username = ' 123 ' and password = ' 456 ';        *///1. Define the SQL statement const char *SQL = "SELECT ID, Name, age from t_student where name =?;";        2. Define a stmt to hold the result set sqlite3_stmt *stmt = NULL; 3. Detecting the legality of a SQL statement the third parameter: the length of the SQL statement, where write-1 will automatically help us calculate the length of the SQL string.    Last parameter: Related to the tail of the SQL statement, which is not written here first.    int result = SQLITE3_PREPARE_V2 (_db, SQL,-1, &stmt, NULL);                if (result = = Sqlite_ok) {NSLog (@ "query statement is legal"); /** Sqlite3_bind_text (): Most of the binding functions have only 3 parameters the 1th parameter is sqlite3_stmt * type        The 2nd parameter refers to the position of the placeholder, the position of the first placeholder is 1, not 0, the number of question mark 3rd parameter refers to the value of the placeholder to bind the 4th parameter refers to the length of the data passed in the 3rd parameter, and for the C string, pass-1 automatically calculates the length of the string The 5th parameter is an optional function callback, which is typically used to complete the memory cleanup work after the statement is executed Sqlite_step (): Execute SQL statement, return Sqlite_done represents successful execution complete sqlite_finalize (): Destroy        SQLITE3_STMT * * * Object *//Set the contents of the placeholder, using placeholders, will automatically detect SQL injection, can effectively prevent SQL injection problem.                Sqlite3_bind_text (stmt, 1, "Jack",-1, NULL);        4. Execute the SQL statement to remove the data from the result set//int stepresult = Sqlite3_step (stmt);            while (Sqlite3_step (stmt) = = Sqlite_row) {//query to a row of data//get the corresponding data for this line//get the ID of the No. 0 column                        int sid = Sqlite3_column_int (stmt, 0);                        Gets the 1th column of the name const unsigned char *sname = Sqlite3_column_text (stmt, 1);                        Get age int sage = Sqlite3_column_int (stmt, 2) of the 2nd column;        NSLog (@ "%d%s%d", Sid, Sname, Sage);    }} else {NSLog (@ "query statement is not legal"); }} @end

Basic use of SQLite-sample code