How to safely escape quotes in R to submit text to SQL?

Question

I have some strings in R which should be written into a MySQL table using RODBC. The problem is that I do not know how to handle quotes within the strings to get no MySQL errors but to also not mess up the text?

I am sure the solution is kind of simple but at the moment I am kind of stuck in my own thinking - I do not get out of the box ... escaping not escaping escaping twice with 2 backslashes or better 4 (or 8, just to be on the save side)

example:

require(RODBC)
con     <- odbcConnect("MyMySQLDatabase")

string  <- "What's up?"
query   <- "INSERT INTO table (textcolumn) VALUES"

value   <- paste0( "('", string, "')" )
sql     <- paste( query, value, ";")

res     <- sqlQuery(con, sql)

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

Answer 1

If one is using RMySQL the dbEscapeStrings() function provided by the package is probably the best way to go. The function takes care of escaping adhering to 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's manual states that:

dbEscapeStrings Currently, only the MySQL driver implements this method