R Academy · Leçon

Requêtes paramétrées et transactions

Transmettez des paramètres en toute sécurité et gérez les transactions de base de données en plusieurs étapes dans R.

Requêtes paramétrées et transactions est une leçon R Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage R Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours R Academy comprend 4 leçons au total.

Pourquoi utiliser des requêtes paramétrées ?

Lorsque la saisie de l’utilisateur est directement insérée dans une chaîne SQL, un attaquant peut y injecter du code SQL malveillant. Les requêtes paramétrées séparent la structure SQL des valeurs de données, ce qui rend les attaques par injection impossibles et rend également votre code plus clair et plus facile à lire.

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() avec des paramètres

dbGetQuery() exécute une instruction SELECT et renvoie les résultats sous forme de bloc de données. Transmettez une liste params pour associer des valeurs aux paramètres fictifs. La syntaxe des paramètres fictifs varie selon le pilote : $1 pour PostgreSQL, ? pour 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() exécute les instructions SQL qui modifient les données (INSERT, UPDATE, DELETE) et renvoie le nombre de lignes concernées. Utilisez params pour associer les valeurs de manière sûre. C’est la fonction appropriée pour les opérations d’écriture.

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)

Plusieurs paramètres dans une même requête

Vous pouvez associer plusieurs paramètres en les fournissant tous dans la liste params. Ils sont associés dans l’ordre aux paramètres fictifs (? ou $1, $2, ...) de la chaîne SQL. Faites toujours correspondre le nombre d’éléments de la liste au nombre de paramètres fictifs.

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 paramétré

Les instructions INSERT paramétrées ajoutent de nouveaux enregistrements en toute sécurité. Associez chaque valeur de colonne à un paramètre. Pour les insertions groupées, utilisez plutôt dbAppendTable() avec un bloc de données : c’est plus rapide et DBI gère automatiquement l’association des valeurs.

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() et dbCommit() — Transactions

Une transaction regroupe plusieurs instructions SQL en une seule unité atomique : soit elles réussissent toutes, soit aucune ne réussit. Utilisez dbBegin() pour la démarrer, puis dbCommit() pour valider toutes les modifications en une seule fois.

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() — Annuler une transaction

Si une instruction d’une transaction échoue, appelez dbRollback() pour annuler toutes les modifications effectuées depuis dbBegin(). La base de données reste ainsi dans un état cohérent. Associez toujours dbBegin() à dbCommit() ou 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() — Une méthode plus sûre

dbWithTransaction() place automatiquement votre bloc de code dans une transaction. La transaction est validée si le bloc réussit et annulée en cas d’erreur. Cette méthode est plus claire et moins sujette aux erreurs que l’appel manuel de 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)

Gestion des erreurs dans les transactions

Combiner tryCatch() et dbWithTransaction() permet d’obtenir des rapports d’erreur clairs. Lorsque le bloc interne déclenche une erreur, dbWithTransaction() annule automatiquement la transaction, et votre gestionnaire d’erreurs peut consigner le problème ou le déclencher à nouveau.

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)

Insertions par lots pour de meilleures performances

Insérer de nombreuses lignes une par une dans une boucle est lent. Deux meilleures approches : utilisez dbAppendTable() pour insérer un bloc de données entier en une fois, ou placez les insertions individuelles dans une seule transaction. Les bases de données écrivent alors les modifications sur le disque une seule fois à la fin, ce qui rend les insertions groupées dans une transaction bien plus rapides que les insertions avec validation automatique.

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() — Toujours fermer les connexions

Les connexions aux bases de données consomment des ressources sur le serveur. Appelez toujours dbDisconnect(con) lorsque vous avez terminé. Utiliser on.exit(dbDisconnect(con)) au début d’une fonction garantit que la connexion sera fermée même en cas d’erreur.

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

Vérification rapide

Vous devez regrouper deux instructions UPDATE liées afin qu’elles réussissent toutes les deux ou qu’aucune ne modifie la base de données. Quelle approche est la meilleure ?

Requêtes paramétrées et transactions — Points clés

Opérations sûres et fiables sur les bases de données en R avec DBI :

Ne concaténez jamais la saisie de l’utilisateur dans des chaînes SQL : utilisez toujours params
dbGetQuery(con, sql, params = list(...)) — SELECT sécurisé
dbExecute(con, sql, params = list(...)) — INSERT/UPDATE/DELETE sécurisés
Paramètres fictifs : ? pour SQLite/MySQL, $1 / $2 pour PostgreSQL
dbBegin() + dbCommit() + dbRollback() — contrôle manuel des transactions
dbWithTransaction(con, { ... }) — validation/annulation automatiques (méthode recommandée)
dbAppendTable(con, 'tbl', df) — insertion groupée rapide
on.exit(dbDisconnect(con)) — toujours fermer les connexions

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)

Questions Fréquemment Posées

La leçon « Requêtes paramétrées et transactions » est-elle gratuite ?

Oui — le texte complet de « Requêtes paramétrées et transactions » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours R Academy, passe à CoddyKit PRO. Le cours R Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Requêtes paramétrées et transactions » ?

Transmettez des paramètres en toute sécurité et gérez les transactions de base de données en plusieurs étapes dans R. Tu pratiques R Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer R Academy ?

Aucune expérience préalable n'est requise. R Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Requêtes paramétrées et transactions » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon R Academy ?

Oui. Chaque leçon R Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

← Retour à R Academy