Loop with SQL insert not removing spaces

Question

I have a situation where I created a loop to makes multiple inserts. However, white space is giving an error when making the SQL statement. Example below:

The code below works

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

This does not work

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

I have tried to trim but is not working as well

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

What I am doing wrong?

Is there a reason you are brute-forcing data in the hard way, ignoring a function like `dbWriteTable` that does it more directly and faster? — r2evans, Aug 23 '21 at 16:42
Not this source specifically, but I know some of this stuff. But nevertheless first I need to find out why my loop is not getting it right. Then I can check other way. — Jonathan Livingston Seagull, Aug 23 '21 at 16:52

score 1 · Accepted Answer · answered Aug 23 '21 at 16:57

There are several things wrong with this approach.

Using dbSendQuery(con, "INSERT INTO ...") to insert data one row at a time. Create the data.frame first, then dbWriteTable it:
```
prod <- data.frame(PROCODIGO=c("LD1268","LD1269","LD1270"), TBPCODIGO=30084L)
dbAppendTable(con, "TBPPRODU", prod)
```
I think there should be more care crafting data into queries. SQL injection can be both malicious (e.g., XKCD's Exploits of a Mom aka "Little Bobby Tables") as well as completely accidental. One should never paste "data" into a query string. Options:
- Parameterized queries, see https://db.rstudio.com/best-practices/run-queries-safely/#parameterized-queries
- glue::glue_sql, which is better than paste since it takes steps to ensure the data is escaped properly.

Even if you want to use paste, realize that it is adding spaces with the default sep=" " argument.

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

I have changed collapse = "" for sep = "" and it worked. I will look into your advice to use dbAppendTable . Sounds simples and safer. Thanks you evans. — Jonathan Livingston Seagull, Aug 23 '21 at 17:05

Loop with SQL insert not removing spaces

1 Answers1