EVOLUTION-MANAGER

Edit File: glue_sql.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><title>R: Interpolate strings with SQL escaping</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="R.css" />
</head><body>

<table width="100%" summary="page for glue_sql {glue}"><tr><td>glue_sql {glue}</td><td style="text-align: right;">R Documentation</td></tr></table>

<h2>Interpolate strings with SQL escaping</h2>

<h3>Description</h3>

<p>SQL databases often have custom quotation syntax for identifiers and strings
which make writing SQL queries error prone and cumbersome to do. <code>glue_sql()</code> and
<code>glue_data_sql()</code> are analogs to <code>glue()</code> and <code>glue_data()</code> which handle the
SQL quoting.
</p>

<h3>Usage</h3>

<pre>
glue_sql(..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL"))

glue_data_sql(.x, ..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL"))
</pre>

<h3>Arguments</h3>

<table summary="R argblock">
<tr valign="top"><td><code>...</code></td>
<td>
<p>[<code>expressions</code>]<br /> Unnamed arguments are taken to be expressions
string(s) to format. Multiple inputs are concatenated together before formatting.
Named arguments are taken to be temporary variables available for substitution.</p>
</td></tr>
<tr valign="top"><td><code>.con</code></td>
<td>
<p>[<code>DBIConnection</code>]:A DBI connection object obtained from <code>DBI::dbConnect()</code>.</p>
</td></tr>
<tr valign="top"><td><code>.envir</code></td>
<td>
<p>[<code>environment</code>: <code>parent.frame()</code>]<br /> Environment to evaluate each expression in. Expressions are
evaluated from left to right. If <code>.x</code> is an environment, the expressions are
evaluated in that environment and <code>.envir</code> is ignored. If <code>NULL</code> is passed it is equivalent to <code><a href="../../base/html/environment.html">emptyenv()</a></code>.</p>
</td></tr>
<tr valign="top"><td><code>.na</code></td>
<td>
<p>[<code>character(1)</code>: &lsquo;NA&rsquo;]<br /> Value to replace NA values
with. If <code>NULL</code> missing values are propagated, that is an <code>NA</code> result will
cause <code>NA</code> output. Otherwise the value is replaced by the value of <code>.na</code>.</p>
</td></tr>
<tr valign="top"><td><code>.x</code></td>
<td>
<p>[<code>listish</code>]<br /> An environment, list or data frame used to lookup values.</p>
</td></tr>
</table>

<h3>Details</h3>

<p>They automatically quote character results, quote identifiers if the glue
expression is surrounded by backticks '<code style="white-space: pre;">`</code>' 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 <code>glue_sql()</code> as a
character.
</p>
<p>Returning the result with <code>DBI::SQL()</code> will suppress quoting if desired for
a given value.
</p>
<p>Note <a href="https://db.rstudio.com/best-practices/run-queries-safely#parameterized-queries">parameterized queries</a>
are generally the safest and most efficient way to pass user defined
values in a query, however not every database driver supports them.
</p>
<p>If you place a <code>*</code> at the end of a glue expression the values will be
collapsed with commas. This is useful for the <a href="https://www.w3schools.com/sql/sql_in.asp">SQL IN Operator</a>
for instance.
</p>

<h3>Value</h3>

<p>A <code>DBI::SQL()</code> object with the given query.
</p>

<h3>Examples</h3>

<pre>
con &lt;- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
iris2 &lt;- iris
colnames(iris2) &lt;- gsub("[.]", "_", tolower(colnames(iris)))
DBI::dbWriteTable(con, "iris", iris2)
var &lt;- "sepal_width"
tbl &lt;- "iris"
num &lt;- 2
val &lt;- "setosa"
glue_sql("
  SELECT {`var`}
  FROM {`tbl`}
  WHERE {`tbl`}.sepal_length &gt; {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 &gt; {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 &lt;- glue_sql("
    SELECT {`var`}
    FROM {`tbl`}
    WHERE {`tbl`}.sepal_length &gt; ?
  ", .con = con)
query &lt;- 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 &lt;- 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)

# If you need to reference a variables from multiple tables use `DBI::Id()`.
# Here we create a new table of nicknames, join the two tables together and
# select columns from both tables. Using `DBI::Id()` and the special
# `glue_sql()` syntax ensures all the table and column identifiers are quoted
# appropriately.

iris_db &lt;- "iris"
nicknames_db &lt;- "nicknames"

nicknames &lt;- data.frame(
  species = c("setosa", "versicolor", "virginica"),
  nickname = c("Beachhead Iris", "Harlequin Blueflag", "Virginia Iris"),
  stringsAsFactors = FALSE
)

DBI::dbWriteTable(con, nicknames_db, nicknames)

cols &lt;- list(
  DBI::Id(table = iris_db, column = "sepal_length"),
  DBI::Id(table = iris_db, column = "sepal_width"),
  DBI::Id(table = nicknames_db, column = "nickname")
)

iris_species &lt;- DBI::Id(table = iris_db, column = "species")
nicknames_species &lt;- DBI::Id(table = nicknames_db, column = "species")

query &lt;- glue_sql("
  SELECT {`cols`*}
  FROM {`iris_db`}
  JOIN {`nicknames_db`}
  ON {`iris_species`}={`nicknames_species`}",
  .con = con
)
query

DBI::dbGetQuery(con, query, n = 5)

DBI::dbDisconnect(con)
</pre>

<hr /><div style="text-align: center;">[Package <em>glue</em> version 1.4.2 <a href="00Index.html">Index</a>]</div>
</body></html>