Where can I find a document that tells we need explicit `as.character()` when we use input of `dateInput` as parameterized SQLite3 query via RSQLite, but not with query constructed with insecure `paste0()`?

shiny
1

Question:
Where can I find a document that tells we need explicit as.character() when we use input of dateInput as parameterized rsqlite3 query in the form of dbGetQuery(con, query, params = c(as.character(input$date)), which is not necessary when we use insecure paste0() instead of parameterized query?

I ask here because I was trapped on shiny when I tried to convert test data like "2012-03-04" to shiny input$date (although I admit this feature is not direct consequence of shiny but rather relevant to R/RSQLite..)

I put a shiny reprex at the end.

Observation behind the question:

On shiny, dateInput is class Date and its lower level piece of information is double but not character, although it looks like character on console.

date1 <- as.Date("2012-03-04")
date1
# [1] "2012-03-04"
dput(date1)
# structure(15403, class = "Date")
print(is.numeric(date1))
# [1] FALSE
print(is.character(date1))
# [1] FALSE
# print(is.Date(date1)) # library(lubridate)
# [1] TRUE
print(typeof(date1))
# [1] "double"

On sqlite3, date data type is text for data in the form such as "YYYY-MM-DD" (ref. 1 at the bottom, extracted from the link : Datatypes In SQLite).

The conversion of shiny date to sqlite3 date is not done automatically. Instead, the conversion from shiny to sqlite3 results in shiny double to sqlite3 numeric, so we need explicit as.character() when we use parameterized query.

Here's extract how it works on R .

When we use parameterized query,

  • ok, with as.character().
library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES ("2012-03-04"), ("1234-05-06");')

date1 <- as.Date("2012-03-04")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(as.character(date1)))
#       mydate id
# 1 2012-03-04  1
  • notok, without as.character().
date1 <- as.Date("2012-03-04")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(date1))
# [1] mydate id    
# <0 rows> (or 0-length row.names)

On the other hand, when we use insecure paste0() approach, I don't need this as.character() (it is maybe because paste0 coerces Date class to character but not to numeric).

  • ok, even without as.character().
date1 <- as.Date("2012-03-04")
query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
dbGetQuery(con, query)
#       mydate id
# 1 2012-03-04  1

I wonder where I can find appropriate indication of this feature, in any documentation, faqs or QandAs. Thanks.

The reprex on shiny to demonstrate this feature:

library(shiny)


library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES ("2012-03-04"), ("1234-05-06");')

ui <- fluidPage(
  dateInput("date1", "Date:", '2012-03-04')
  # dateInput("date1", "Date:", '4321-09-08')
  ,
  h4("Dataset in sqlite3 database"),
  verbatimTextOutput("all_data_in_sqlite3")
  ,
  h4("Check date as character"),
  h5("- It is character."), 
  h5("- So no need to use `as.character()` even when with parameterized query."), 
  verbatimTextOutput("summary_date_as_character")
  ,
  h4("Check date as Date"),
  h5("- It is double."), 
  h5("- So NEED to use `as.character()` when with parameterized query, but no need to use it when with insecure `paste0()` or `sprintf()`"), 
  verbatimTextOutput("summary_date_as_Date")
  ,
  h4("Check date as input$date1"),
  h5("- It is double."), 
  h5("- So NEED to use `as.character()` when with parameterized query, but no need to use it when with insecure `paste0()` or `sprintf()`"), 
  verbatimTextOutput("summary_date_input_date1")
)

server <- function(input, output, session) {
  # all_data_in_sqlite3 ----
  output$all_data_in_sqlite3 <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
  })
  
  # summary_date_as_character ----
  output$summary_date_as_character <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
    
    date1 <- "2012-03-04"
    # date1 <- as.Date("2012-03-04")
    # date1 <- input$date1
    print('check "2012-03-04"..')
    
    
    print(c("dput?: ", dput(date1)))
    # print(is.Date(date1)) # library(lubridate)
    print(c("is.numeric?: ", is.numeric(date1)))
    print(c("is.character?: ", is.character(date1)))
    print(c("typeof?: ", typeof(date1)))
    
    
    print("check insequre query with `paste0()`..")
    query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- paste0("select * from t1 where t1.mydate = ", "'", as.character(date1), "'")
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check insequre query with `sprintf()`..")
    query <- sprintf("select * from t1 where t1.mydate = '%s'", date1)
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- sprintf("select * from t1 where t1.mydate = '%s'", as.character(date1))
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check parameterized query with `?`..")
    query <- "select * from t1 where t1.mydate = ?"
    print("ok, gotten, without `as.character()")
    print(dbGetQuery(con, query, params = c(date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    print("check parameterized query with `:datex`..")
    query <- "select * from t1 where t1.mydate = :datex"
    print("ok, gotten, without `as.character()")
    print(dbGetQuery(con, query, params = list(datex = date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = list(datex = as.character(date1))))
    
    # print("check, with glue_sql, parameterized query with `?`..")
    # library(glue)
    # query <- glue_sql("select * from t1 where t1.mydate = ?")
    # print("notok, empty, without `as.character()")
    # print(dbGetQuery(con, query, params = c(date1)))
    # print("ok, gotten, with `as.character()")
    # print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    # dbDisconnect(con)
  })
  
  # summary_date_as_Date ----
  output$summary_date_as_Date <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
    
    # date1 <- "2012-03-04"
    date1 <- as.Date("2012-03-04")
    # date1 <- input$date1
    print('check "as.Date("2012-03-04")"..')
    
    print(c("dput?: ", dput(date1)))
    # print(is.Date(date1)) # library(lubridate)
    print(c("is.numeric?: ", is.numeric(date1)))
    print(c("is.character?: ", is.character(date1)))
    print(c("typeof?: ", typeof(date1)))
    
    
    print("check insequre query with `paste0()`..")
    query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- paste0("select * from t1 where t1.mydate = ", "'", as.character(date1), "'")
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check insequre query with `sprintf()`..")
    query <- sprintf("select * from t1 where t1.mydate = '%s'", date1)
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- sprintf("select * from t1 where t1.mydate = '%s'", as.character(date1))
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check parameterized query with `?`..")
    query <- "select * from t1 where t1.mydate = ?"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = c(date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    print("check parameterized query with `:datex`..")
    query <- "select * from t1 where t1.mydate = :datex"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = list(datex = date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = list(datex = as.character(date1))))
    
    # print("check, with glue_sql, parameterized query with `?`..")
    # library(glue)
    # query <- glue_sql("select * from t1 where t1.mydate = ?")
    # print("notok, empty, without `as.character()")
    # print(dbGetQuery(con, query, params = c(date1)))
    # print("ok, gotten, with `as.character()")
    # print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    # dbDisconnect(con)
  })
  # summary_date_input_date1 ----
  output$summary_date_input_date1 <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
    
    # date1 <- "2012-03-04"
    # date1 <- as.Date("2012-03-04")
    date1 <- input$date1
    print('check "input$date1"..')
    
    
    print(c("dput?: ", dput(date1)))
    # print(is.Date(date1)) # library(lubridate)
    print(c("is.numeric?: ", is.numeric(date1)))
    print(c("is.character?: ", is.character(date1)))
    print(c("typeof?: ", typeof(date1)))
    
    
    print("check insequre query with `paste0()`..")
    query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- paste0("select * from t1 where t1.mydate = ", "'", as.character(date1), "'")
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check insequre query with `sprintf()`..")
    query <- sprintf("select * from t1 where t1.mydate = '%s'", date1)
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- sprintf("select * from t1 where t1.mydate = '%s'", as.character(date1))
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check parameterized query with `?`..")
    query <- "select * from t1 where t1.mydate = ?"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = c(date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    print("check parameterized query with `:datex`..")
    query <- "select * from t1 where t1.mydate = :datex"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = list(datex = date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = list(datex = as.character(date1))))
    
    # print("check, with glue_sql, parameterized query with `?`..")
    # library(glue)
    # query <- glue_sql("select * from t1 where t1.mydate = ?")
    # print("notok, empty, without `as.character()")
    # print(dbGetQuery(con, query, params = c(date1)))
    # print("ok, gotten, with `as.character()")
    # print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    # dbDisconnect(con)
  })
}

shinyApp(ui = ui, server = server)

(ref. 1)

Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of
SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
Datatypes In SQLite

sessionInfo:

sessionInfo()
R version 4.3.2 (2023-10-31)
Platform: aarch64-apple-darwin20 (64-bit)
Running under: macOS Sonoma 14.4.1

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib 
LAPACK: /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

time zone: Asia/Tokyo
tzcode source: internal

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods  
[7] base     

other attached packages:
[1] glue_1.7.0      DBI_1.2.2       lubridate_1.9.3 shiny_1.8.0    

loaded via a namespace (and not attached):
 [1] sass_0.4.8        generics_0.1.3    RSQLite_2.3.5    
 [4] stringi_1.8.3     digest_0.6.34     magrittr_2.0.3   
 [7] evaluate_0.23     timechange_0.3.0  pkgload_1.3.4    
[10] fastmap_1.1.1     blob_1.2.4        jsonlite_1.8.8   
[13] pkgbuild_1.4.3    sessioninfo_1.2.2 urlchecker_1.0.1 
[16] promises_1.2.1    purrr_1.0.2       jquerylib_0.1.4  
[19] cli_3.6.2         rlang_1.1.3       crayon_1.5.2     
[22] ellipsis_0.3.2    bit64_4.0.5       remotes_2.4.2.1  
[25] withr_3.0.0       cachem_1.0.8      yaml_2.3.8       
[28] devtools_2.4.5    tools_4.3.2       memoise_2.0.1    
[31] httpuv_1.6.14     vctrs_0.6.5       R6_2.5.1         
[34] mime_0.12         lifecycle_1.0.4   stringr_1.5.1    
[37] fs_1.6.3          htmlwidgets_1.6.4 bit_4.0.5        
[40] usethis_2.2.3     miniUI_0.1.1.1    pkgconfig_2.0.3  
[43] bslib_0.6.1       later_1.3.2       profvis_0.3.8    
[46] Rcpp_1.0.12       xfun_0.42         rstudioapi_0.15.0
[49] knitr_1.45        xtable_1.8-4      htmltools_0.5.7  
[52] rmarkdown_2.25    compiler_4.3.2