mysql - SLEEP(x) 在 SQL 查询中的作用说明

Question

我不明白为什么会这样。有人可以解释一下吗？

SELECT * FROM users WHERE name = ''

按原样返回 0 条记录

SELECT * FROM users WHERE name = 'janet'

按原样返回 1 记录

SELECT * FROM users WHERE name = ''-SLEEP(3)

返回表中的EVERY记录，为什么？？

SLEEP(3) returns 0

使用 name = ''-0 产生相同的结果(返回每条记录)

这不是实际使用，因为我正在测试基于时间的 SQL 注入(inject)

Answer 1

documentation for the sleep function状态:

Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. If SLEEP() is interrupted, it returns 1. The duration may have a fractional part.

所以它返回一个整数，所以你实际得到的是'' - 0，这里''被隐式转换为一个整数所以这两种类型是可比较的，所以你有 0 - 0 等于 0，所以你的 where 子句实际上是:

WHERE Name = 0;

由于您是将字符串与整数进行比较，因此必须进行类型转换。以下来自MySQL Docs

The following rules describe how conversion occurs for comparison operations:

• If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
• If both arguments in a comparison operation are strings, they are compared as strings.
• If both arguments are integers, they are compared as integers.
• Hexadecimal values are treated as binary strings if not compared to a number.
• If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
• If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
• In all other cases, the arguments are compared as floating-point (real) numbers.

因为您有一个字符串参数和一个整数参数，所以它是最后一个子句。将字符串(不是数字)转换为 float 会导致错误 0，您可以使用如下简单的方法对其进行测试:

SELECT  CAST('A String' AS DECIMAL(10,5));

这会给你 0.00000。因此，发生转换后的最终 where 子句是:

WHERE 0 = 0;

因此返回所有行。