0Pricing
R Academy · Lekcja

Zapytania parametryzowane i transakcje

Bezpiecznie przekazuj parametry i zarządzaj wieloetapowymi transakcjami bazodanowymi w R.

Zapytania parametryzowane i transakcje to bezpłatna lekcja R Academy na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej R Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs R Academy zawiera 4 lekcji w sumie.

Dlaczego warto używać zapytań parametryzowanych?

Gdy dane wprowadzone przez użytkownika zostaną bezpośrednio wklejone do ciągu SQL, napastnik może wstrzyknąć złośliwy kod SQL. Zapytania parametryzowane oddzielają strukturę SQL od wartości danych, dzięki czemu ataki typu SQL injection stają się niemożliwe, a kod jest bardziej przejrzysty i łatwiejszy do czytania.

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() z parametrami

dbGetQuery() wykonuje instrukcję SELECT i zwraca wyniki jako ramkę danych. Należy przekazać listę params, aby przypisać wartości do symboli zastępczych. Składnia symboli zastępczych zależy od sterownika: $1 dla PostgreSQL oraz ? dla 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() wykonuje instrukcje SQL modyfikujące dane (INSERT, UPDATE, DELETE) i zwraca liczbę zmienionych wierszy. W celu bezpiecznego przypisywania wartości należy używać params. Jest to właściwa funkcja do operacji zapisu.

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)

Wiele parametrów w jednym zapytaniu

Można przypisać wiele parametrów, umieszczając je wszystkie na liście params. Są one przypisywane w kolejności do symboli zastępczych (? lub $1, $2, ...) w ciągu SQL. Należy zawsze dopasować liczbę elementów listy do liczby symboli zastępczych.

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)

Parametryzowana instrukcja INSERT

Parametryzowane instrukcje INSERT umożliwiają bezpieczne dodawanie nowych rekordów. Każdą wartość kolumny należy przypisać jako parametr. W przypadku wstawiania zbiorczego należy zamiast tego użyć dbAppendTable() z ramką danych — jest to szybsze, a DBI automatycznie obsługuje przypisywanie parametrów.

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() i dbCommit() — transakcje

Transakcja grupuje wiele instrukcji SQL w jedną niepodzielną całość: albo wszystkie kończą się powodzeniem, albo żadna nie zostaje wykonana. Użyj dbBegin(), aby rozpocząć transakcję, a następnie dbCommit(), aby zatwierdzić wszystkie zmiany jednocześnie.

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() — wycofywanie transakcji

Jeśli dowolna instrukcja w transakcji zakończy się niepowodzeniem, należy wywołać dbRollback(), aby wycofać wszystkie zmiany wprowadzone od czasu dbBegin(). Dzięki temu baza danych pozostaje w spójnym stanie. Zawsze należy łączyć dbBegin() z jednym z wywołań: dbCommit() albo 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() — bezpieczniejszy wzorzec

dbWithTransaction() automatycznie opakowuje blok kodu w transakcję. Zatwierdza transakcję, jeśli blok wykona się pomyślnie, i wycofuje ją, jeśli wystąpi dowolny błąd. Jest to rozwiązanie przejrzystsze i mniej podatne na błędy niż ręczne wywoływanie 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)

Obsługa błędów wewnątrz transakcji

Połączenie tryCatch() z dbWithTransaction() zapewnia przejrzyste raportowanie błędów. Gdy wewnętrzny blok zgłosi błąd, dbWithTransaction() automatycznie wycofa transakcję, a procedura obsługi błędu będzie mogła zarejestrować problem lub przekazać go dalej.

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)

Wstawianie zbiorcze na potrzeby wydajności

Wstawianie wielu wierszy pojedynczo w pętli jest powolne. Dostępne są dwa lepsze podejścia: użycie dbAppendTable() do jednoczesnego wstawienia całej ramki danych albo opakowanie pojedynczych operacji wstawiania w jedną transakcję — bazy danych zapisują zmiany na dysku tylko raz, na końcu, dzięki czemu wstawianie zbiorcze w transakcji jest znacznie szybsze niż operacje z automatycznym zatwierdzaniem.

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() — zawsze zamykaj połączenia

Połączenia z bazą danych zużywają zasoby serwera. Po zakończeniu pracy zawsze należy wywołać dbDisconnect(con). Użycie on.exit(dbDisconnect(con)) na początku funkcji gwarantuje zamknięcie połączenia nawet w przypadku wystąpienia błędu.

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

Szybkie sprawdzenie

Należy zgrupować dwie powiązane instrukcje UPDATE tak, aby albo obie zakończyły się powodzeniem, albo żadna nie zmieniła bazy danych. Które podejście jest najlepsze?

Zapytania parametryzowane i transakcje — najważniejsze informacje

Bezpieczne i niezawodne operacje na bazach danych w R z użyciem DBI:

  • Nigdy nie łącz danych wprowadzonych przez użytkownika z ciągami SQL — zawsze używaj params
  • dbGetQuery(con, sql, params = list(...)) — bezpieczne zapytanie SELECT
  • dbExecute(con, sql, params = list(...)) — bezpieczne INSERT/UPDATE/DELETE
  • Symbole zastępcze: ? dla SQLite/MySQL, $1 / $2 dla PostgreSQL
  • dbBegin() + dbCommit() + dbRollback() — ręczne sterowanie transakcją
  • dbWithTransaction(con, { ... }) — automatyczne zatwierdzanie/wycofywanie (zalecane)
  • dbAppendTable(con, 'tbl', df) — szybkie wstawianie zbiorcze
  • on.exit(dbDisconnect(con)) — zawsze zamykaj połączenia
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)

Często zadawane pytania

Czy lekcja „Zapytania parametryzowane i transakcje” jest bezpłatna?

Tak — pełny tekst „Zapytania parametryzowane i transakcje” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu R Academy, przejdź na CoddyKit PRO. Kurs R Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Zapytania parametryzowane i transakcje”?

Bezpiecznie przekazuj parametry i zarządzaj wieloetapowymi transakcjami bazodanowymi w R. Ćwiczysz R Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć R Academy?

Nie wymagamy żadnego doświadczenia. R Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Zapytania parametryzowane i transakcje”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji R Academy?

Tak. Każda lekcja R Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Podstawy DBI i RSQLite
  2. Łączenie z PostgreSQL i MySQL
  3. dbplyr: SQL za pomocą składni dplyr
  4. Zapytania parametryzowane i transakcje
← Powrót do R Academy