Loop with SQL insert not removing spaces

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?

Solution

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