0512 作业

1. pdo预处理机制在防sql注入的作用?

$sql = “select from users where username=’{$user}’ and password=’{$pwd}’”
假定用户输入的帐号为’ or 1=1 #及密码任意
PDO会拼接字符串结果如下:
“select from users where username=’’ or 1=1 #’ and password=’qetad’”
条件被修改为username=’’ or 1=1这个结果为真, 密码被#注释了,导致无需用户名和密码就能通过执行.

$dsn = "mysql:host=127.0.0.1;dbname=user;";
$username = "root";
$password = "root123";
$pdo = new PDO($dsn, $username, $password);
//假定用户输入的帐号为' or 1=1 #及密码任意
$user = "' or 1=1 #";
$pwd = "qetad";
$sql = "select * from `users` where `username`='{$user}' and `password`='{$pwd}'";
//rowCount影响行数
echo $stmt->rowCount(); //输出 104

当使用prepare时,prepare语句服务器发送一条sql给mysql服务器，mysql服务器会解析这条sql。
excute语句会把绑定的参数当做纯参数赋值给prepare。哪怕参数中有sql命令也不会被执行，从而实现防治sql注入。

$dsn = "mysql:host=127.0.0.1;dbname=user;";
$username = "root";
$password = "root123";
$pdo = new PDO($dsn, $username, $password);
//假定用户输入的帐号为' or 1=1 #及密码任意
$user = "' or 1=1 #";
$pwd = "qetad";
$sql = "select * from `users` where `username`=:user and `password`=:pwd";
//使用prepare
$stmt = $pdo->prepare($sql);
$stmt->bindParam('user',$user);
$stmt->bindParam('pwd', $pwd);
$stmt->execute();
//rowCount影响行数
echo $stmt->rowCount();  // 0

2. pdo curd预处理? 扩展:pdo 预处理中bindValue与bindParam的不同之处有哪些?

bindValue
PDOStatement::bindValue ($param, $value, $type = PDO::PARAM_STR) bool
绑定一个值到用作预处理的 SQL 语句中的对应命名占位符或问号占位符
bindParam
PDOStatement::bindParam ($param, &$var, $type = PDO::PARAM_STR, $maxLength = null, $driverOptions = null) bool
绑定一个PHP变量到用作预处理的SQL语句中的对应命名占位符或问号占位符。不同于 PDOStatement::bindValue() ，此变量作为引用被绑定，并只在 PDOStatement::execute() 被调用的时候才取其值。
bindParam不能绑定具体的数值.

$dsn = "mysql:host=127.0.0.1;dbname=user;";
$username = "root";
$password = "root123";
$pdo = new PDO($dsn, $username, $password);
// 使用问号点位符
$sql1 = "select * from `users` where `id`=:id";
$stmt1 = $pdo->prepare($sql1);
$id = 1;
$stmt1->bindValue('id',$id);
$stmt1->bindParam('id',$id);
$stmt1->execute();
// 使用命名点位符
$sql2 = "select * from `users` where `id`=?";
$stmt2 = $pdo->prepare($sql1);
$stmt2->bindValue(1,1);
$stmt2->bindParam(1,1);  //报错
$stmt2->execute();