IN in SQL Query through textInput - Rshiny

shiny
1

Hello,
I am trying to build an application through R shiny which takes input from users and runs a SQL query behind and gives the output. The input I am taking is not a single value as in I should be able to take multiple IDs, pass then through "IN" in SQL query and get the output. Below is how it looks:

ui<- fluidPage(
  textInput(inputId = "id",label = "Input your IDs with single space between them"),
  actionButton(inputId = "submit", label = "Run"),
)

server <- function(input,output) {
  
  data <- eventReactive(input$submit, {
    
   dbGetQuery(pool,
               sqlInterpolate(ANSI(),
                              "select id, sum(XYZ)
                               from tableA
                               where id in (?id)
                               and date = '2018-04-30'
                               group by 1;",
                               id = gsub(" ","','",input$id) #replacing spaces with ','  which is in SQL format ex:'id1','id2'
                             )
    )

  })
  
    output$table <- renderDataTable({
              data()
            })
  
}

I am taking different IDs separated with spaces and running a query to get the output. This is working fine if I paste one ID in the input text field but is not working when I input multiple IDs. Can somebody please help?

2

Do you have a starting and ending quote when there's more than one id? If the input is id1 id2 your gsub will return id1','id2, not 'id1','id2'

1 Like
3

How is it working for single input then? I am assuming the quotes on either end are added by default.

I tried to paste the query to see how it is reading the data in server section -

  output$query <- renderTable({
    print(sqlInterpolate(ANSI(),
                         "select id, sum(XYZ)
                         from tableA
                         where id in (?id)
                         and date = '2018-04-30'
                         group by 1;",
                         id = gsub(" ","','",input$id)
    ))
  })

And the output shows - where id in ('42'',''43').

I don't understand why instead of reading single quote once, it is reading single quote twice (please note it is not reading double quotes but reading single quote twice even if I use ' instead of '

Thanks!

4

In this particular case I don't think you can use sqlInterpolate. The whole purpose of it is to protect your app from malicious SQL injections, by making sure whatever value you pass to it is not able to break out of being a single string or whatever. In your case, if the quotes were not doubled, what if input$id was set to '); DELETE FROM tableA; --?

You will need to split input$id yourself, call DBI::dbQuoteString on each value (or maybe it's vectorised), then paste the query together. This would also be a great feature request for DBI--to have sqlInterpolate support multiple values like this in a convenient way.