使用PDO在MySQL中清理输入的正确方法 [英] Correct way to sanitize input in MySQL using PDO

问题描述

所以我有一个朋友尝试在我的网站上运行SQLinjection，他设法使用下面的代码进入该网站.我该如何预防呢?我已经读过一些有关清理变量的内容，但是我该怎么做呢?

so I had a friend of mine try to run a SQLinjection on my site and he managed to get into it using the code underneath. How can I prevent this? I have read something about sanitizing the variables but how do I do this?

';在登录名(用户名，密码)中插入INSERT INTO值('Gjertsmells'，'password')；在登录位置'x'='x

';INSERT INTO login (username, password) VALUES ('Gjertsmells', 'password');SELECT 'password' FROM Login WHERE 'x'='x

$db = new PDO('mysql:host=XXXXXXXX;dbname=XXXXXXX', 'XXXXXXXXXX', 'XXXXXXXXX');

        // query MySQL to verify login
        $query = $db->prepare("SELECT password FROM login WHERE username='$username'");
        $query->execute();

        $column = $query->fetchColumn();
        if($column === $password)

推荐答案

预处理语句的思想是不连接变量，而是绑定参数.不同之处在于，该变量永远不会插入到SQL中，而MySQL引擎会单独处理该变量，因此不会进行SQL注入.这也带来了额外的好处，那就是不需要对变量进行转义或预处理.

The idea of prepared statements is that you don't concatenate variables, instead you bind the parameters. The difference is the variable never gets inserted into the SQL, rather the MySQL engine handles the variable separately which leaves no possibility of SQL Injection. This also has the added bonus that no escaping or pre-processing of the variable is required.

$query = $db->prepare("SELECT password FROM login WHERE username = :username");
$query->execute(array(':username' => $username));

这篇关于使用PDO在MySQL中清理输入的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！