how to specify quotes when using sql query in R shiny

[chrisjacques13]

I'm trying to create a simple app to show how to write sql queries: users can type in their full sql queries and see the results.

Unfortunately I can't get the app to work if i add a string to the query, e.g. select * from iris where Species = 'setosa'

This brings the error: unrecognized token: "'"

Here's a repex:

library(shiny)
library(RSQLite)

db_example <- RSQLite::datasetsDb()

# check this works outside of shiny:
#dbGetQuery(db_example, "SELECT * FROM iris WHERE Species = 'setosa' ; ")

ui<- fluidPage(
  textInput(inputId = "sql_query",
            label = "write your sql query:"),
  actionButton(inputId = "submit", label = "Run"),
  tableOutput(outputId = "my_table")
  
)

server <- function(input,output) {
  
  my_result <- eventReactive(input$submit, {

    dbGetQuery(db_example,
               input$sql_query
               )
    
  })
    
  output$my_table <- renderTable({
    my_result()
  })
  
}


shinyApp(ui, server)

It works fine for just select * from iris but if you try adding a string condition such as
select * from iris where Species = 'setosa' it throws an error, even though the same syntax works fine outside of Shiny.

I've tried various syntax to get the quoting to work as expected with no luck (including escaping with )... Any ideas how this should be done?

Here's my session details:

R version 3.6.1 (2019-07-05)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS Mojave 10.14.6

packageVersion("shiny")
[1] ‘1.3.2’
packageVersion("RSQLite")
[1] ‘2.1.1’

[nirgrahamuk]

I haven't modified your code in any way, but when I test it , it works normally ...
I typed:

select * from iris where Species = 'versicolor'

and pressed run

Once you figure out your issue ( a ctrl-shift-f10 restart,might be worth a try..)
this could become useful, as exposing ability to write sql has its dangers:

Shiny - SQL injection prevention