sending character strings to SQL TO_DATE fuction

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.

I have never used that because I use glue :package: 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
https://db.rstudio.com/best-practices/run-queries-safely/#parameterized-queries

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 :slight_smile:


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

1 Like

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.

1 Like

Glad to be of service! :slight_smile:

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

1 Like

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.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.