当 field=0 时，mySQL 返回所有行

2021-11-20 00:00:00 where-clause select mysql

我正在做一些测试，当我查询一个表时，很惊讶，查询 SELECT * FROM table WHERE email=0 返回了表中的所有行.

I was making some tests, and it was a surprise when i was querying a table, and the query SELECT * FROM table WHERE email=0 returned all rows from the table.

此表没有0"值，并且填充了常规电子邮件.

This table has no '0' values and it's populated with regular e-mails.

为什么会这样?这可能会导致严重的安全问题.

Why this happens? This can lead to serious security problems.

有没有办法在不修改查询的情况下避免这种情况?

Is there a way to avoid this without modifying the query?

我在这里遗漏了什么吗?

Am i missing something here?

谢谢.

推荐答案

这是因为它将电子邮件字段(我假设它是一个 varchar 字段)转换为整数.任何没有有效整数的字段都等于 0.您应该确保只将字符串字段与字符串值进行比较(日期也是如此，与日期比较).查询应如下所示.

This is because it is converting the email field (which I assume is a varchar field) to an integer. Any field without a valid integer will equate to 0. You should make sure that you only compare string fields to string values (same goes for dates, comparing to dates). The query should be as follows.

SELECT * FROM table WHERE email='0';

相关文章