How to safely escape quotes in R for sending text in SQL?

Question

I have several rows in R that need to be written to a MySQL table using RODBC . The problem is that I don’t know how to process quotes inside strings so as not to get MySQL errors, but also not mess up the text?

I'm sure the solution is pretty simple, but for now I’m kind of stuck in my own thinking - I’m not out of the box ... avoiding without slipping, avoiding twice with two backslashes or better 4 (or 8 to be on the save side)

Example:

 require(RODBC) con <- odbcConnect("MyMySQLDatabase") string <- "What up?" query <- "INSERT INTO table (textcolumn) VALUES" value <- paste0( "('", string, "')" ) sql <- paste( query, value, ";") res <- sqlQuery(con, sql) 

(How to process the text that I read from files with possible single and double quotes?)

Answer 1

I would start by doubling the quotes:

 string <- gsub("'","''",string) 

This will double quotation marks:

 string [1] "What' up?" 

Answer 2

If RMySQL used , the dbEscapeStrings() function provided by the package is probably the best way. The function takes care to avoid following the standards set by MySQL. LINK

 require(RMySQL) mysql <- dbDriver("MySQL") con <- dbConnect( mysql, user="superuser", password="totallyawesomeandsavepassword", host="dbhost.hosthome.dom", dbname="awesome" ) dbEscapeStrings(con,"'''''fhf'''''''''rh'''''''''") # [1] "\\'\\'\\'\\'\\'fhf\\'\\'\\'\\'\\'\\'\\'\\'\\'rh\\'\\'\\'\\'\\'\\'\\'\\'\\'" 

RMySQL indicates that:

dbEscapeStrings Currently, only the MySQL driver implements this method.