SQlite数据库的C编程接口（四） 绑定参数（Bound Parameters） ——《Using SQlite》读书笔记

xinjiang

　　SQlite数据库的C编程接口（四）
绑定参数（Bound Parameters） by斜风细雨QQ：253786989 2012-02-05
　　 语句参数（statement
parameters）是指插入到SQL命令字符串中的特殊字符，他们作为临时占位符。当一条语句在prepare之后，尚未执行之前，可以给这些占位符绑定指定的值。
　　参数符号（Parameter Tokens）
　　 语句参数一共有5种类型，它们跟随SQL命令字符串一起被传入到sqlite3_prepare函数。
　　
（1）?
　　 一个自动索引的匿名参数，如果一条语句中含有多个“?”语句参数，则它们被隐式的赋予索引1,2…。如：

[cpp]
view plaincopyprint?

• INSERTINTOpeople(id,name)VALUES(?,?);
  

  INSERT INTO people (id, name) VALUES ( ?, ? );
　　 这两个“?”语句参数，分表代表id的值和name的值。需要注意的是SQL命令字符串中的?语句参数的书写，不要带单引号，'?'只是一个单字符文本值，并不是一个语句参数。当这条SQL命令字符串prepare之后，就可以给这两个“?”语句参数绑定合适的值，之后调用step函数执行语句。
　　
（2）?<index>
　　 具有显示数字索引的语句参数。“?<index>”与“?”相比，主要的优点是，在一条SQL命令字符串中可以有多个具有相同索引的问号语句参数，如一条SQL命令字符串中包含多个“?1”，这就允许在同一条语句中，在多个语句参数所占据的位置绑定相同的值。如：

[cpp]
view plaincopyprint?

• INSERTINTOpeople(pid,uid,name)VALUES(?1,?1,?2);
  

  INSERT INTO people (pid, uid, name) VALUES ( ?1, ?1, ?2 );
　　 “?<index>”的index值也可以不必连续。如：

[cpp]
view plaincopyprint?

• INSERTINTOpeople(pid,uid,name)VALUES(?1,?2,?4);
  

  INSERT INTO people (pid, uid, name) VALUES ( ?1, ?2, ?4 );
　　
（3）:<name> 如：

[cpp]
view plaincopyprint?

• INSERTINTOpeople(id,name)VALUES(:id,:name);
  

  INSERT INTO people (id, name) VALUES ( :id, :name );
　　 这种形式的语句参数，看起来非常直白。“:id”代表id的值，“:name” 代表name的值。
　　
（4）@<name>
　　 用法与“:<name>”类似。
　　 （5）$<name>
　　 这是用来支持Tcl变量的扩展语法，除非使用Tcl编程，否则推荐使用“:<name>”版本。
　　 以上5种类型的语句参数，在使用的时候选择其中一种，并始终使用它。最好不要在一条语句中穿插使用多种形式的语句参数，这样会造成视觉混淆。推荐使用“:<name>”版本，因为这种形式的语句参数看起来更直观。
　　绑定值（Binding Values）
　　
在一条带参数的语句prepare之后，step之前，可以给其中的每一个参数绑定一个指定的值。如果一条语句已经调用sqlite3_step函数执行了，那就不能再给这条语句中的参数绑定具体的值了，除非这条语句被重置。
　　 一共有如下9个bind函数，所有这些函数的第1个参数，第2个参数和返回值都是相同的。第一个参数是指向sqlite3_stmu结构体的指针，第2个参数是要绑定的参数索引值，记住索引值是从1（而不是0）开始的。第3个参数是要赋值给参数的绑定值。第4个参数（如果有的话），代表第三个参数“绑定值”的字节长度。第5个参数（如果有的话），它是一个指向内存管理回调函数的指针。所有的这些bind函数，如果执行成功则返回SQLITE_OK，否则返回一个整形错误码。


[cpp]
view plaincopyprint?

• intsqlite3_bind_blob(sqlite3_stmt*,int,constvoid*,intn,void(*)(void*));
  

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
　　 绑定一个任意长度的BLOB类型的二进制数据。（BLOB：二进制大对象，相当于一个可以存储大量二进制数据的容器。）


[cpp]
view plaincopyprint?

• intsqlite3_bind_double(sqlite3_stmt*,int,double);
  

int sqlite3_bind_double(sqlite3_stmt*, int, double);
　　 绑定一个64位浮点值。


[cpp]
view plaincopyprint?

• intsqlite3_bind_int(sqlite3_stmt*,int,int);
  

int sqlite3_bind_int(sqlite3_stmt*, int, int);
　　 绑定一个32位有符号整型值。


[cpp]
view plaincopyprint?

• intsqlite3_bind_int64(sqlite3_stmt*,int,sqlite3_int64);
  

int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
　　 绑定一个64位有符号整型值。


[cpp]
view plaincopyprint?

• intsqlite3_bind_null(sqlite3_stmt*,int);
  

int sqlite3_bind_null(sqlite3_stmt*, int);
　　 绑定NULL。


[cpp]
view plaincopyprint?

• intsqlite3_bind_text(sqlite3_stmt*,int,constchar*,intn,void(*)(void*));
  

int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
　　 绑定一个任意长度的UTF-8编码的文本值，第4个参数是字节长度，注意不是字符长度。如果给第4个参数传递负值，SQlite就会自动计算绑定值的字节长度（不包括NULL结尾符）。


[cpp]
view plaincopyprint?

• intsqlite3_bind_text16(sqlite3_stmt*,int,constvoid*,int,void(*)(void*));
  

int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
　　 绑定一个任意长度的UTF-16编码的文本值，第4个参数是字节长度，注意不是字符长度。如果给第4个参数传递负值，SQlite就会自动计算绑定值的字节长度（不包括NULL结尾符）。


[cpp]
view plaincopyprint?

• intsqlite3_bind_zeroblob(sqlite3_stmt*,int,intn);
  

int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
　　 绑定一个任意长度的BLOB类型的二进制数据，它的每一个字节被置0。第3个参数是字节长度。这个函数的特殊用处是，创建一个大的BLOB对象，之后可以通过BLOB接口函数进行更新。


[cpp]
view plaincopyprint?

• intsqlite3_bind_value(sqlite3_stmt*,int,constsqlite3_value*);
  

int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
　　绑定sqlite3_value结构体类型的值，sqlite3_value结构体可以保存任意格式的数据。
　　 对于text和BLOB类型的bind函数，绑定值传递的是一个buffer指针。通常这个buffer指针一定要保证有效，直到该语句参数绑定了一个新值，或者语句被finalize销毁。对于这两类bind函数的第5个参数是对这个buffer的一个控制。
　　 如果第5个参数传递NULL或者SQLITE_STATIC常量，则SQlite会假定这块buffer是静态内存，或者客户应用程序会小心的管理和释放这块buffer，所以SQlite放手不管。
　　 如果第5个参数传递的是SQLITE_TRANSIENT常量，则SQlite会在内部复制这块buffer的内容。这就允许客户应用程序在调用完bind函数之后，立刻释放这块buffer（或者是一块栈上的buffer在离开作用域之后自动销毁）。SQlite会自动在合适的时机释放它内部复制的这块buffer。
　　 对于第5个参数的最后一种选择是传递一个有效的“void mem_callback(void *ptr)”函数指针。当SQlite使用完这块buffer并打算释放它的时候，第5个参数传递的函数指针所指向的函数将会被调用。比如这块buffer是由sqlite3_malloc函数或者sqlite3_realloc函数分配的，则可以直接传递sqlite3_free函数指针给bind函数的第5个参数。如果是由其它系列的内存管理函数分配的内存，则应该传递其相应的内存释放函数。
　　 针对bind函数使用的索引值，有下面3个非常有用的函数。


[cpp]
view plaincopyprint?

• intsqlite3_bind_parameter_count(sqlite3_stmt*);
  

int sqlite3_bind_parameter_count(sqlite3_stmt*);
　　 返回一个整数，指明一条语句中所使用的参数的最大索引值。


[cpp]
view plaincopyprint?

• intsqlite3_bind_parameter_index(sqlite3_stmt*stmt,constchar*name)
  

int sqlite3_bind_parameter_index( sqlite3_stmt *stmt, const char *name )
　　 返回一个命名参数（如：":pid"）的索引值。注意这第2个参数是UTF-8编码的，即使针对UTF-16编码的语句，第2个参数也要以UTF-8编码的字符串赋值。如果没有找到匹配名字的参数，该函数返回0。如：

[cpp]
view plaincopyprint?

• sqlite3_bind_int(stmt,sqlite3_bind_parameter_index(stmt,":pid"),pid);
  

  sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(stmt, ":pid"), pid);

[cpp]
view plaincopyprint?

• constchar*sqlite3_bind_parameter_name(sqlite3_stmt*stmt,intpidx)
  

const char* sqlite3_bind_parameter_name( sqlite3_stmt *stmt, int pidx )
　　 返回指定索引参数的文本名称，以UTF-8编码。


[cpp]
view plaincopyprint?

• intsqlite3_clear_bindings(sqlite3_stmt*stmt)
  

int sqlite3_clear_bindings( sqlite3_stmt *stmt )
　　 如果想清空一条语句中所有参数所绑定的值，调用sqlite3_clear_bindings函数，该函数调用之后，语句中所有参数都绑定NULL值。该函数总是返回SQLITE_OK。
　　 如果想确保绑定到参数的值，不会引起内存泄露。最好在每次重置语句时，清空所有参数绑定。
　　安全性和性能(Security and Performance)
　　 构造一条SQL命令字符串，并修改其中的某些值，除了使用上面的语句参数的方式，还有一种方法就是使用诸如c语言的字符串处理函数，如：


[cpp]
view plaincopyprint?

• snprintf(buf,buf_size,
  
• 
  "INSERTINTOpeople(id,name)VALUES(%d,'%s');",
  
• id_val,name_val);
  

  snprintf(buf, buf_size,         
"INSERT INTO people( id, name ) VALUES ( %d, '%s' );",         
id_val, name_val);
　　 假如为id_val, name_va作如下赋值：


[cpp]
view plaincopyprint?

• id_val=23;
  
• 
  name_val="Fred";
  

  id_val = 23;
name_val = "Fred";
　　 则得到的存储在buf中的SQL语句如下：

[cpp]
view plaincopyprint?

• INSERTINTOpeople(id,name)VALUES(23,'Fred');
  

  INSERT INTO people( id, name ) VALUES ( 23, 'Fred');
　　 那么使用语句参数的方式，和使用字符串处理函数的方式相比，有什么好处呢？主要有以下三点：
　　 （1） 使用“语句参数”方式，具有更高的安全性，可以有效防止“SQL注入攻击”。 “SQL注入攻击”要想达到目的，就必须让attack value随着SQL命令字符串一起传送进SQL解析器。黑客如果在一条SQL命令字符串被送入到sqlite3_prepare函数之前，利用c字符串处理函数等途径将attack
value注入其中，而在sqlite3_prepare函数之中进行解析（parse），就可以达到攻击目的。而使用“语句参数”方式，被传送到sqlite3_prepare函数的只是SQL命令字符串中的参数符号（如：“?”），而不是具体的值。在sqlite3_prepare函数执行之后，才会使用bind函数给参数符号绑定具体的值，这就可以避免attack
value随着SQL命令字符串一起在sqlite3_prepare函数中被解析，从而有效躲避“SQL注入攻击”。
　　 （2）使用“语句参数”方式，可以更快的完成值替换。
　　 （3）使用“语句参数”方式，更节省内存。原因是使用如snprintf函数，需要一个SQL命令模板，一块足够大的输出缓存，而且字符串处理函数需要工作内存（working
memory），除此之外对于整形，浮点型，特别是BLOBs，经常会占用更多的空间。
　　示例代码


[cpp]
view plaincopyprint?

• char*data="";/*defaulttoemptystring*/
  
• sqlite3_stmt*stmt=NULL;
  
• 
  intidx=-1;
  
• 
  /*...set"data"pointer...*/
  
• 
  /*...opendatabase...*/
  
• 
  
• 
  rc=sqlite3_prepare_v2(db,"INSERTINTOtblVALUES(:str)",-1,&stmt,NULL);
  
• 
  if(rc!=SQLITE_OK)exit(-1);
  
• 
  
• 
  idx=sqlite3_bind_parameter_index(stmt,":str");
  
• sqlite3_bind_text(stmt,idx,data,-1,SQLITE_STATIC);
  
• 
  
• rc=sqlite3_step(stmt);
  
• 
  if((rc!=SQLITE_DONE)&&(rc!=SQLITE_ROW))exit(-1);
  
• 
  
• sqlite3_finalize(stmt);
  
• 
  /*...closedatabase...*/
  

char *data = ""; /* default to empty string */
sqlite3_stmt *stmt = NULL;
int idx = -1;
/* ... set "data" pointer ... */
/* ... open database ... */
rc = sqlite3_prepare_v2( db, "INSERT INTO tbl VALUES ( :str )", -1, &stmt, NULL );
if ( rc != SQLITE_OK) exit( -1 );
idx = sqlite3_bind_parameter_index( stmt, ":str" );
sqlite3_bind_text( stmt, idx, data, -1, SQLITE_STATIC );
rc = sqlite3_step( stmt );
if (( rc != SQLITE_DONE )&&( rc != SQLITE_ROW )) exit ( -1 );
sqlite3_finalize( stmt );
/* ... close database ... */
　　 使用了参数绑定的方式，避免可能的“SQL注入攻击”。
　　潜在的陷阱（Potential Pitfalls）
　　（1）


[cpp]
view plaincopyprint?

• INSERTINTOmembership(pid,gid,type)VALUES(:pid,:gid,:type);
  

INSERT INTO membership ( pid, gid, type ) VALUES ( :pid, :gid, :type );
　　 这条SQL命令字符串在prepare之后，“:pid, :gid, :type”这三个参数全部绑定为NULL值。这条语句在执行之前，一定要给这三个参数绑定新的值。假如表membership的type这一列有默认值，那么有的程序员可能会有一个误解，假如上面这条语句在step执行时，参数“:type”绑定的值为NULL，那么最终插入到表membership的列type中的值，应该是该列的默认值。这种假设是错误的，实际插入的就是NULL，而不是该列的默认值。假如type列想插入默认值，正确的写法如下：


[cpp]
view plaincopyprint?

• INSERTINTOmembership(pid,gid)VALUES(:pid,:gid);
  

INSERT INTO membership ( pid, gid ) VALUES ( :pid, :gid );
　　（2）
　　
另一种容易引起误用的情况是与NULL值的比较。


[cpp]
view plaincopyprint?

• SELECT*FROMemployeeWHEREmanager=:manager;
  

SELECT * FROM employee WHERE manager = :manager;
　　
这条语句看起来可以很好的工作，但当参数“:manager”绑定NULL值的时候，这个查询操作将不会检索到任何数据，即使表中存在manager为NULL的行。如果需要manager列与NULL值进行比较，正确的写法如下：


[cpp]
view plaincopyprint?

• SELECT*FROMemployeeWHEREmanagerIS:manager;
  

SELECT * FROM employee WHERE manager IS :manager;
　　SQlite数据库的C编程接口（四）
绑定参数（Bound Parameters） by斜风细雨QQ：253786989 2012-02-05