R Academy · Lesson

Parameterized Queries and Transactions

Safely pass parameters and manage multi-step database transactions in R.

Why Parameterized Queries?

When user input is pasted directly into a SQL string, an attacker can inject malicious SQL code. Parameterized queries separate the SQL structure from the data values, making injection attacks impossible and also making your code cleaner and easier to read.

library(DBI)

# DANGEROUS: string concatenation (SQL injection risk!)
user_input <- "'; DROP TABLE users; --"
# bad_sql <- paste0("SELECT * FROM users WHERE name = '", user_input, "'")
# dbGetQuery(con, bad_sql)   <- NEVER do this

# SAFE: parameterized query
# dbGetQuery(con, 'SELECT * FROM users WHERE name = $1',
#            params = list(user_input))

cat('Parameterized queries prevent SQL injection')

dbGetQuery() with Parameters

dbGetQuery() executes a SELECT statement and returns results as a data frame. Pass a params list to bind values to placeholders. The placeholder syntax varies by driver: $1 for PostgreSQL, ? for SQLite/MySQL.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'employees', data.frame(
  name       = c('Alice', 'Bob', 'Carol'),
  department = c('Engineering', 'Marketing', 'Engineering'),
  salary     = c(90000, 75000, 95000)
))

# Parameterized SELECT — SQLite uses ?
result <- dbGetQuery(
  con,
  'SELECT name, salary FROM employees WHERE department = ?',
  params = list('Engineering')
)
print(result)

dbDisconnect(con)

All lessons in this course

← Back to R Academy