php mysqli扩展之预处理，phpmysqli预处理

php mysqli扩展之预处理，phpmysqli预处理

　　在前一篇 mysqli基础知识中谈到mysqli的安装及基础操作（主要是单条sql语句的查询操作），今天介绍的是mysqli中很重要的一个部分：预处理。

　　在mysqli操作中常常涉及到它的三个主要类：MySQLi类，MySQL_STMT类，MySQLi_RESULT类。预处理主要是利用MySQL_STMT类完成的。

　　预处理是一种重要的 防止SQL注入的手段，对提高网站安全性有重要意义。

　　本文案例为 数据库名为test，数据表名为test，  字段有id ,title 两个，id自增长主键。

　

　　使用mysqli预处理执行插入操作：

<?<span>php 

</span><span>define</span>("HOST", "localhost"<span>);
</span><span>define</span>("USER", 'root'<span>);
</span><span>define</span>("PWD", ''<span>);
</span><span>define</span>("DB", 'test'<span>);

</span><span>$mysqli</span>=<span>new</span> Mysqli(HOST,USER,PWD,<span>DB);

</span><span>if</span> (<span>$mysqli</span>-><span>connect_errno) {
    </span>"Connect Error:".<span>$mysqli</span>-><span>connect_error;
}

</span><span>$mysqli</span>->set_charset('utf8'<span>);

</span><span>$id</span>=''<span>;
</span><span>$title</span>='title4'<span>;
</span><span>//</span><span>用？代替 变量</span>
<span>$sql</span>="INSERT test VALUES (?,?)"<span>;
</span><span>//</span><span>获得$mysqli_stmt对象，一定要记住传$sql，预处理是对sql语句的预处理。</span>
<span>$mysqli_stmt</span>=<span>$mysqli</span>->prepare(<span>$sql</span><span>);

</span><span>//</span><span>第一个参数表明变量类型，有i(int),d(double),s(string),b(blob)</span>
<span>$mysqli_stmt</span>->bind_param('is',<span>$id</span>,<span>$title</span><span>);

</span><span>//</span><span>执行预处理语句</span>
<span>if</span>(<span>$mysqli_stmt</span>-><span>execute()){
    </span><span>echo</span> <span>$mysqli_stmt</span>-><span>insert_id;
}</span><span>else</span><span>{
    </span><span>echo</span> <span>$mysqli_stmt</span>-><span>error;

}
</span><span>$mysqli</span>->close();

使用mysqli预处理防止sql注入：

<span>$id</span>='4'<span>;
</span><span>$title</span>='title4'<span>;

</span><span>$sql</span>="SELECT * FROM test WHERE id=? AND title=?"<span>;
</span><span>$mysqli_stmt</span>=<span>$mysqli</span>->prepare(<span>$sql</span><span>);
</span><span>$mysqli_stmt</span>->bind_param('is',<span>$id</span>,<span>$title</span><span>);

</span><span>if</span> (<span>$mysqli_stmt</span>-><span>execute()) {
    </span><span>$mysqli_stmt</span>-><span>store_result();
    </span><span>if</span>(<span>$mysqli_stmt</span>->num_rows()>0<span>){
        </span><span>echo</span> "验证成功"<span>;
    }</span><span>else</span><span>{
        </span><span>echo</span> "验证失败"<span>;
    }
}
    </span><span>$mysqli_stmt</span>-><span>free_result();
    </span><span>$mysqli_stmt</span>->close();

使用mysqli预处理执行查询语句：

<span>$sql</span>="SELECT id,title FROM test WHERE id>=?"<span>;

</span><span>$mysqli_stmt</span>=<span>$mysqli</span>->prepare(<span>$sql</span><span>);
</span><span>$id</span>=1<span>;

</span><span>$mysqli_stmt</span>->bind_param('i',<span>$id</span><span>);

</span><span>if</span>(<span>$mysqli_stmt</span>-><span>execute()){
    </span><span>$mysqli_stmt</span>-><span>store_result();<br />   //将一个变量绑定到一个prepared语句上用于结果存储
    </span><span>$mysqli_stmt</span>->bind_result(<span>$id</span>,<span>$title</span><span>);
    </span><span>while</span> (<span>$mysqli_stmt</span>-><span>fetch()) {
        </span><span>echo</span> <span>$id</span>.' :'.<span>$title</span>.'<br/>'<span>;
    }


}</span>

    更多mysqli技术请参见php官方手册，查手册是学习的最好方法~