sql - 使用 SQL 插入循环而不删除空格

Question

我有一种情况，我创建了一个循环来进行多次插入。但是，在制作 SQL 语句时，空格会产生错误。下面的例子：

下面的代码有效

query <- "INSERT INTO TBPPRODU (TBPCODIGO,PROCODIGO) VALUES (30084,'LD1268')"
dbSendQuery(con,query)

这不起作用

prod <- data.frame(PROCODIGO=c("LD1268","LD1269","LD1270")

x <- data.frame(PROCODIGO=NA)

for (i in 1:nrow(prod)) {
  x[i,] <- prod[i,]
  query <- paste("INSERT INTO TBPPRODU (TBPCODIGO,PROCODIGO) VALUES (30084,'",x[i,"PROCODIGO"],"')",collapse = "")
  dbSendQuery(con,query)
}

## the error output says there is white space before ' LD1268'
Error: nanodbc/nanodbc.cpp:1655: 23000: [ODBC Firebird Driver][Firebird]violation of FOREIGN KEY constraint "PRODU_TBPPRODU" on table "TBPPRODU"
Foreign key reference target does not exist
Problematic key value is ("PROCODIGO" = ' LD1268') 

我尝试修剪但效果不佳

query <- paste("INSERT INTO TBPPRODU (TBPCODIGO,PROCODIGO) VALUES (30084,'",x[trimws(i),"PROCODIGO"],"')",collapse = "")

我做错了什么？

标签： sqlrfirebirddbi

Answer 1

这种方法有几个问题。

1. 用于一次dbSendQuery(con, "INSERT INTO ...")插入一行数据。创建第data.frame一个，然后dbWriteTable它：

   prod <- data.frame(PROCODIGO=c("LD1268","LD1269","LD1270"), TBPCODIGO=30084L)
   dbAppendTable(con, "TBPPRODU", prod)
   

2. 我认为应该更加小心地将数据制作成查询。SQL 注入既可以是恶意的（例如，XKCD 的Exploits of a Mom又名“Little Bobby Tables”），也可以是完全偶然的。永远不 应该将paste“数据”放入查询字符串中。选项：

   • 参数化查询，请参阅https://db.rstudio.com/best-practices/run-queries-safely/#parameterized-queries
   • glue::glue_sql，这比paste它采取措施确保数据正确转义要好。

3. 即使您想使用paste，也要意识到它正在使用默认sep=" "参数添加空格。

   paste("VALUES (30084,'", "hello", "')")
   # [1] "VALUES (30084,' hello ')"                   # not right
   paste0("VALUES (30084,'", "hello", "')")
   # [1] "VALUES (30084,'hello')"
   paste("VALUES (30084,'", "hello", "')", sep = "")  # equivalent to paste0
   # [1] "VALUES (30084,'hello')"