Using dateRangeInput with BETWEEN logic in SQL

[danimaiams]

Hi people!

I'm stuck in a SQL code what's need a logic from a dateRangeInput.

Look my problem:

 query <- glue(
      "select 
      val_lim_escoamento as LE,
      val_tensao_residual as TR
      from
      QT_QTS.PLA_ORDEM_PRODUCAO
      where DIM_EXT_TUBO = {as.numeric(input$diametro)}
      and DATE between 'DATE 1' AND 'DATE 2' <----- HERE IS THE PROBLEM ------
      order by DTH_CRIACAO_REG desc")
    
    df <- dbGetQuery(
      connection_reportUser,
      query
    )

But, I don't have DATE 1 and DATE 2, I have just "dates" (the limit range between 2 dates), in my widget list:

dateRangeInput("dates", 
label = ("Periodo"),
start = Sys.Date() - 90, end = Sys.Date() - 1,
format = "dd/mm/yyyy",
language = "pt-BR",
separator = "ate"
)

How can I solve this problem?

[jdlong]

Looking at the data range example here makes me think the output of a dateRangeInput widget is a vector of, length 2, of dates. So maybe this would work:

query <- glue(
      "select 
      val_lim_escoamento as LE,
      val_tensao_residual as TR
      from
      QT_QTS.PLA_ORDEM_PRODUCAO
      where DIM_EXT_TUBO = {as.numeric(input$diametro)}
      and DATE between '{input$dates[1]}' AND '{input$dates[2]}' <----- HERE IS THE PROBLEM ------
      order by DTH_CRIACAO_REG desc")

You'll need to test to ensure that the values coming out of input$dates are in the correct string format to go into your SQL.

[jlacko]

When facing a similar situation I have used code like quoted below; it worked.

The nice thing about date input range is that you do not have to worry too much about sanitizing SQL inputs (you recall the XKCD strip about little Bobby tables, do you?) as the output is generated by the widget, not the user.

src <- dbGetQuery(conn, paste0("SELECT * FROM myTable where created between '",
                               as.character(input$dateRange[1]),
                               "' and '",
                               as.character(input$dateRange[2])';"))

A note: this code works against Postgres, it is possible other backends may require a slightly modified approach (such as using DATE before the date literal).

[danimaiams]

It's not working. I tried with many syntax.

Look my try:

      and dth_criacao_reg between {as.character(input$dates[1])} and {as.character(input$dates[2])}

With my UI.R:

                   dateRangeInput("dates", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 90, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                                  
                   ),

Where is the mistake?

[jlacko]

Two comments: 1) check the name of your input and 2) enclose the dates in single quotes. You want the date to have 'YYYY-MM-DD' format; the single quotes are important. Depending on your database backend you might need format DATE 'YYYY-MM-DD'. Postgres does not need the DATE, Teradata requires it (go figure... ).
You might also have to adjust / convert the format of date format from slash separated to dash separated.

[danimaiams]

I got it, but still not working.

I'm using Oracle database.

Look the data export

In the code it's written:

dateRangeInput("dates", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 90, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                   ),

I think the own program can understand and transform the date format by itself.

And other question...
If I want to enter with input called 'GRAU' (the last table collum), I must declare the widget a 'textInput'?

I must type:
'L80 TIPO 1'
'L80_TIPO_1'
L80 TIPO 1
L80_TIPO_1
...
There is a standard shape?

[jlacko]

OK, Oracle does require date literals identified by DATE. So try something along the lines:

and dth_criacao_reg between DATE '{as.character(input$dates[1])}' and DATE '{as.character(input$dates[2])}'

I am not that familiar with the glue package, and unsure of how it will handle the single quotes.

[jdlong]

you'll get better help if you show the value of query in your post. We're all sort of guessing what's in the query, but you have it right in front of you.

[danimaiams]

Man... my query is a default value of data. The others parameters have been tested and are correct:

WITHOUT the date, the tableoutput is there:

BUT, when i try to enter with DATE filter, it does not work.

I just tried the last code of the friend @jlacko, but it still not working even with many variation of syntax.

My UI.R code:

dateRangeInput("dates", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 90, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                   )

menuItem( text = "Descricao do Material",
                               tabName = "descricao",
                               
                               numericInput(inputId= "diametro", label= "Diametro do Aco:", value= 0),
                               
                               numericInput(inputId= "parede", label= "Parede:", value= 0),
                               
                               textInput(inputId= "grau", label= "Grau:", value= 'x'),
                               
                               textInput(inputId= "aqa", label= "AQA:", value= 'x'),
                               
                               numericInput(inputId= "ciclo", label = "Ciclo:", value = 0),
                               
                               actionButton("plot", "Plotar!")

Server.R

 query <- glue(
      "select 
      cod_ordem_producao as Ordem,
      dim_ext_tubo as Diametro,
      esp_par_tubo as Parede,
      cod_aqa as AQA,
      tmo_ciclo_plan as Ciclo,
      dth_criacao_reg as Data,
      dsc_aco as Grau,
      val_lim_escoamento as LE,
      val_tensao_residual as TR
      from
      QT_QTS.PLA_ORDEM_PRODUCAO
      where DIM_EXT_TUBO = {as.numeric(input$diametro)}
      and esp_par_tubo = {as.numeric(input$parede)}
      and tmo_ciclo_plan = {as.numeric(input$ciclo)}
      and dth_criacao_reg between dates '{as.character(input$dates[1])}' and dates '{as.character(input$dates[2])}'
      and VAL_LIM_ESCOAMENTO != 0
      order by DTH_CRIACAO_REG desc")
    
    df <- dbGetQuery(
      connection_reportUser,
      query
    )

[jlacko]

I don't think the query will work with

and dth_criacao_reg between dates '{as.character(input$dates[1])}' and dates '{as.character(input$dates[2])}'

You need date, not dates, to introduce the date literal.

Also, try to show us the value of query string - either via browser() or just plain print() but do get it out

[danimaiams]

Ok.

Good result (without data input)

Bad result (with data input)

And I wrote "dates" cause it's the ID of my dateRangeInput...
May I have to use DATE for all cases?

[danimaiams]

@jlacko, It worked.

Many thanks! I owe you one!

[jlacko]

I am glad it did!

Date can be tricky; there is a reason why it is a reserved word

[jcheng]

I really implore you (all) to use parameterized queries instead of glue() or paste() to form dynamic queries. The latter is a big security hole that can be used to trivially easily perform a sql injection attack.

https://db.rstudio.com/best-practices/run-queries-safely/

[paul]

would you consider glue_sql() to be a safe option Joe?

[jlacko]

While I agree in principle with you recommendation (hard to disagree...) I have a question, more out of curiosity:

Is it necessary to sanitize the results from a dateRangeInput? I mean, it will always return a vector of two dates, no mater what the user inputs?

Or is there a way to somehow trick the dateRangeInput into dropping the proverbial table Students?

[danimaiams]

Replying my own post in the past:
https://forum.posit.co/t/using-daterangeinput-with-between-logic-in-sql/22776

I'm using almost the same code, but with other names, like:

dateRangeInput("iData", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 365, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                   )

In the GLUE package, doing this:

df2 <- dbGetQuery(
      connection,
      query <- glue(
"select DATA_ENFORNAMENTO_TF2
from DWA_AM_TRACA 
where DATA_ENFORNAMENTO_TF2 between DATE '{as.character(input$iData[1])}' and DATE '{as.character(input$iData[2])}'
")

But, that way it just did not work and I'm copying the example in the link posted.
ex: where AUST.DTH_DESENFORNAMENTO between DATE '{as.character(input$dates[1])}' and DATE '{as.character(input$dates[2])}' <------ this worked

Where is the mistake?

[andresrcs]

Where are you putting this code? I'm triying to replicate your issue on my postgresql server but works normally for me, the only thing I can think off is that you are putting this code outside a reactive expression.

Could you make a minimal REPRoducible EXample (reprex) about your issue?
Here is a very useful guide about how to make a reprex for a shiny app

Shiny debugging and reprex guide

Shiny issues can be challenging to resolve relative to other problems with your code or statistical methods. Shiny apps are often large, complex projects with interacting files. When seeking help from others it is considered polite to: First, do your best to work through RStudio's debugging tools to diagnose your issue on your own. Often those shiny logs and tracebacks are useful to others trying to help out. Second, strive to minimize the effort required to replicate your issue. You can do this with a reproducible example ("reprex"). Shiny Debugging Errors in Shiny code can be difficult to track down. If you don't know where your problem is coming from, you can track it down with some o…