sql - R 中的粘贴函数在构建 SQL 查询时不起作用

Question

下面的代码是在 R 中正常运行的 sql 查询。

s1 <- build_sql(" INSERT INTO DB_PUBLIC_ANNEX.JS06290.IPQ_TEST(PIN,WRNTY_CLM_SEQ_NUM) VALUES (123,456)")
TABLE <- sqlQuery(connection_string, s1)

但是当我对同一个查询使用如下所示的粘贴功能时，值没有被插入到数据库中。

s1 <- build_sql(paste("INSERT INTO DB_PUBLIC_ANNEX.JS06290.IPQ_TEST(PIN,WRNTY_CLM_SEQ_NUM) VALUES (",123,",",456,")"))
TABLE <- sqlQuery(connection_string, s1)

错误如下:

[1] "42000 27 Syntax error or access violation"                                                                                          
[2] "[RODBC] ERROR: Could not SQLExecDirect ''INSERT INTO DB_PUBLIC_ANNEX.JS06290.IPQ_TEST(PIN,WRNTY_CLM_SEQ_NUM) VALUES ( 123 , 456 )''"

Answer 1

在这种情况下，build_sql 试图通过引用您粘贴的内容来保护您免受 sql 注入(inject)。

>s1<-paste("SELECT CURRENT_DATE")
>s1
[1] "SELECT CURRENT_DATE"

>build_sql(s1)
<SQL> 'SELECT CURRENT_DATE'

注意字符串开头和结尾的单引号。这就是我们的绊脚石。

如果你想让粘贴结果中的文本被视为sql，你可以像这样将它包含在sql()中，但我不推荐它。

>build_sql(sql(s1))
<SQL> SELECT CURRENT_DATE

所以如果我们像这样建立 q1，我们会得到一个错误。这里我使用的是 RJDBC，但根本问题是一样的。

>col1=as.integer(123)
>col2=as.integer(456)
>q1=build_sql(paste("INSERT INTO TEST_TABLE (COL1 ,COL2 ) VALUES (",col1,",",col2,")"))
>q1
<SQL> 'INSERT INTO TEST_TABLE (COL1 ,COL2 ) VALUES ( 123 , 456 )'
>dbSendUpdate(con, q1)
Error in .local(conn, statement, ...) : 
  execute JDBC update query failed in dbSendUpdate (ERROR:  ''INSERT INTO TEST_TABLE (COL1 ,COL2 ) VALUES ( 123 , 456 )''
error                                                              ^ found "'" (at char 59) expecting a keyword
)

严格来说我们不需要在这里粘贴，如果我们想使用 build_sql，它实际上有点妨碍我们。

>q1=build_sql("INSERT INTO TEST_TABLE (COL1 ,COL2 ) VALUES (",col1,",",col2,")")
>q1
<SQL> INSERT INTO TEST_TABLE (COL1 ,COL2 ) VALUES (123,456)
>dbSendUpdate(con, q1)

你可以这样做，但它破坏了 build_sql 提供的有限的 sql 注入(inject)保护。

>q1=build_sql(sql(paste("INSERT INTO TEST_TABLE (COL1 ,COL2 ) VALUES (",col1,",",col2,")")))
>q1
<SQL> INSERT INTO TEST_TABLE (COL1 ,COL2 ) VALUES ( 123 , 456 )
>dbSendUpdate(con, q1)