使用SQL注入防护语法查询Books表返回空结果的问题

我有一个名为Books的表，包含id、visibility（整数类型）、config（JSON类型）字段。config字段的内容格式如下：

{"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注入时，无论传"true"、1还是true，都返回空结果：

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

问题核心是类型不匹配：JSON_EXTRACT返回的是JSON原生布尔类型值，而参数绑定传入的"true"、1或true会被转换成字符串/数值类型，和JSON布尔值无法匹配。

你可以用以下几种方式修复：

• 用JSON_TRUE()直接对比
  不需要传布尔参数，直接在SQL里使用JSON原生布尔常量：

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

• 将参数转换为JSON类型
  把传入的参数用CAST(? AS JSON)转换成JSON类型，确保类型匹配：

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

• 使用JSON_CONTAINS判断
  换用JSON_CONTAINS函数检查字段是否包含指定的JSON布尔值：

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

内容的提问来源于stack exchange，提问作者Akram