R programming, shiny app, sql

shiny
, ,
1

I have weather data in which there are temperature values, wind-speed values based on valid_date_time which looks like this "2015-08-11 08:00:00.000" and this is in Poxict format in R. And I am building a shiny application in which I want my application to connect with database and ask the user date and time Range for which he wants to see the data and then display it on the application.

MY Code:

library(shiny)

Define UI for application that draws a histogram
ui <- fluidPage( # Sidebar with a slider input for number of bins sidebarLayout( sidebarPanel(

    dateInput(inputId = "dateRange",  
                   label =  "Date range",

                   value  = Sys.Date()
    ),
       # Show a plot of the generated distribution
  mainPanel(
     plotOutput("distPlot")
  )
) ))server <- function(input, output,session) { output$distPlot <- renderTable({

conn <-  dbConnect(odbc(),
          Driver="SQL Server",
          Server = "linemsql.c47jvbfx5hpe.us-east-2.rds.amazonaws.com",
          Database = "test",
                  Port = 1433)
  dbGetQuery(conn, paste0(
    "SELECT temperature FROM tblWeatherGWC WHERE validDateTime =" = paste (input$dateRange, "00:00:00.000")))
# Run the application shinyApp(ui = ui, server = server)

When I run this in my application I get this error :

Warning: Error in : <SQL> '2018-07-18 00:00:00.000'
    nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '2018'. 
    101:
2

Not only does this give you an error, but there's also a security issue here in the form of a SQL injection vulnerability. The first thing I'd try is converting to either a parameterized query, or use dplyr if you're familiar with that.

Separately, you'll also want to make sure you close that connection, whether the query succeeds or not; on.exit(dbClose(conn), add = TRUE) immediately after opening the connection, for example.

And one bit of unsolicited advice: I'd highly recommend you clean up your formatting and indentation. It'll make bugs easier to spot going forward. If you at least put each line of code on its own line, you can then use RStudio's auto-indent (Ctrl+i) to clean up the indentation; or use formatR.

Hope that helps!

2 Likes
3

Using Joe's suggestions, your code might look something like this:

library(shiny)
library(DBI)

# Define UI for application that draws a histogram
ui <- 
  fluidPage( # Sidebar with a slider input for number of bins sidebarLayout( sidebarPanel(
    dateInput(inputId = "dateRange",  
              label =  "Date range",
              value  = Sys.Date()
    ),
    # Show a plot of the generated distribution
    mainPanel(
      plotOutput("distPlot")
    )
  ) 

server <- function(input, output,session) { 
  output$distPlot <- 
    renderTable({
      conn <-  dbConnect(odbc(),
                         Driver="SQL Server",
                         Server = "linemsql.c47jvbfx5hpe.us-east-2.rds.amazonaws.com",
                         Database = "test",
                         UID = "user_id",
                         PWD = "password",
                         Port = 1433)
      on.exit(dbClose(conn), add = TRUE)
      
      dbGetQuery(
        conn, 
        sqlInterpolate(
          "SELECT temperature 
           FROM tblWeatherGWC 
           WHERE validDateTime = ?dateRange",
          dateRange = sprintf("%s 00:00:00.000", as.character(input$dateRange))
        )
      )
    })
})
# Run the application shinyApp(ui = ui, server = server)
2 Likes