2.1 Example – an apostrophe sign in data

Let us begin with a simple example illustrating how your own data can lead to problems similar to a SQL injections attack.

Imagine a simple SQL database with a table called cakes:

We receive a CSV data file containing the same database but with new prices. You are asked to update the database. So you write your R code as below:

library(RODBC)

connHandle <- odbcConnect("cakesDatabase")
newData <- read.csv("newData.csv", stringsAsFactors = F)

for(row in 1:nrow(newData)){
  query <- paste0(
    "UPDATE cakes 
     SET price = ", newData$price[row], " 
     WHERE cake = '", newData$cake[row], "'"
  )
  sqlQuery(connHandle, query)
}

odbcClose(connHandle)

Such a code will fail on a Kevin’s Cherry Tart because this name contains an apostrophe. The resulting query would be UPDATE cakes SET price = 12.3 WHERE cake = 'Kevin's Cherry Tart'; which is not a proper SQL query. To deal with the Kevin’s Cherry Tart we need to escape the apostrophe in the cake’s name so that the database knows that it doesn’t denote the end of the string value.

2.2 Example – simple SQL injection

There is a nice XKCD about that – see here. Let’s translate it into an example in R.

We have got a database of students with a table students

A new school year has begun and new students have come. We have just received a CSV file with the same structure as the table students and we are asked to add it to the database. So we prepare a simple script:

library(RODBC)

connHandle <- odbcConnect('studentsDatabase')
newStudents <- read.csv('newStudents.csv', stringsAsFactors = F)

for(row in 1:nrow(newStudents)){
  query <- paste0(
    "INSERT INTO students (first_name, last_name)
     VALUES (
       '", newStudents$first_name[row],"', 
       '", newStudents$last_name[row],"', 
     )"
  )
  sqlQuery(P, query)
}

odbcClose(connHandle)

Unfortunately one of our new students’ name is:

For this student our query would be:

INSERT INTO students (last_name, first_name)
  VALUES ('Smith', 'Robert'); DROP TABLE students; --')

These are in fact two SQL queries and one SQL comment:

• INSERT INTO students (last_name, first_name) VALUES ('Smith', 'Robert');
• DROP TABLE students;
• --')

Execution of such a query can lead to a serious data loss (hopefully we have made a backup copy or do not have sufficient rights to drop the students table). To avoid such problems we should properly escape parameters values in our SQL queries.

2.3 How to escape values in SQL queries?

At this point we already know that we should properly escape parameters values in our SQL queries. There are many techniques of doing that:

• Manually checking the data types.
• Using parameterized queries.
• Using high-level functions which escape values for us.

library(RODBCext)

connHandle <- odbcConnect("cakesDatabase")
newData <- read.csv("newData.csv", stringsAsFactors = F)

query <- "UPDATE cakes SET price = ? WHERE cake = ?"
for(row in 1:nrow(newData)){
  sqlExecute(connHandle, query, newData[i, ])
}

odbcClose(connHandle)

library(RODBCext)

connHandle <- odbcConnect("cakesDatabase")
newData <- read.csv("newData.csv", stringsAsFactors = F)

query <- "UPDATE cakes SET price = ? WHERE cake = ?"
sqlExecute(connHandle, query, newData)

odbcClose(connHandle)

2.4 Summary

When using SQL we must pay attention to escape query parameter values properly. The existing R database connectivity packages do not provide a completely reliable way of doing that. A set of SQL injections safe functions provides very limited functionality and more flexible functions are using naive escaping methods. That is why RODBCext is a preferred way to make your R code SQL injections safe.

I hope dplyr developers will switch to use parameterized queries internally at some point. This would provide R community with a brilliant and safe R to SQL mapper and to forget about a manual preparation of SQL queries.

3.1 Example – big insert

A typical scenario is an insert of many rows to a table:

library(RODBCext)
connHandle <- odbcConnect('EWD') # my sample ODBC database
data <- data.frame(1:10000, letters[rep(1:10, 1000)])

# Ordinary query - paste0() called in every loop
system.time({
  for(row in 1:nrow(data)){
    query <- paste0("INSERT INTO my_table VALUES (", data[row, 1], "'", data[row, 2],"')")
    sqlQuery(connHandle, query)
  }
})
#   user  system elapsed 
#  5.384   2.288  16.397

# Ordinary query - paste0() called only once
system.time({
  queries <- paste0(
    "INSERT INTO my_table VALUES (", data[, 1], "'", data[, 2],"')"
  )
  for(query in queries){
    sqlQuery(connHandle, query)
  }
})
#   user  system elapsed 
#  2.088   2.028   7.255 

# Parameterized query
system.time({
  sqlExecute(connHandle, "INSERT INTO my_table VALUES (?, ?)", data)
})
#   user  system elapsed 
#  0.300   0.232   3.935 
odbcClose(connHandle)

3.2 Example – speeding up a SELECT query

Also repeated execution of a SELECT query can benefit from using parameterized variant:

library(RODBCext)
connHandle <- odbcConnect('EWD') # my sample ODBC database

pupils = sqlQuery(
  connHandle, "SELECT id_obserwacji FROM obserwacje LIMIT 10000", 
  stringsAsFactors = F
)[, 1]

# Ordinary query - paste0() called in every loop
system.time({
  for(i in pupils){
    query <- paste0(
      "SELECT count(*) 
       FROM testy_obserwacje JOIN testy USING (id_testu) JOIN arkusze USING (arkusz) 
       WHERE id_obserwacji = ", pupils[i]
    )
    tmp <- sqlQuery(connHandle, query)
    # some other computations here
  }
})
#   user  system elapsed 
# 10.896   1.508  61.424 

# Ordinary query - paste0() called only once
system.time({
  queries <- paste0(
    "SELECT count(*) 
     FROM testy_obserwacje JOIN testy USING (id_testu) JOIN arkusze USING (arkusz) 
     WHERE id_obserwacji = ", pupils
  )
  for(query in queries){
    tmp <- sqlQuery(connHandle, query)
    # some other computations here
  }
})
#   user  system elapsed 
# 11.016   1.108  51.766 

# Parameterized query
system.time({
  query = "
    SELECT count(*) 
    FROM testy_obserwacje JOIN testy USING (id_testu) JOIN arkusze USING (arkusz) 
    WHERE id_obserwacji = ?"
  sqlPrepare(connHandle, query)
  for(i in pupils){
    tmp = sqlExecute(connHandle, NULL, pupils[i], fetch=T)
    # some other computations here
  }
})
#   user  system elapsed 
# 12.140   0.312  26.468

The longer query string, the more complicated query planning and the more query repetitions, the bigger amount of time can be saved.

4.1 RODBCext

RODBCext package tries to fill this gap by introducing parameterized queries support on the top of the RODBC package. RODBCext provides only two functions, both of them using database connection handlers from RODBC:

• sqlPrepare(connHandle, SQLquery, errors = TRUE)
• sqlExecute(connHandle, SQLquery, data, fetch = FALSE, errors = TRUE, ...)

library(RODBCext)
connHandle <- odbcConnect("myDatabase")

# good old RODBC call
data <- sqlQuery(connHandle, "SELECT * FROM myTable WHERE column = 'myValue'") 
# RODBCext equivalent
data <- sqlExecute(connHandle, "SELECT * FROM myTable WHERE column = ?", 'myValue', fetch = TRUE) 

odbcClose(connHandle)

library(RODBCext)
connHandle <- odbcConnect("myDatabase")

filterData <- data.frame('column1' = 1:5, column2 = c('a', 'b', 'c', 'd', 'e'))
data <- sqlExecute(connHandle, "SELECT * FROM myTable WHERE column1 = ? AND column2 = ?", filterData, fetch = TRUE)

odbcClose(connHandle)

library(RODBCext)
connHandle <- odbcConnect("myDatabase")

sqlExecute(connHandle, "SELECT * FROM myTable WHERE column = ?", 'myValue', fetch = FALSE)
data <- sqlGetResults(connHandle, max = 10) # fetch no more than 10 first rows
# data processing comes here
data <- sqlGetResults(connHandle) # fetch all other rows

odbcClose(connHandle)

library(RODBCext)
connHandle <- odbcConnect("myDatabase")

sqlExecute(
  connHandle, "SELECT * FROM myTable WHERE column = ?", 'myValue', 
  fetch = TRUE, stringsAsFactors = FALSE, dec = ",", max = 50, as.is = TRUE
)

odbcClose(connHandle)

library(RODBCext)
connHandle <- odbcConnect('myDatabase') 

sqlPrepare(connHandle, "SELECT * FROM myTable WHERE column = ?") # prepare query

# for some reason (e.g. resources limits) data must be processed sequentialy
foreach(i in observations){
  data = sqlExecute(connHandle, NULL, i$column, fetch=T)
  # data processing for a given observations goes here
}
odbcClose(connHandle)