glue_sql {glue} | R Documentation |
SQL databases often have custom quotation syntax for identifiers and strings
which make writing SQL queries error prone and cumbersome to do. glue_sql()
and
glue_data_sql()
are analogs to glue()
and glue_data()
which handle the
SQL quoting.
glue_sql(..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL")) glue_data_sql(.x, ..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL"))
... |
[ |
.con |
[ |
.envir |
[ |
.na |
[ |
.x |
[ |
They automatically quote character results, quote identifiers if the glue expression is surrounded by backticks ‘ and do not quote non-characters such as numbers. If numeric data is stored in a character column (which should be quoted) pass the data to’glue_sql()' as a character.
Returning the result with DBI::SQL()
will suppress quoting if desired for
a given value.
Note parameterized queries are generally the safest and most efficient way to pass user defined values in a query, however not every database driver supports them.
If you place a *
at the end of a glue expression the values will be
collapsed with commas. This is useful for the SQL IN Operator
for instance.
A DBI::SQL()
object with the given query.
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") colnames(iris) <- gsub("[.]", "_", tolower(colnames(iris))) DBI::dbWriteTable(con, "iris", iris) var <- "sepal_width" tbl <- "iris" num <- 2 val <- "setosa" glue_sql(" SELECT {`var`} FROM {`tbl`} WHERE {`tbl`}.sepal_length > {num} AND {`tbl`}.species = {val} ", .con = con) # If sepal_length is store on the database as a character explicitly convert # the data to character to quote appropriately. glue_sql(" SELECT {`var`} FROM {`tbl`} WHERE {`tbl`}.sepal_length > {as.character(num)} AND {`tbl`}.species = {val} ", .con = con) # `glue_sql()` can be used in conjuction with parameterized queries using # `DBI::dbBind()` to provide protection for SQL Injection attacks sql <- glue_sql(" SELECT {`var`} FROM {`tbl`} WHERE {`tbl`}.sepal_length > ? ", .con = con) query <- DBI::dbSendQuery(con, sql) DBI::dbBind(query, list(num)) DBI::dbFetch(query, n = 4) DBI::dbClearResult(query) # `glue_sql()` can be used to build up more complex queries with # interchangeable sub queries. It returns `DBI::SQL()` objects which are # properly protected from quoting. sub_query <- glue_sql(" SELECT * FROM {`tbl`} ", .con = con) glue_sql(" SELECT s.{`var`} FROM ({sub_query}) AS s ", .con = con) # If you want to input multiple values for use in SQL IN statements put `*` # at the end of the value and the values will be collapsed and quoted appropriately. glue_sql("SELECT * FROM {`tbl`} WHERE sepal_length IN ({vals*})", vals = 1, .con = con) glue_sql("SELECT * FROM {`tbl`} WHERE sepal_length IN ({vals*})", vals = 1:5, .con = con) glue_sql("SELECT * FROM {`tbl`} WHERE species IN ({vals*})", vals = "setosa", .con = con) glue_sql("SELECT * FROM {`tbl`} WHERE species IN ({vals*})", vals = c("setosa", "versicolor"), .con = con) DBI::dbDisconnect(con)