R Academy · Les

Geparametriseerde queries en transacties

Geef parameters veilig door en beheer transacties met meerdere stappen in R.

Les 4 van 413 stappen

Geparametriseerde queries en transacties is een gratis R Academy-les op CoddyKit. Dit is les 4 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject R Academy. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus R Academy bevat in totaal 4 lessen.

Waarom geparametriseerde queries?

Wanneer invoer van een gebruiker rechtstreeks in een SQL-tekenreeks wordt geplaatst, kan een aanvaller schadelijke SQL-code injecteren. Geparametriseerde queries scheiden de SQL-structuur van de gegevenswaarden, waardoor injectieaanvallen onmogelijk worden en je code bovendien overzichtelijker en gemakkelijker te lezen is.

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() met parameters

dbGetQuery() voert een SELECT-instructie uit en retourneert de resultaten als een gegevensframe. Geef een lijst params door om waarden aan tijdelijke aanduidingen te koppelen. De syntaxis van tijdelijke aanduidingen verschilt per stuurprogramma: $1 voor PostgreSQL en ? voor 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)

dbExecute() — INSERT, UPDATE, DELETE

dbExecute() voert SQL-instructies uit die gegevens wijzigen (INSERT, UPDATE, DELETE) en retourneert het aantal gewijzigde rijen. Gebruik params om waarden veilig te koppelen. Dit is de juiste functie voor schrijfbewerkingen.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'users', data.frame(
  id     = 1:3,
  name   = c('Alice', 'Bob', 'Carol'),
  active = c(TRUE, FALSE, TRUE)
))

# Safe UPDATE with parameters
rows_affected <- dbExecute(
  con,
  'UPDATE users SET active = ? WHERE id = ?',
  params = list(TRUE, 2)
)
cat('Rows updated:', rows_affected, '\n')

# Verify
result <- dbGetQuery(con, 'SELECT * FROM users WHERE id = 2')
cat('Bob active:', result$active)

dbDisconnect(con)

Meerdere parameters in één query

Je kunt meerdere parameters koppelen door ze allemaal in de lijst params op te nemen. Ze worden in volgorde gekoppeld aan de tijdelijke aanduidingen (? of $1, $2, ...) in de SQL-tekenreeks. Zorg dat het aantal lijstelementen altijd overeenkomt met het aantal tijdelijke aanduidingen.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'products', data.frame(
  id    = 1:4,
  name  = c('Apple', 'Banana', 'Cherry', 'Date'),
  price = c(1.5, 0.8, 3.0, 5.0),
  stock = c(100, 200, 50, 30)
))

# Filter by two parameters
result <- dbGetQuery(
  con,
  'SELECT name, price FROM products WHERE price > ? AND stock > ?',
  params = list(1.0, 40)
)
print(result)

dbDisconnect(con)

Geparametriseerde INSERT

Geparametriseerde INSERT-instructies voegen veilig nieuwe records toe. Koppel elke kolomwaarde als parameter. Gebruik voor bulk-invoegingen in plaats daarvan dbAppendTable() met een gegevensframe — dat is sneller en DBI regelt het koppelen automatisch.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE logs (level TEXT, message TEXT, ts TEXT)')

# Parameterized INSERT
dbExecute(
  con,
  'INSERT INTO logs (level, message, ts) VALUES (?, ?, ?)',
  params = list('INFO', 'User logged in', as.character(Sys.time()))
)

result <- dbGetQuery(con, 'SELECT * FROM logs')
print(result)

dbDisconnect(con)

dbBegin() en dbCommit() — Transacties

Een transactie groepeert meerdere SQL-instructies in één atomaire eenheid: alles slaagt, of niets wordt uitgevoerd. Gebruik dbBegin() om te starten en daarna dbCommit() om alle wijzigingen samen definitief te maken.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE accounts (id INTEGER, balance REAL)')
dbExecute(con, "INSERT INTO accounts VALUES (1, 1000), (2, 500)")

# Transfer 200 from account 1 to account 2
dbBegin(con)
  dbExecute(con, 'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            params = list(200, 1))
  dbExecute(con, 'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            params = list(200, 2))
dbCommit(con)

result <- dbGetQuery(con, 'SELECT * FROM accounts')
print(result)
dbDisconnect(con)

dbRollback() — Een transactie ongedaan maken

Als een instructie in een transactie mislukt, roep je dbRollback() aan om alle wijzigingen sinds dbBegin() ongedaan te maken. Zo blijft de database consistent. Combineer dbBegin() altijd met dbCommit() of dbRollback().

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE orders (id INTEGER, amount REAL)')
dbExecute(con, 'INSERT INTO orders VALUES (1, 500)')

# Simulate a failed transaction
dbBegin(con)
tryCatch({
  dbExecute(con, 'UPDATE orders SET amount = ? WHERE id = ?',
            params = list(600, 1))
  stop('Simulated error during processing')  # something goes wrong
  dbCommit(con)
}, error = function(e) {
  dbRollback(con)
  cat('Transaction rolled back:', e$message, '\n')
})

# Amount is still 500 (rollback worked)
result <- dbGetQuery(con, 'SELECT * FROM orders')
cat('Amount after rollback:', result$amount)
dbDisconnect(con)

dbWithTransaction() — Veiliger patroon

dbWithTransaction() voert je codeblok automatisch binnen een transactie uit. De functie legt de wijzigingen vast als het blok slaagt en maakt ze ongedaan als er een fout optreedt. Dit is overzichtelijker en minder foutgevoelig dan handmatig dbBegin() / dbCommit() / dbRollback() aanroepen.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE ledger (account TEXT, amount REAL)')
dbExecute(con, "INSERT INTO ledger VALUES ('Alice', 1000), ('Bob', 500)")

# Automatic transaction management:
dbWithTransaction(con, {
  dbExecute(con, "UPDATE ledger SET amount = amount - 100 WHERE account = 'Alice'",
            params = list())
  dbExecute(con, "UPDATE ledger SET amount = amount + 100 WHERE account = 'Bob'",
            params = list())
})
# Committed automatically on success

print(dbGetQuery(con, 'SELECT * FROM ledger'))
dbDisconnect(con)

Fouten afhandelen binnen transacties

Door tryCatch() te combineren met dbWithTransaction() krijg je overzichtelijke foutrapportage. Wanneer het binnenste blok een fout veroorzaakt, maakt dbWithTransaction() de wijzigingen automatisch ongedaan en kan je foutafhandelaar het probleem loggen of opnieuw doorgeven.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE inventory (item TEXT, qty INTEGER)')
dbExecute(con, "INSERT INTO inventory VALUES ('Widget', 100)")

safe_update <- function(con, item, qty_change) {
  tryCatch(
    dbWithTransaction(con, {
      dbExecute(con,
        'UPDATE inventory SET qty = qty + ? WHERE item = ?',
        params = list(qty_change, item))
      cat('Updated', item, 'by', qty_change, '\n')
    }),
    error = function(e) cat('Failed (rolled back):', e$message, '\n')
  )
}

safe_update(con, 'Widget', -30)
print(dbGetQuery(con, 'SELECT * FROM inventory'))
dbDisconnect(con)

Batch-invoegingen voor betere prestaties

Veel rijen één voor één invoegen in een lus is traag. Twee betere benaderingen zijn: gebruik dbAppendTable() om een heel gegevensframe tegelijk in te voegen, of plaats afzonderlijke invoegingen in één transactie — databases schrijven de wijzigingen dan maar één keer naar schijf aan het einde, waardoor bulk-invoegingen binnen een transactie veel sneller zijn dan invoegingen met automatisch vastleggen.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbExecute(con, 'CREATE TABLE events (user_id INTEGER, action TEXT)')

# Fast: insert a whole data frame at once
new_events <- data.frame(
  user_id = c(101, 102, 103, 104),
  action  = c('login', 'view', 'purchase', 'logout')
)
dbAppendTable(con, 'events', new_events)

result <- dbGetQuery(con, 'SELECT COUNT(*) AS n FROM events')
cat('Rows inserted:', result$n)

dbDisconnect(con)

dbDisconnect() — Verbindingen altijd sluiten

Databaseverbindingen gebruiken serverbronnen. Roep altijd dbDisconnect(con) aan wanneer je klaar bent. Door bovenaan een functie on.exit(dbDisconnect(con)) te gebruiken, zorg je ervoor dat de verbinding zelfs bij een fout wordt gesloten.

library(DBI)

# Pattern: use on.exit to guarantee disconnection
run_query <- function(sql) {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  on.exit(dbDisconnect(con), add = TRUE)  # always runs

  dbExecute(con, 'CREATE TABLE t (x INTEGER)')
  dbExecute(con, 'INSERT INTO t VALUES (1), (2), (3)')
  result <- dbGetQuery(con, sql)
  result  # con is closed automatically after return
}

df <- run_query('SELECT * FROM t WHERE x > 1')
cat('Rows:', nrow(df))
# on.exit fires after return — connection cleanly closed

Korte controle

Je moet twee gerelateerde UPDATE-instructies groeperen, zodat beide slagen of geen van beide de database wijzigt. Welke aanpak is het beste?

Beantwoord dit in de interactieve les

Geparametriseerde queries en transacties — Belangrijkste punten

Veilige en betrouwbare databasebewerkingen in R met DBI:

Voeg invoer van gebruikers nooit samen in SQL-tekenreeksen — gebruik altijd params
dbGetQuery(con, sql, params = list(...)) — veilige SELECT
dbExecute(con, sql, params = list(...)) — veilige INSERT/UPDATE/DELETE
Tijdelijke aanduidingen: ? voor SQLite/MySQL, $1 / $2 voor PostgreSQL
dbBegin() + dbCommit() + dbRollback() — handmatige transactiecontrole
dbWithTransaction(con, { ... }) — automatisch vastleggen/ongedaan maken (aanbevolen)
dbAppendTable(con, 'tbl', df) — snelle bulk-invoeging
on.exit(dbDisconnect(con)) — verbindingen altijd sluiten

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
on.exit(dbDisconnect(con), add = TRUE)

dbExecute(con, 'CREATE TABLE transfers (from_id INT, to_id INT, amount REAL)')

# Safe parameterized insert inside a transaction:
dbWithTransaction(con, {
  dbExecute(con,
    'INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)',
    params = list(1, 2, 250.00)
  )
})

result <- dbGetQuery(con, 'SELECT * FROM transfers')
cat('Transfer recorded:', result$amount)

Gratis beginnen

Leer R met een AI-tutor — gratis

Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.

Cursussen: 43
Lessen: 159

Gratis beginnen met leren Bekijk de volledige cursus

Veelgestelde vragen

Is de les “Geparametriseerde queries en transacties” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad R Academy, waaronder “Geparametriseerde queries en transacties”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus R Academy bevat in totaal 4 lessen.

Wat leer ik in “Geparametriseerde queries en transacties”?

Geef parameters veilig door en beheer transacties met meerdere stappen in R. Je oefent met R Academy door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.

Heb ik ervaring nodig om met R Academy te beginnen?

Ervaring vooraf is niet nodig. R Academy op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 4 van 4.

Hoe lang duurt de les “Geparametriseerde queries en transacties”?

De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.

Kan ik code schrijven en uitvoeren in deze les over R Academy?

Ja. Elke les over R Academy bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.

Alle lessen in deze cursus

Basisprincipes van DBI en RSQLite
Verbinding maken met PostgreSQL en MySQL
dbplyr: SQL via dplyr-syntaxis
Geparametriseerde queries en transacties

← Terug naar R Academy