Проблема
Я хочу написать оболочку для некоторых функций DBI
, которая позволит безопасно выполнять параметризованные запросы. Я нашел этот ресурс, в котором объясняется как использовать пакет glue
для вставки параметров в запрос SQL. Однако есть два различных способа использования пакета glue
для вставки параметров:
- Метод 1 включает использование
?
в запросе sql, где необходимо вставить параметры, а затем использованиеdbBind
для их заполнения. Пример из приведенной выше ссылки:
library(glue)
library(DBI)
airport_sql <- glue_sql("SELECT * FROM airports WHERE faa = ?")
airport <- dbSendQuery(con, airport_sql)
dbBind(airport, list("GPT"))
dbFetch(airport)
- Метод 2 предполагает использование
glue_sql
илиglue_data_sql
для самостоятельного заполнения параметров (без использованияdbBind
). Снова пример из ссылки выше:
airport_sql <-
glue_sql(
"SELECT * FROM airports WHERE faa IN ({airports*})",
airports = c("GPT", "MSY"),
.con = con
)
airport <- dbSendQuery(con, airport_sql)
dbFetch(airport)
Я бы предпочел использовать второй метод, потому что он имеет много дополнительных функций, таких как свертывание нескольких значений для оператора in
в предложении where
оператора sql. См. второй пример выше, как это работает (обратите внимание на *
после параметра, который указывает, что он должен быть свернут). Вопрос в том, безопасно ли это против SQL-инъекций? (Есть ли другие вещи, о которых мне нужно беспокоиться?)
Мой код
В настоящее время это код, который у меня есть для моей оболочки.
paramQueryWrapper <- function(
sql,
params = NULL,
dsn = standard_dsn,
login = user_login,
pw = user_pw
){
if(missing(sql) || length(sql) != 1 || !is.character(sql)){
stop("Please provide sql as a character vector of length 1.")
}
if(!is.null(params)){
if(!is.list(params)) stop("params must be a (named) list (or NULL).")
if(length(params) < 1) stop("params must be either NULL, or contain at least one element.")
if(is.null(names(params)) || any(names(params) == "")) stop("All elements in params must be named.")
}
con <- DBI::dbConnect(
odbc::odbc(),
dsn = dsn,
UID = login,
PWD = pw
)
on.exit(DBI::dbDisconnect(con), add = TRUE)
# Replace params with corresponding values and execute query
sql <- glue::glue_data_sql(.x = params, sql, .con = con)
query <- DBI::dbSendQuery(conn = con, sql)
on.exit(DBI::dbClearResult(query), add = TRUE, after = FALSE)
return(tibble::as_tibble(DBI::dbFetch(query)))
}
Мой вопрос
Это безопасно от SQL-инъекций? Тем более, что я не использую dbBind
.
Эпилог
Я знаю, что уже существует оболочка с именем dbGetQuery
, которая разрешает параметры (см. этот вопрос для больше информации - ищите ответ @krlmlr для примера с параметризованным запросом). Но это снова основано на первом методе с использованием ?
, который гораздо более простой с точки зрения функциональности.
DBI
: cran.r-project. org/web/packages/DBI/DBI.pdf - person MichaelChirico   schedule 16.05.2019glue_sql
также упоминается внедрение (помимоdbBind
): glue.tidyverse.org/reference/glue_sql. html - person MichaelChirico   schedule 16.05.2019DBI
. Это упоминает внедрение SQL в несколько функций, среди которыхdbBind
. Но, как я уже упоминал в своем ответе, эта функция не допускает именованных параметров для базы данных SQL-сервера. Поэтому, если кто-то не может объяснить мне, как использоватьdbBind
с именованными параметрами на сервере sql, это не имеет значения. 2. О документации поglue_sql
. Это буквально только говорит о том, что параметризованные запросы являются самыми безопасными, подразумевая, что использованиеdbBind
лучше. Но ничего не говорится о безопасности использованияglue_sql
самого по себе. Опять не отвечая на мой вопрос. - person Willem   schedule 16.05.2019glue_sql
в принципе не защищен от SQL-инъекций. - person starja   schedule 15.04.2021