Searching for a string in RSQLite

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

-----Original Message-----
From: r-help-bounces at stat.math.ethz.ch 
[mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Duncan Murdoch
Sent: 25 November 2004 15:38
To: r-help at stat.math.ethz.ch
Subject: [R] Searching for a string in RSQLite


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

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.

You may find dQuote() and sQuote() to be helpful, but a better