Select SQL column to get in shiny

Question

shiny and SQL server are linked.
We succeeded in specifying search conditions by input from shiny.
Isn't it possible to select the column names to be obtained in this way in the output from shiny?

ui.R

shinyUI(
  fluidPage(
    selectInput("select","select", choices = c("CountryCode","District","NAME")),
    textInput("ID","ID"),
    actionButton("go", "go"),
    tableOutput("table"),
    tableOutput("tablee")
  )
)

server.R

shinyServer(function(input, output) {
  
  observeEvent(input$go,{
    output$table <- renderTable({
      sql <- 'SELECT ?select FROM City;'
      query <- sqlInterpolate(pool, sql, select = input$select)
      dbGetQuery(pool, query)
    })
  })
  
  output$tablee <- renderTable({
    sql <- "SELECT * FROM City WHERE ID = ?ID;"
    query <- sqlInterpolate(pool, sql, ID = input$ID)
    dbGetQuery(pool, query)
  })
  
})

global.R

library(shiny)
library(DBI)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)

Tim Biegeleisen · Accepted Answer · 2020-02-06T10:16:57.920

2

This answer covers your first query attempt:

observeEvent(input$go,{
    output$table <- renderTable({
        sql <- 'SELECT ?select FROM City;'
        query <- sqlInterpolate(pool, sql, select = input$select)
        dbGetQuery(pool, query)
    })
})

No, you can't do this, because prepared statements in SQL only can use placeholders for data, not for database objects (e.g. table and column names). You will have to use paste to build the query:

observeEvent(input$go,{
    output$table <- renderTable({
        sql <- paste("SELECT", input$select, "FROM City;")
        dbGetQuery(pool, sql)
    })
})

But note that this approach may be prone to SQL injection, assuming that input$select would be coming from the outside. A typical workaround to this problem would be to instead have some number of prepared statements ready, and then to select the appropriate one based on the input from the outside, for example:

observeEvent(input$go,{
    output$table <- renderTable({
        sql1 <- "SELECT name FROM City;"
        sql2 <- "SELECT state FROM City;"
        query <- ifelse(input$select == "name", sql1, sql2)
        dbGetQuery(pool, query)
    })
})

edited Feb 06 '20 at 10:16

answered Feb 06 '20 at 10:04

Tim Biegeleisen

502,043
27
286
360

This, it is quite common to store your pre-baked SQL statements in `global.R` or an external file to source in `server.R`. Although I don't fancy storing long static text blobs it is the safest options. – anddt Feb 06 '20 at 11:09
@anddt Note that having too many prepared statements in the database can also be a performance hindrance. – Tim Biegeleisen Feb 06 '20 at 12:30
@TimBiegeleisen Never got to the point where I had to store too many. How come is that? text snippets should be pretty lightweight to store/retrieve afaik. Am I missing something? – anddt Feb 06 '20 at 15:42
@anddt Prepared statements aren't actually evaluated in the client calling the database, they are evaluated on the database itself. The database will keep prepared statements in memory, and there can be an overhead in doing this. – Tim Biegeleisen Feb 06 '20 at 16:29

Select SQL column to get in shiny

ui.R

server.R

1 Answers1