R help - Nov 2004 - 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

Single quotes in a string are escaped by putting two single quotes in
a row.  E.g., 

   state <- "mess: '' ("

Regards,

--
David

Duncan Murdoch wrote:
> 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
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html

> -----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
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list 
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
> 
The normal character for escaping the next character to prevent it being
interpreted in SQL (including SQLite) is the backslash (i.e. \). Unless, of
course, I'm not understanding the precise nature of your request.

Regards

Mike

> 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
solution might be to ask the developers of the RSQLite package to add
R functions that call the SQLite api functions sqlite_exec_printf()
and sqlite_get_table_printf() which automatically escape single quotes
(among other things, http://www.sqlite.org/c_interface.html)

Hadley