切换到准备好的语句 [英] Switching to Prepared Statements
问题描述
我刚刚开始做我的第一个项目(很有趣).我正在学习PHP和MySQL，并且已经完成了我的第一个正常工作的应用程序.它可以工作，但是我现在正在学习如何保护我的应用程序的安全，从而防止SQL注入. 我大约有50多个PHP文件来管理与MySQL数据库的交互.它们都看起来像这样:
I have just started working on my first project (for fun). I am learning PHP and MySQL and have sort of completed my first working application. It works, but I now am learning how to secure my application and thus prevent SQL injections. I have roughly 50+ PHP files that manage interaction with my MySQL database. They all look something like this:
<?php
$inputvalues = $_POST;
$errors = false;
$result = false;
session_start();
$uid = $_SESSION['usr_id'];
$mysqli = new mysqli('localhost', "root", "", "testdb");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
foreach ($inputvalues as $key => $value) {
if(isset($value) && !empty($value)) {
$inputvalues[$key] = $mysqli->real_escape_string( $value );
} else {
$errors[$key] = 'The field '.$key.' is empty';
}
}
if( !$errors ) {
$addresult = "
SELECT a.firstnames, a.surname, a.schoolrole, a.datejoined FROM teachers a LEFT JOIN schools b ON a.schoolid = b.id WHERE b.id = '".$inputvalues['schoolid']."'
";
if( $result = $mysqli->query($addresult) ) {
while($row = $result->fetch_all())
{
$returnResult = $row;
}
}
}
mysqli_close($mysqli);
echo json_encode(['result' => $returnResult, 'errors' => $errors]);
exit;
?>
这是我在整个应用程序中用于向数据库读取数据或从数据库写入数据的格式.如果我需要将它们更改为准备好的语句，而不是在其中插入任何信息，而只是在检索它们，该如何处理?
This is the format I have used throughout my application for reading and writing data to/from the database. If I need to change them to prepared statements, where I am not inserting any information but just retrieving them, how would I go about it?
此外，如果我没有向数据库输入任何数据，它是否仍然易于注入?
Also, If I am not entering any data to the DB, is it still vulnerable to injection?
您能否请我提供一个示例，说明如何使当前代码适应准备好的语句，
Could you kindly provide me with an example on how I can adapt my current code to prepared statements, I would really appreciate it.
推荐答案
我遇到过同样的情况.我也使用了连接语句，然后将应用程序切换为准备好的语句.
I have been in the same situation. I was using concatenated statements too, then I switched my application to prepared statements.
坏消息:您将更改通过将客户端数据连接到SQL语句而构建的每个SQL语句，该语句几乎将是您50个源文件中包含的每个SQL语句.
the bad news is you are going to change every SQL statement built by concatenating client data to the SQL statement, which almost will be every SQL statement you have in your 50 source files.
好消息是从转换为准备好的语句所获得的好处是无价的，例如:
the good news is the gain from switching to prepared statements is priceless, for example:
1-您永远不必担心"SQL注入攻击"
1-you will never be worried about something called "SQL Injection attack"
php 手册说
如果应用程序专门使用 准备好的语句，开发人员可以确保没有SQL注入 将会发生(但是，如果正在构建查询的其他部分 使用未转义的输入，仍然可以进行SQL注入.
If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).
对我来说，这个理由-放心-足以支付更改我的源代码的费用. ，现在您的客户可以在表单名称字段robert; DROP table students; -- ;)中输入内容，这样您就不会再发生任何事情了
For me, that reason -peace of mind- is enough to pay the cost of changing my source code. , now your clients can type in a form name field robert; DROP table students; -- ;) and you feel safe that nothing is gonna happen
2-您无需再转义客户端参数.您可以在SQL语句中直接使用它们，例如:
2- you don't need to escape the client parameters anymore. you can directly use them in the SQL statement, something like :
$query = "SELECT FROM user WHERE id = ?";
$vars[] = $_POST['id'];
代替
$id = $mysqli->real_escape_string($_POST['id']);
$query = "SELECT FROM user WHERE id = $id";
这是在使用准备好的语句之前必须做的事情，这使您面临忘记作为正常人逃避一个参数的危险.攻击者破坏系统所需的一切只是1个未转义的参数.
which is something you had to do before using prepared statements, which was putting you in danger of forgetting to escape one parameter as a normal human being. and all it takes for an attacker to corrupt your system is just 1 unescaped parameter.
通常更改源文件总是有风险的，并且会很痛苦，尤其是在您的软件设计不佳以及没有明显的测试计划的情况下.但是我会告诉你我做了什么，以使其尽可能地容易.
typically changing the source files is always risky and has pain, especially if your software design is bad and if you don't have an obvious testing plan. but I will tell you what I did to make it as easier as possible.
我创建了一个函数，每个数据库交互代码都将使用该函数，因此以后您可以在一个地方更改所需的内容-该函数-您可以执行以下操作
I made a function that every database interaction code is going to use, so you can change what you want later in one place -that function- you can make something like this
class SystemModel
{
/**
* @param string $query
* @param string $types
* @param array $vars
* @param \mysqli $conn
* @return boolean|$stmt
*/
public function preparedQuery($query,$types, array $vars, $conn)
{
if (count($vars) > 0) {
$hasVars = true;
}
array_unshift($vars, $types);
$stmt = $conn->prepare($query);
if (! $stmt) {
return false;
}
if (isset($hasVars)) {
if (! call_user_func_array(array( $stmt, 'bind_param'), $this->refValues($vars))) {
return false;
}
}
$stmt->execute();
return $stmt;
}
/* used only inside preparedQuery */
/* code taken from: https://stackoverflow.com/a/13572647/5407848 */
protected function refValues($arr)
{
if (strnatcmp(phpversion(), '5.3') >= 0) {
$refs = array();
foreach ($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
}
现在，您可以在源文件中的任何位置使用此接口，例如，让我们更改问题中提供的当前SQL语句.让我们更改一下
Now, you can use this interface anywhere you want in your source files, for example let's change your current SQL statements you have provided in the question. Let us change this
$mysqli = new mysqli('localhost', "root", "", "testdb");
$addresult = "
SELECT a.firstnames, a.surname, a.schoolrole, a.datejoined
FROM teachers a LEFT JOIN schools b ON a.schoolid = b.id
WHERE b.id = '".$inputvalues['schoolid']."'";
if( $result = $mysqli->query($addresult) ) {
while($row = $result->fetch_all())
{
$returnResult = $row;
}
}
进入
$mysqli = new mysqli('localhost', "root", "", "testdb");
$sysModel = new SystemModel();
$addresult = "
SELECT a.firstnames, a.surname, a.schoolrole, a.datejoined
FROM teachers a LEFT JOIN schools b ON a.schoolid = b.id
WHERE b.id = ?";
$types = "i"; // for more information on paramters types, please check :
//https://php.net/manual/en/mysqli-stmt.bind-param.php
$vars = [];
$vars[] = $inputvalues['schoolid'];
$stmt = $sysModel->preparedQuery($addresult, $types, $vars, $mysqli);
if (!$stmt || $stmt->errno) {
die('error'); // TODO: change later for a better illustrative output
}
$result = $stmt->get_result();
$returnResult = [];
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$returnResult[] = $row;
}
此外，如果我没有向数据库输入任何数据，它是否仍然易于注入?
Also, If I am not entering any data to the DB, is it still vulnerable to injection?
是的，通过将错误的字符串连接到SQL语句来施加Sql Injection攻击.是INSERT，SELECT，DELETE，UPDATE.例如
Yes, Sql Injection attack is applied by concatenating bad string to your SQL statement. whither it is an INSERT, SELECT, DELETE, UPDATE. for example
$query = "SELECT * FROM user WHERE name = '{$_GET['name']}' AND password = '{$_GET['pass']}'"
类似的东西可能会被
// exmaple.com?name=me&pass=1' OR 1=1; --
这将导致一条SQL语句
which will result in a SQL statement
$query = "SELECT * FROM user WHERE name = 'me' AND password = '1' OR 1=1; -- '"
//executing the SQL statement and getting the result
if($result->num_rows){
//user is authentic
}else{
//wrong password
}
// that SQL will always get results from the table which will be considered a correct password
祝您好运，将您的软件切换到准备好的语句，并记住，无论发生什么事情，您都可以放心使用SQL注入攻击，这值得您更改源文件
Good luck with switching your software to prepared statements, and remember that the peace of mind you are going to get from knowing that whatever happens, you are safe from SQL injection attacks is worth the cost of changing the source files
这篇关于切换到准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
