[R] Searching for a string in RSQLite

[Duncan Murdoch]

I'd like to search for a particular string in an SQLite database using
RSQLite, but I'm running into problems constructing the query
properly, because of embedded quotes and parens in the string.

Is there a function that escapes these for me, or some other fixup
that would let me do the queries below?  In the real situation I don't
have control over what strings get searched for.

Example based on ?SQLite:

> library(RSQLite)
> m <- dbDriver("SQLite")
> con <- dbConnect(m, dbname = "base.dbms")
> data(USArrests)
> dbWriteTable(con, "USArrests", USArrests, overwrite = T)
[1] TRUE
> state <- "Wyoming"

# this works fine:

> dbGetQuery(con, paste("SELECT * from USArrests where row_names='",state,"'",sep=""))
  row_names Murder Assault UrbanPop Rape
1   Wyoming    6.8     161       60 15.6

# Buf if the search string contains characters that SQL interprets, I
# get an error

> state <- "messy:  ' ("
> dbGetQuery(con, paste("SELECT * from USArrests where row_names='",state,"'",sep=""))
Error in sqliteExecStatement(con, statement) : 
        RS-DBI driver: (error in statement: near "(": syntax error)

Duncan Murdoch