如何在 PHP 中检查 sql 查询的完整性 [英] How to check for the sanity of sql query in PHP
问题描述
我正在用 PHP 编写 DbAdapter.为了避免 sql 注入攻击，对于条件选择，我需要一种方法来检查我将要运行的 SQL 查询的完整性.鉴于准备好的语句使实现变得非常复杂，在类的核心执行之前，是否有一种快速的方法来检查 sql 查询(WHERE 子句，特别是这里的情况)的完整性?例如，对于恶意或可疑查询返回 false 的辅助方法就可以了.
I am writing a DbAdapter in PHP. Trying to avoid sql injection attacks, for conditional selects, I need a way to check for the sanity of the SQL query that I am going to run. Given that prepared statements make the implementation very complicated, is there a quick way to check for the sanity of the sql query (WHERE clauses in particular as is the case here) before executing in the heart of the class? For example, a helper method to return false for malicious or suspicious queries will be fine.
我的班级代码:
require_once './config.php';
class DbAdapter
{
private $link;
/**
* DbAdapter constructor.
*/
public function __construct()
{
$this->link = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);
if ($this->link->connect_errno) {
die($this->link->connect_error);
}
}
/**
* @param $table
* @param array $columns
* @param string $condition
* @return bool|mysqli_result
*/
public function select($table, $columns = [], $condition = "")
{
$colsString = $this->extractCols($columns);
$whereString = $this->extractConditions($condition);
$sql = "SELECT $colsString FROM `$table` " . $whereString;
return $this->link->query($sql);
}
public function __destruct()
{
$this->link->close();
}
private function extractCols(array $columns)
{
if(!$columns) { return '*';}
else {
$str = "";
foreach($columns as $col) {
$str .= "$col,";
}
return trim($str, ',');
}
}
private function extractConditions(string $conditions)
{
if(!$conditions) {
return "";
}
else {
$where = "WHERE ";
foreach ($conditions as $key => $value){
$where .= "$key=" . $conditions[$key] . "&";
}
return trim($where, "&");
}
}
}
推荐答案
简答
您可以使用EXPLAIN，如EXPLAIN SELECT foo FROM table_bar.如何以编程方式解释结果以实现理智"，然而，这是一个更困难的问题.您将需要理智"的程序化定义，比如检查超过 n 行"；或涉及多个 t 个表."
Short Answer
You can use EXPLAIN, as in EXPLAIN SELECT foo FROM table_bar. How to interpret the results programmatically for "sanity," however, is a much more difficult question. You'll need a programmatic definition of "sanity," like "examines more than n rows" or "involves more than t tables."
您提到您的动机包括想要避免 sql 注入攻击".如果这就是您所担心的，那么这里最重要的事情是避免将任何 用户数据连接到查询中.如果您连接任何用户数据，SQL注入是可能的，而且很难被发现.完全阻止它会更好.
You mentioned that your motivation includes wanting to "avoid sql injection attacks." If that's what's worrying you, the most important thing here is to avoid concatenating any user data into a query. SQL injection is possible if you concatenate any user data, and it's very, very hard to detect. Much better simply to prevent it entirely.
坦率地说，这段代码让我毛骨悚然:
This code, frankly, makes my hair stand on end:
$where = "WHERE ";
foreach ($conditions as $key => $value){
$where .= "$key=" . $conditions[$key] . "&";
}
没有办法使其足够安全或对其进行足够的健全性检查.您可能会想，是的，但所有条件都应该只包含数字，"或类似的易于验证的东西，但您不能安全地依赖它.明年、下周或明天修改代码并添加字符串参数时会发生什么?即时漏洞.
There's no way to make that safe enough or to sanity-check it enough. You might think, "Yeah, but all of the conditions should contain only digits," or something similarly easy to validate, but you cannot safely rely on that. What happens when you modify your code next year, or next week, or tomorrow, and add a string parameter? Instant vulnerability.
您需要使用准备好的语句，而不是将变量连接到您的查询中.仅仅逃避你的变量是不够的.请参阅如何防止 PHP 中的 SQL 注入?.
You need to use prepared statements, rather than concatenating variables into your query. Simply escaping your variables is not enough. See How can I prevent SQL injection in PHP?.
请注意，这通常是您在将查询部署到生产之前执行的操作，而不是即时执行.如果您正在构建一个允许用户构建自己的查询的通行费，则可能不可避免地需要对查询进行一些即时评估.
Note that this is typically something you do before deploying queries to production, not on the fly. If you're building a toll that allows users to build their own queries, some on-the-fly evaluation of the queries may be unavoidable.
但是如果您处理的只是 WHERE 子句中的多个条件，那么查询将很快(并且您不需要使用 EXPLAIN)作为只要有两件事是真的:
But if all you're dealing with is multiple conditions in the WHERE clause, then queries will be fast (and you won't need to use EXPLAIN) as long as two things are true:
- 您不使用子查询，例如
... WHERE id IN (SELECT id from OtherTable WHERE ...) ...和 - 你有合适的索引.(同样，在 99% 以上的情况下，您可以在开发时预测到这一点.)
相关战争故事"希望能减轻您的一些恐惧
我曾经编写过一个工具，它允许在每个主要表中有几百万行的数据库上针对 MySQL 构建和运行各种复杂的查询.查询大多是简单的 WHERE 条件，例如 WHERE lastOrder >'2018-01-01'，以及一些(主要是硬编码的)JOIN 和子查询的可能性.我只是积极地索引，从不需要EXPLAIN任何东西；它从未真正遇到任何性能瓶颈.
Relevant "War Story" to Hopefully Ease Some of Your Fears
I once wrote a tool that allowed all kinds of complex queries to be built and run against MySQL on a database with several million rows in each of the major tables. The queries were mostly straightforward WHERE conditions, like WHERE lastOrder > '2018-01-01', along with a few (mostly hard-coded) JOIN and subquery possibilities. I just indexed aggressively and never needed to EXPLAIN anything; it never really hit any bottlenecks of performance.
这篇关于如何在 PHP 中检查 sql 查询的完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
