一、SQL注入实例
$unsafe_variable = $_POST['user_input'];
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");
当POST的内容为:
value'); DROP TABLE table;--
以上的整个SQL查询语句变成:
INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')
二、防止SQL注入措施
1.Use prepared statements and parameterized queries.
SQL语句和查询的参数分别发送给数据库服务器进行解析。这种方式有2种实现:
(1)使用PDO(PHP data object)
(1)使用PDO(PHP data object)
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute(array('name' => $name));
foreach ($stmt as $row) {
// do something with $row
}
(2)使用MySQLi
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// do something with $row
}
2.对查询语句进行转义(最常见的方式)
$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);
mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");
Warning:
As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Please use mysqli extension and mysqli::escape_string function instead
$mysqli = new mysqli("server", "username", "password", "database_name");
// TODO - Check that connection was successful.
$unsafe_variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// TODO check that $stmt creation succeeded
// "s" means the database expects a string
$stmt->bind_param("s", $unsafe_variable);
$stmt->execute();
$stmt->close();
$mysqli->close();
3.限制引入的参数
$orders = array("name","price","qty"); //field names
$key = array_search($_GET['sort'],$orders)); // see if we have such a name
$orderby = $orders[$key]; //if not, first one will be set automatically. smart enuf :)
$query = "SELECT * FROM `table` ORDER BY $orderby"; //value is safe
4.对引入参数进行编码
SELECT password FROM users WHERE name = 'root' --普通方式
SELECT password FROM users WHERE name = 0x726f6f74 --防止注入
SELECT password FROM users WHERE name = UNHEX('726f6f74') --防止注入
set @INPUT = hex("%实验%");
select * from login where reset_passwd_question like unhex(@INPUT) ;
There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:
0x prefix can only be used on data columns such as char, varchar, text, block, binary, etc.
Also its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error.
UNHEX() works on any column; you do not have to worry about the empty string.
0x prefix can only be used on data columns such as char, varchar, text, block, binary, etc.
Also its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error.
UNHEX() works on any column; you do not have to worry about the empty string.
5.使用MySQL存储过程
其他:验证输入参数
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php
http://stackoverflow.com/questions/18026088/pdo-sends-raw-query-to-mysql-while-mysqli-sends-prepared-query-both-produce-the