MySQL预编译（参数化查询）入门指南

MySQL预编译（参数化查询）是指在执行SQL语句时，把传入的参数用问号来代替，这样一来传入的参数就不会被当作SQL语句的一部分，从而可以避免遭受SQL注入漏洞的攻击。再者，同样的SQL语句只需要编译一次，后续只需要传入不同的值即可，也可以减少服务器的开支，提升性能。

MySQLi和PDO都支持预编译查询；下面代码以MySQLi为例：

<?php
$mysqli = new mysqli("localhost", "root", "123456", "stmt_db");
$mysqli->set_charset("utf8");

$title = "标题标题".mt_rand(1000,9999);
$addtime = time();

if($stmt = $mysqli->prepare("insert into stmt_table (title,addtime) values (?,?)")){

    $stmt->bind_param("si", $title,$addtime);
    if($stmt->execute()){
		$id = $stmt->insert_id;
		echo 'insert ok ID:',$stmt->insert_id,'<br />';
	}else{
		echo 'insert err';
	}
    $stmt->close();
	
}else{
	echo 'insert prepare err';
}

$title = mt_rand(1000,9999)."标题标题";
$id = $id;

if($stmt = $mysqli->prepare("update stmt_table set title = ? where id = ?")){

    $stmt->bind_param("si", $title,$id);
    if($stmt->execute()){
		echo 'update ok ID:',$id,'<br />';
	}else{
		echo 'update err<br />';
	}
    $stmt->close();
	
}else{
	echo 'update prepare err<br />';
}

$title = '标题';
$addtime = '16';

if($stmt = $mysqli->prepare("select title,addtime from stmt_table where title like concat('%',?,'%') and addtime like concat(?,'%') order by id desc")){

    $stmt->bind_param("si", $title,$addtime);
    if($stmt->execute()){
		$stmt->store_result();
		$stmt->bind_result($tit, $tim);

		while ($stmt->fetch()) {
			echo $tit,'，',$tim,'<br />';
		}
		echo $stmt->num_rows;
	}else{
		echo 'select err<br />';
	}
    $stmt->close();
}else{
	echo 'select prepare err<br />';
}

$mysqli->close();