Shiny : reactive data selection from a PostgreSQL database trought multiple input SQL query

shiny
4

So after 2 days of pain, tears and drying stackoverflow, I figured out the problem. The mistake was sticking to sqlInterpolate for creating the SQL query. Using some renderPrint function to visualize the generated query, I noticed that some inopportune double quote was showing up in my query.
It appears that sqlInterpolate have been created to prevent security breach trough sql injection attacks (https://shiny.rstudio.com/articles/sql-injections.html), not allowing to use multiple input.
Thanks to parameterized queries (https://db.rstudio.com/best-practices/run-queries-safely) I was able to implement multiple in the query using sql_glue function.

Here are the usefull links for next ones :

glue documentation (https://glue.tidyverse.org/reference/glue_sql.html)
some similar topic (https://forum.posit.co/t/using-multiple-r-variables-in-sql-chunk/2940/13)
similar with dbQuoteIdentifier function (https://stackoverflow.com/questions/43385119/how-to-use-dynamic-values-while-executing-sql-scripts-in-r)

And the final code :

library(RPostgreSQL)
library(gdal)
library(leaflet)
library(shiny)
library(tidyverse)
library(sp)
library(rgeos)
library(rgdal)
library(DT)
library(knitr)
library(raster)
library(sf)
library(postGIStools)
library(rpostgis)
library(shinydashboard)
library(zip)
library(pool)
library(rjson)
library(reprex)
library(glue)

pool <- dbPool(drv = dbDriver("PostgreSQL", max.con = 100), user = "username", password = "pswd", host = "000.000.00.000", port = 5432, dbname = "database", idleTimeout = 3600000)

typology <- dbGetQuery(pool, "SELECT type FROM table GROUP BY type")
all_typo <- sort(unique(typology$type))

area_table <- dbGetQuery(pool, "SELECT area FROM tableGROUP BY area")
all_area <- sort(unique(area_table$area ))

ui <- fluidPage(
   sidebarLayout(
       sidebarPanel(
           selectInput(
               inputId = "area",
               label = "Select a district",
               choices = all_area,
               selected = 'area1',
               multiple = TRUE,
               selectize = FALSE
           ),
           selectInput(
               inputId = "typo",
               label = "Select a type",
               choices = all_typo,
               selected = 'type1',
               multiple = TRUE,
               selectize = FALSE
           )
       ),
       mainPanel(
           tabsetPanel(
               tabPanel("graph", plotOutput("plot")),
               tabPanel("Table", dataTableOutput("table"))
           )
       )
   )
)

server <- function(input, output, session) {

   selectedData <- reactive({
       req(input$area)
       req(input$typo)
       query <- glue::glue_sql(
   			"SELECT year, SUM(surface) 
               FROM table
               WHERE area IN ({area_name*})
               AND type IN ({type*})
               GROUP BY year;",
           area_name = input$area,
   		type = input$typo,
   		.con = pool)
       outp <- as.data.frame(dbGetQuery(pool, query))
   	outp
   })

   output$table <- DT::renderDataTable({
       DT::datatable(  data = selectedData(),
               options = list(pageLength = 14),
               rownames = FALSE)
   })

   output$plot <- renderPlot({
       ggplot( data = selectedData(), aes(x = year, y = sum)) + geom_point()
   })

}

shinyApp(ui = ui, server = server)
2 Likes