Paste function in R is not working in building SQL query

Question

Below code is normal sql query in R which is working fine.

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

But when I use the paste function like below for the same query ,the values are not getting inserted into the database.

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)

Below is Error :

[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

In this case build_sql is trying to protect you from sql injection by quoting the contents of your paste.

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

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

Notice the sinqle quotation marks at the beginning and end of the string. This is what is tripping us up.

If you want want the text in the results of paste to be treated as sql, you can enclose it in sql() like this, but I wouldn't recommend it.

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

So if we build q1 up like this, we get an error. Here I'm using RJDBC, but the fundamental problem is the same.

>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
)

We don't strictly need paste here, and it's actually getting a bit in our way if we want to use 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)

You could just do this, but it defeats the limited sql injection protection that build_sql affords.

>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)