sending character strings to SQL TO_DATE fuction

[phatboye]

Hello again I have variables in R that contains a date represented as a character string that I'd like to use to query an oracle SQL database for data dated between to endpoints. The string format is of the form "YYYY-MM-DD". I am using the examples given on db.rstudio.com as a reference. The <DATE_FIELD> variable on the Oracle SQL database is stored as a SQL date variable.

For example, I have wrote a query like this
library(tidyverse)
librar(odbc)

parameters <- c(date1, date2)
query <- c("SELECT * FROM SOME_TABLE
SOME OTHER STUFF
WHERE <DATE_FIELD>
BETWEEN TO_DATE(?, "YYYY-MM-DD")
AND TO_DATE(?, "YYYY-MM-DD")"

result <- dbSendQuery(connection, query) %>%
dBind(parameters)
result <- dbFetch(result)

I seem to be doing something wrong with the TO_DATE function, do I need to escape the question mark? How do I fix this.

[cderv]

@phatboye

dBind(parameters)

I have never used that because I use glue to build such queries but from the documentation in db.rstudio.com you linked too, it seems that dbBind takes as argument the result of dbSendQuery AND the parameters.
Should it be dBind(result, parameters) in your case ?
The doc I mentioned is here
Solutions - Run Queries Safely

[jlacko]

If your string is in format "YYYY-MM-DD" you don't really need to convert it to date. Just precede it by the DATE literal and you are set.
Have a look at this question, we had discussed it at some length

Using dateRangeInput with BETWEEN logic in SQL

Man... my query is a default value of data. The others parameters have been tested and are correct: [date2] WITHOUT the date, the tableoutput is there: [date] 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" ) m…

The rest is text parsing (make sure to print / browser() the query string and test it in your favorite DB tool).

[danimaiams]

I solved my problem that way:
Using GLUE package

df <- dbGetQuery(
      connection_reportUser,
      query <- glue(
      "select 
      cod_ordem_producao as ORDEM,
      from
      QT_QTS.PLA_ORDEM_PRODUCAO
      where DIM_EXT_TUBO = {as.numeric(input$diametro)}
      and dth_criacao_reg between DATE '{as.character(input$dates[1])}' and DATE '{as.character(input$dates[2])}'
      order by DTH_CRIACAO_REG desc")
      )

My input for the date is called 'dates'... You have to put
... between DATE '{as.character(input$thenamehere[1])}' and DATE '{as.character(input$thenamehere[2])}'

Our friend @jlacko saved me.

[jlacko]

Glad to be of service!

A word of warning though: while it should not be a problem when dealing with dateRangeInput, as it can be relied to return always dates (and not malicious strings), you should be in general aware of the risk of SQL injections when pasting / glueing queries together like this.

Have a look at what Shiny documentation has to say in this matter - https://shiny.rstudio.com/articles/sql-injections.html

[phatboye]

To everyone warning me about SQL injections thank you for your input but I am already aware of that issue. I am just trying to figure out how to make this code work. I will look into using the glue library to make my code work. One last thought I was under the impression that the dBind() function already checks for and reduces the potential for SQL injections.