mysql 使用和不使用SQL注入预防语法的不同查询结果

我有一个表Books，其中包含一些名为id、visibility和config的列。Visibility是一个整型列，config是json，其内容为以下形式

{"test": 
 {
  "test1": false, "ids": [1234, 567]}, 
  "visibility_options": {"all_people": true, "some_people": true}
}

当我运行下面的查询时，我得到了正确的预期结果：

select("DISTINCT(books.id), books.*")
.where([
  "
   books.visibility = 3
   AND
   JSON_EXTRACT(config, '$.visibility_options.all_people') = true
  "
])

当我运行下面的查询以避免SQL注入攻击时，我得到了一个空结果。

select("DISTINCT(books.id), books.*")
.where([
  "
   books.visibility = ?
   AND
   JSON_EXTRACT(config, '$.visibility_options.all_people') = ?
  ",
    3,
    "true"
])

避免SQL注入查询是否遗漏了什么？我尝试使用“true”作为1，true，但仍然得到相同的空结果。

您正在传递字符串值'true'作为参数值，但MySQL的true关键字不是字符串。
以下是区别的演示：

mysql> set @j = '{"test": ...}';

mysql> select JSON_EXTRACT(@j, '$.visibility_options.all_people');
+-----------------------------------------------------+
| JSON_EXTRACT(@j, '$.visibility_options.all_people') |
+-----------------------------------------------------+
| true                                                |
+-----------------------------------------------------+

mysql> select JSON_EXTRACT(@j, '$.visibility_options.all_people') = 'true';
+--------------------------------------------------------------+
| JSON_EXTRACT(@j, '$.visibility_options.all_people') = 'true' |
+--------------------------------------------------------------+
|                                                            0 |
+--------------------------------------------------------------+

mysql> select JSON_EXTRACT(@j, '$.visibility_options.all_people') = true;
+------------------------------------------------------------+
| JSON_EXTRACT(@j, '$.visibility_options.all_people') = true |
+------------------------------------------------------------+
|                                                          1 |
+------------------------------------------------------------+

使用JSON_EXTRACT（）时，返回值的数据类型为JSON，而不是字符串或布尔值。JSON值'true'不等于字符串值'true'

mysql> select cast('true' as json) = 'true';
+-------------------------------+
| cast('true' as json) = 'true' |
+-------------------------------+
|                             0 |
+-------------------------------+

但是如果使用JSON_UNQUOTE（），则可以将JSON值'true'转换为等效的字符串。

mysql> select JSON_UNQUOTE(JSON_EXTRACT(@j, '$.visibility_options.all_people')) = 'true';
+----------------------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(@j, '$.visibility_options.all_people')) = 'true' |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+

如果将值显式转换为字符串，也可以执行此操作。

mysql> select cast(cast('true' as json) as char) = 'true';
+---------------------------------------------+
| cast(cast('true' as json) as char) = 'true' |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

mysql> select CAST(JSON_EXTRACT(@j, '$.visibility_options.all_people') AS CHAR) = 'true';
+----------------------------------------------------------------------------+
| CAST(JSON_EXTRACT(@j, '$.visibility_options.all_people') AS CHAR) = 'true' |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+

老实说，在MySQL中使用JSON会让一切变得更困难，我避免使用它。

mysql 使用和不使用SQL注入预防语法的不同查询结果

1条答案

相关问题

热门标签

最新问答