R Academy · Lezione

Query parametrizzate e transazioni

Passi i parametri in sicurezza e gestisca transazioni di database in più passaggi in R.

Lezione 4 di 413 passaggi

Query parametrizzate e transazioni è una lezione R Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento R Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso R Academy include 4 lezioni in totale.

Perché usare query parametrizzate?

Quando l'input dell'utente viene inserito direttamente in una stringa SQL, un aggressore può iniettare codice SQL dannoso. Le query parametrizzate separano la struttura SQL dai valori dei dati, rendendo impossibili gli attacchi di injection e rendendo inoltre il codice più pulito e leggibile.

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() con parametri

dbGetQuery() esegue un'istruzione SELECT e restituisce i risultati come data frame. Passi una lista params per associare i valori ai segnaposto. La sintassi dei segnaposto varia in base al driver: $1 per PostgreSQL, ? per 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() esegue istruzioni SQL che modificano i dati (INSERT, UPDATE, DELETE) e restituisce il numero di righe interessate. Utilizzi params per associare i valori in modo sicuro. Questa è la funzione corretta per le operazioni di scrittura.

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)

Più parametri in un'unica query

È possibile associare più parametri fornendoli tutti nella lista params. Vengono associati nell'ordine ai segnaposto (? o $1, $2, ...) nella stringa SQL. Il numero di elementi della lista deve sempre corrispondere al numero di segnaposto.

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)

INSERT parametrizzato

Le istruzioni INSERT parametrizzate aggiungono nuovi record in modo sicuro. Associ ogni valore di colonna a un parametro. Per gli inserimenti in blocco, utilizzi invece dbAppendTable() con un data frame: è più veloce e DBI gestisce automaticamente l'associazione dei valori.

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() e dbCommit() — Transazioni

Una transazione raggruppa più istruzioni SQL in un'unica unità atomica: o tutte hanno esito positivo oppure nessuna viene applicata. Utilizzi dbBegin() per iniziare, quindi dbCommit() per confermare tutte le modifiche insieme.

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() — Annullare una transazione

Se un'istruzione di una transazione non va a buon fine, chiami dbRollback() per annullare tutte le modifiche effettuate dopo dbBegin(). In questo modo il database rimane in uno stato coerente. Abbini sempre dbBegin() a dbCommit() o 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() — Un approccio più sicuro

dbWithTransaction() racchiude automaticamente il blocco di codice in una transazione. Esegue il commit se il blocco ha esito positivo ed esegue il rollback se si verifica un errore. È più pulito e meno soggetto a errori rispetto alla chiamata manuale di dbBegin() / dbCommit() / dbRollback().

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)

Gestione degli errori nelle transazioni

La combinazione di tryCatch() e dbWithTransaction() consente di ottenere una gestione chiara degli errori. Quando il blocco interno genera un errore, dbWithTransaction() esegue automaticamente il rollback e il gestore degli errori può registrare o rilanciare il problema.

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)

Inserimenti in batch per le prestazioni

Inserire molte righe una alla volta in un ciclo è lento. Esistono due approcci migliori: utilizzare dbAppendTable() per inserire un intero data frame in una sola volta oppure racchiudere i singoli inserimenti in un'unica transazione. In questo modo i database eseguono il commit delle scritture su disco una sola volta alla fine, rendendo gli inserimenti massivi in una transazione molto più veloci degli inserimenti con autocommit.

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() — Chiudere sempre le connessioni

Le connessioni al database consumano risorse sul server. Chiami sempre dbDisconnect(con) quando ha terminato. L'utilizzo di on.exit(dbDisconnect(con)) all'inizio di una funzione garantisce la chiusura della connessione anche in caso di errore.

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

Verifica rapida

Deve raggruppare due istruzioni UPDATE correlate in modo che entrambe abbiano esito positivo oppure che nessuna modifichi il database. Qual è l'approccio migliore?

Rispondi nella lezione interattiva

Query parametrizzate e transazioni — Punti chiave

Operazioni sui database in R sicure e affidabili con DBI:

Non concatenare mai l'input dell'utente nelle stringhe SQL: utilizzi sempre params
dbGetQuery(con, sql, params = list(...)) — SELECT sicura
dbExecute(con, sql, params = list(...)) — INSERT/UPDATE/DELETE sicuri
Segnaposto: ? per SQLite/MySQL, $1 / $2 per PostgreSQL
dbBegin() + dbCommit() + dbRollback() — controllo manuale delle transazioni
dbWithTransaction(con, { ... }) — commit/rollback automatico, approccio preferibile
dbAppendTable(con, 'tbl', df) — inserimento massivo rapido
on.exit(dbDisconnect(con)) — chiude sempre le connessioni

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 per iniziare

Impara R con un tutor IA — gratis

Scrivi ed esegui vero codice nel tuo browser, ricevi aiuto istantaneo da un tutor IA disponibile 24/7, e riprendi da dove hai lasciato sul web o nell'app.

Corsi: 43
Lezioni: 159

Inizia a imparare gratis Visualizza il corso completo

Domande Frequenti

La lezione «Query parametrizzate e transazioni» è gratuita?

Sì — il testo completo di «Query parametrizzate e transazioni» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso R Academy, passa a CoddyKit PRO. Il corso R Academy include 4 lezioni in totale.

Cosa imparerò in «Query parametrizzate e transazioni»?

Passi i parametri in sicurezza e gestisca transazioni di database in più passaggi in R. Eserciti R Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare R Academy?

Non è richiesta alcuna esperienza precedente. R Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Query parametrizzate e transazioni»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione R Academy?

Sì. Ogni lezione R Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a R Academy