为什么MySQL允许在SELECT查询中进行不精确的匹配?

Here's the story. I'm testing doing some security testing (using zaproxy) of a Laravel (PHP framework) application running with a MySQL database as the primary store for data.

这是故事。我正在测试一个Laravel (PHP框架)应用程序的安全性测试(使用zaproxy)，它使用MySQL数据库作为数据的主要存储。

Zaproxy is reporting a possible SQL injection for a POST request URL with the following payload:

Zaproxy正在报告一个POST请求URL的SQL注入，其有效负载如下:

id[]=3-2&enabled[]=on

Basically, it's an AJAX request to turn on/turn off a particular feature in a list. Zaproxy is fuzzing the request: where the id value is 3-2, there should be an integer - the id of the item to update.

基本上，打开/关闭列表中的某个特性是一个AJAX请求。Zaproxy正在模糊处理请求:如果id值为3-2，则应该有一个整数——要更新的项的id。

The problem is that this request is working. It should fail, but the code is actually updating the item where id = 3.

问题是这个请求是有效的。它应该会失败，但是代码实际上是在更新id = 3的项。

I'm doing things the way I'm supposed to: the model is retrieved using Eloquent's Model::find($id) method, passing in the id value from the request (which, after a bit of investigation, was determined to be the string "3-2"). AFAIK, the Eloquent library should be executing the query by binding the ID value to a parameter.

我按照我应该的方式来做:模型是使用validmodel::find($id)方法检索的，它从请求中传递id值(经过一点调查，这个值被确定为字符串“3-2”)。AFAIK，有说服力的库应该通过将ID值绑定到一个参数来执行查询。

I tried executing the query using Laravel's DB class with the following code:

我尝试使用Laravel的DB类执行查询，代码如下:

$result = DB::select("SELECT * FROM table WHERE id=?;", array("3-2"));

and got the row for id = 3.

得到id = 3的行。

Then I tried executing the following query against my MySQL database:

然后我尝试对我的MySQL数据库执行以下查询:

SELECT * FROM table WHERE id='3-2';

and it did retrieve the row where id = 3. I also tried it with another value: "3abc". It looks like any value prefixed with a number will retrieve a row.

它确实检索了id = 3的行。我还尝试了另一个值:“3abc”。看起来任何以数字为前缀的值都将检索一行。

So ultimately, this appears to be a problem with MySQL. As far as I'm concerned, if I ask for a row where id = '3-2' and there is no row with that exact ID value, then I want it to return an empty set of results.

最终，这似乎是MySQL的问题。就我而言，如果我要求一个id = '3-2'的行，并且没有一个行具有准确的id值，那么我希望它返回一个空的结果集。

I have two questions:

我有两个问题:

Is there a way to change this behaviour? It appears to be at the level of the database server, so is there anything in the database server configuration to prevent this kind of thing?

有办法改变这种行为吗?它似乎位于数据库服务器的级别，那么数据库服务器配置中有什么可以防止这种情况发生吗?
This looks like a serious security issue to me. Zaproxy is able to inject some arbitrary value and make changes to my database. Admittedly, this is a fairly minor issue for my application, and the (probably) only values that would work will be values prefixed with a number, but still...

在我看来，这是一个严重的安全问题。Zaproxy能够注入一些任意值并对我的数据库进行修改。不可否认，对于我的应用程序来说，这是一个相当小的问题，而且(可能)唯一的值将会是用一个数字前缀的值，但是仍然…

4 个解决方案

#1

SELECT * FROM table WHERE id= ? AND ? REGEXP "^[0-9]$";

This will be faster than what I suggested in the comments above. Edit: Ah, I see you can't change the query. Then it is confirmed, you must sanitize the inputs in code. Another very poor and dirty option, if you are in an odd situation where you can't change query but can change database, is to change the id field to [VAR]CHAR.

这将比我在上面评论中建议的要快。编辑:啊，我看到你不能更改查询。然后确认，您必须对代码中的输入进行清理。另一个非常糟糕的选项是将id字段改为[VAR]CHAR。

#2

I believe this is due to MySQL automatically converting your strings into numbers when comparing against a numeric data type.

我相信这是由于MySQL在与数字数据类型比较时自动将字符串转换成数字。

https://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

mysql> SELECT 1 > '6x';

选择1个> '6x';

-> 0

- > 0

mysql> SELECT 7 > '6x';

mysql>选择7 > '6x';

-> 1

- > 1

mysql> SELECT 0 > 'x6';

选择0 > 'x6';

-> 0

- > 0

mysql> SELECT 0 = 'x6';

选择0 = 'x6';

-> 1

- > 1

You want to really just put armor around MySQL to prevent such a string from being compared. Maybe switch to a different SQL server.

你需要在MySQL周围设置护甲来防止这样的字符串被比较。可能切换到另一个SQL服务器。

#3

Without re-writing a bunch of code then in all honesty the correct answer is

不重写一堆代码，那么诚实地说，正确的答案是

This is a non-issue

这是一个问题

Zaproxy even states that it's possibly a SQL injection attack, meaning that it does not know! It never said "umm yeah we deleted tables by passing x-y-and-z to your query"

Zaproxy甚至表示可能是SQL注入攻击，这意味着它不知道!它从来没有说"嗯，是的，我们通过传递x-y- z到你的查询来删除表"

// if this is legal and returns results
$result = DB::select("SELECT * FROM table WHERE id=?;", array("3"));

// then why is it an issue for this
$result = DB::select("SELECT * FROM table WHERE id=?;", array("3-2"));

// to be interpreted as
$result = DB::select("SELECT * FROM table WHERE id=?;", array("3"));

You are parameterizing your queries so Zaproxy is off it's rocker.

你正在参数化你的查询，所以Zaproxy关闭了它的摇杆。

#4

Here's what I wound up doing:

下面是我最后要做的:

First, I suspect that my expectations were a little unreasonable. I was expecting that if I used parameterized queries, I wouldn't need to sanitize my inputs. This is clearly not the case. While parameterized queries eliminate some of the most pernicious SQL injection attacks, this example shows that there is still a need to examine your inputs and make sure you're getting the right stuff from the user.

首先，我怀疑我的期望有点不合理。我原以为，如果使用参数化查询，就不需要对输入进行消毒。显然情况并非如此。虽然参数化查询消除了一些最有害的SQL注入攻击，但这个示例显示仍然需要检查输入并确保从用户那里获得正确的内容。

So, with that said... I decided to write some code to make checking ID values easier. I added the following trait to my application:

所以,说……我决定编写一些代码，以便更容易地检查ID值。我在申请中增加了以下特点:

trait IDValidationTrait
{
    /**
     * Check the ID value to see if it's valid
     *
     * This is an abstract function because it will be defined differently
     * for different models. Some models have IDs which are strings,
     * others have integer IDs
     */
    abstract public static function isValidID($id);

    /**
     * Check the ID value & fail (throw an exception) if it is not valid
     */
    public static function validIDOrFail($id)
    {
        ...
    }

    /**
     * Find a model only if the ID matches EXACTLY
     */
    public static function findExactID($id)
    {
        ...
    }

    /**
     * Find a model only if the ID matches EXACTLY or throw an exception 
     */
    public static function findExactIDOrFail($id)
    {
        ...
    }
}

Thus, whenever I would normally use the find() method on my model class to retrieve a model, instead I use either findExactID() or findExactIDOrFail(), depending on how I want to handle the error.

因此，每当我通常在模型类上使用find()方法来检索模型时，我都会使用findExactID()或findExactIDOrFail()，这取决于我如何处理错误。

Thank you to everyone who commented - you helped me to focus my thinking and to understand better what was going on.

谢谢大家的评论——你们帮助我集中思想，更好地理解了发生了什么。

#1

SELECT * FROM table WHERE id= ? AND ? REGEXP "^[0-9]$";

#2