使用R中的RMariaDB和池清理发送到MariaDB的多个JSON字符串

Question

我目前正在构建一个(大型)调查，需要将人们提供的答复发送到数据库。我已经使用pool和RMariaDB包建立了我的数据库连接，并且我编写了以下函数来构造SQL查询并提交我的数据(数据使用SSL证书进行保护，所有这些信息都通过列表db_config传递)。

save_db <- function (db_pool, x, db_name, db_config, replace_val) {
  # Construct the DB query to be sent to the database
  if (!replace_val) {
    query <- sprintf(
      "INSERT INTO %s (%s) VALUES ('%s')",
      db_name,
      paste(names(x), collapse = ", "),
      paste(x, collapse = "', '")
    )
  } else {
    query <- sprintf(
      "UPDATE %s SET %s WHERE %s;",
      db_name,
      paste(paste0(names(x)[-1], " = \'", x[-1], "\'"), collapse = ", "),
      paste0(names(x)[1], " = \'", x[1], "\'")
    )
  }

  # Submit the insert query to the database via the opened connection
  RMariaDB::dbExecute(db_pool, query)
}

db_pool是处理数据库连接的池对象；x是包含我发送到数据库的数据的命名向量，其中的名称对应于我的MariaDB的列名，值存储为data blobs；db_name是我的数据库的名称；replace_val是布尔值。

数据斑点本质上是与调查不同的输出对象，例如响应的向量或矩阵，使用jsonlite包中的toJSON()转换为字符串。

到现在为止还好。我能够将数据发送到数据库，下载数据并使用fromJSON()命令重建响应。一切都很好。然而，我确实有一个安全问题。在我的调查中，我确实有一些开放式的问题，人们可以写他们想要的东西。虽然可能性不大，但我担心有人可能会使用SQL注入攻击。最坏的情况，我会丢失我所有的数据。

我从DBI包中知道了sqlInterpolate()函数。据我所知，该函数转义了任何引号，这意味着提交的任何值都将被转换为安全字符串。

我不能做的是修改我上面的函数来使用sqlInterpolate。在我的例子中，x是一个长度为7的命名向量，其中每个向量元素都是一个JSON字符串。实际上，我需要对每个JSON字符串使用sqlInterpolate()。我想知道是否有一种“简单”的方法来做这件事，或者我最好的做法是完全重写我的函数，向DB发送七个单独的存款，即每个向量元素一个。

一个相当简单的示例如下所示：

library(jsonlite)

# Create some data to test the string on
y <- 1:3
z <- matrix(runif(4), 2, 2)
q <- c("one", "don't")
x <- c(toJSON(y), toJSON(z), toJSON(q))
names(x) <- c("var_1", "var_2", "var_3")
db_name <- "my_db"

# Current sprintf() statement
sprintf(
  "INSERT INTO %s (%s) VALUES ('%s')",
  db_name,
  paste(names(x), collapse = ", "),
  paste(x, collapse = "', '")
)

我需要插入的是('%s')在sprintf()语句中捕获的值(对于update查询也是如此)。因为我相当确定，只要将所有内容转换为JSON字符串，就可以净化我的DB输入？

任何帮助都将不胜感激。

Answer 1

今天，我花了几个小时尝试这个问题，但失败了，我相信我设法找到了一个替代方案。我已经做了一些测试，它似乎正在工作。我正在张贴我自己的问题的答案，以防有人在不同的时间遇到类似的问题。

我更新的函数现在看起来像这样：

save_db <- function (db_pool, x, db_name, db_config, replace_val) {
  # Interpolate the elements of x
  x <- do.call(c, lapply(x, function(y) {
    sql <- "?value"
    sqlInterpolate(db_pool, sql, value = y)
  }))

  # Construct the DB query to be sent to the database
  if (!replace_val) {
    query <- sprintf(
      "INSERT INTO %s (%s) VALUES (%s)",
      db_name,
      paste(names(x), collapse = ", "),
      paste(x, collapse = ", ")
    )

  } else {
    query <- sprintf(
      "UPDATE %s SET %s WHERE %s;",
      db_name,
      paste(paste0(names(x)[-1], " = ", x[-1]), collapse = ", "),
      paste0(names(x)[1], " = ", x[1])
    )
  }

  # Submit the insert query to the database via the opened connection
  RMariaDB::dbExecute(db_pool, query)
}

关键似乎是只在实际的JSON字符串本身上使用插值，如下所示：

  x <- do.call(c, lapply(x, function(y) {
    sql <- "?value"
    sqlInterpolate(db_pool, sql, value = y)
  }))

函数的其余部分可以按原样使用。要了解这一点，让我们使用我在原始问题中提供的示例：

y <- 1:3
z <- matrix(runif(4), 2, 2)
q <- c("one", "don't")
x <- c(toJSON(y), toJSON(z), toJSON(q))
names(x) <- c("var_1", "var_2", "var_3")
db_name <- "my_db"

# Current sprintf() statement
sprintf(
  "INSERT INTO %s (%s) VALUES ('%s')",
  db_name,
  paste(names(x), collapse = ", "),
  paste(x, collapse = "', '")
)

这将产生输出：

"INSERT INTO my_db (var_1, var_2, var_3) VALUES ('[1,2,3]', '[[0.6573,0.1726],[0.3291,0.9903]]', '[\"one\",\"don't\"]')" 

如果我现在像上面那样转换x并使用更新的sprintf()调用(请注意，多余的单引号已被删除)：

x <- do.call(c, lapply(x, function(y) {
  sql <- "?value"
  sqlInterpolate(ANSI(), sql, value = y)
}))

sprintf(
  "INSERT INTO %s (%s) VALUES (%s)",
  db_name,
  paste(names(x), collapse = ", "),
  paste(x, collapse = ", ")
)

我将得到：

"INSERT INTO my_db (var_1, var_2, var_3) VALUES ('[1,2,3]', '[[0.6573,0.1726],[0.3291,0.9903]]', '[\"one\",\"don''t\"]')"

我们可以看到，don't中的单引号被正确地引起来了。如果我在自己的解决方案中遗漏了一些重要的东西，请随时发表评论。