Параметризованные запросы с RODBC

Question

У меня есть переменная в R, которую я хотел бы передать в базу данных. Я мог бы использовать paste как многие предлагают при чтении результатов Google, но это небезопасно из-за уязвимостей SQL-инъекций. Я бы предпочел что-то вроде этого:

x <- 42
sqlQuery(db, 'SELECT Id, Name FROM People WHERE Age > ?;', bind=c(x))

Можно ли использовать параметризованные запросы с RODBC? Если нет, есть ли альтернативная библиотека, которая их поддерживает?

Я использую SQL Server, RODBC 1.3-6 и R 3.0.0.

Answer 1

Матеуш Золтак написал RODBCext пакет 2014 года (по материалам работ Брайана Рипли и Майкла Лапсли):

conn = odbcConnect('MyDataSource')

sqlPrepare(conn, "SELECT * FROM myTable WHERE column = ?")
sqlExecute(conn, 'myValue')
sqlFetchMore(conn)

Источник: http://cran.r-project.org/web/packages/RODBCext/vignettes/Parameterized_SQL_queries.html

Answer 2

Это те варианты, которые я знаю об использовании RODBC. Я знаю, что RSQLite изначально поддерживает привязку параметров, но обычно это не подходит для большинства людей.

# Note that sprintf doesn't quote character values. The quotes need
# to be already in the sql, or you have to add them yourself to the
# parameter using paste().
q <- "select * from table where val1 = '%s' and val2 < %d and val3 >= %f"
sprintf(q,"Hey!",10,3.141)

# The gsub route means you can't easily use a single placeholder
# value.
q <- "select * from table where val1 = '?' and val2 < ? and val3 >= ?"
gsub("?","Value!",q,fixed = TRUE)

Я имею дело с большим количеством стандартных запросов для моей работы, которые требуют различных параметров. Так как в моем случае у меня есть только SELECT привилегии, и я единственный, кто запускает мой код, мне не нужно беспокоиться о проверке.

Так что я в основном пошел gsub маршрут, чтобы иметь возможность хранить все мои запросы в отдельных файлах.sql. Это потому, что запросы часто бывают достаточно длинными, поэтому их хранение в моих файлах.R становится громоздким. Разделяя их, мне легче редактировать и поддерживать их с форматированием и выделением, которые больше подходят для SQL.

Поэтому я написал несколько небольших функций, которые читают запрос из файла.sql и связывают любые параметры. Я пишу запрос с параметрами, обозначенными двоеточиями, т.е. :param1:, :param2:,

Затем я использую эту функцию для чтения файла.sql:

function (path, args = NULL) 
{
    stopifnot(file.exists(path))
    if (length(args) > 0) {
        stopifnot(all(names(args) != ""))
        sql <- readChar(path, nchar = file.info(path)$size)
        p <- paste0(":", names(args), ":")
        sql <- gsub_all(pattern = p, replacement = args, x = sql)
        return(sql)
    } else {
        sql <- readChar(path, nchar = file.info(path)$size)
        return(sql)
    }
}

где gsub_all в основном это просто оболочка для цикла по параметрам и args это именованный список значений параметров.

Это диапазон вариантов, которые я знаю.