dbGetQuery选择所有现有列

Question

我有一个要从数据库中选择的列向量。如果缺少该列，我希望选择所有存在的列。但是，我不知道如何在我的查询中指定这一点？

例如，要选择列drat，我指定"SELECT drat FROM mtcars"。假设我的列名是drat和colMissing。

我的查询不使用"SELECT drat, colMissing FROM mtcars"作为Error: no such column: colMissing。

但是，我想要drat导出。如何确保所有现有列都将被导出，并跳过不存在的列？在我的真实数据中，我有一个列名和许多数据库的长向量，所以我想要自动地这样做。

虚构的例子：

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
dbGetQuery(con, "SELECT * FROM mtcars") # select all columns
dbGetQuery(con, "SELECT drat, wt, disp, colMissing FROM mtcars", n = 6)  # does not work as contains non existing columns name. How to export only existing ones?

Answer 1

我不认为SQL为您提供了一种以这种方式动态设置要选择的列的简单方法。我认为进行这种类型筛选的最简单方法是确定要动态连接的列并以编程方式创建查询。

cols <- c("drat", "wt", "disp", "colMissing")
cols_to_select <- intersect(dbListFields(con, "mtcars"), cols)
cols_to_select
# [1] "disp" "drat" "wt"  
qry <- paste("select", paste(dbQuoteIdentifier(con, cols_to_select), collapse = ","), "from mtcars")
qry
# [1] "select `disp`,`drat`,`wt` from mtcars"

head(dbGetQuery(con, qry))
#   disp drat    wt
# 1  160 3.90 2.620
# 2  160 3.90 2.875
# 3  108 3.85 2.320
# 4  258 3.08 3.215
# 5  360 3.15 3.440
# 6  225 2.76 3.460

我在这里采取了深思熟虑的步骤，以减少paste-ing一起带来的无意中的SQL注入的风险。现有框架的列名可能是相当愚蠢的-恶意的，这是可行的。(不，我不认为这些名称的风险是真实的，这种类型的错误更有可能造成语法错误。)

someframe <- data.frame(a=1,b=2)
names(someframe)[1] <- "Robert');DROP TABLE Students;--"
qry <- paste("select", paste(names(someframe), collapse = ","), "from mtcars")
qry
# [1] "select Robert');DROP TABLE Students;--,b from mtcars"

好吧，所以这在这里行不通(尽管有https://xkcd.com/327/)，但是.动态生成查询时要小心。dbQuoteIdentifier是一个旨在降低这种风险的函数。使用比较数据(例如，WHERE cyl > 5)，使用参数绑定 (即WHERE cyl > ?)要好得多；但是，这在SELECT部分中不起作用，所以请注意。

作为旁人..。我认为SQL注入的讨论通常集中在查询的参数( WHERE子句中)，而不是要选择的字段上。但是，通过字段名实现这一点是可行的，尽管它需要知道注入中的目标表名。(我正在使用下面的Server。)

DBI::dbWriteTable(con, "#r2mt", mtcars[1:2,])
DBI::dbGetQuery(con, "select * from #r2mt")
#       row_names mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1     Mazda RX4  21   6  160 110  3.9 2.620 16.46  0  1    4    4
# 2 Mazda RX4 Wag  21   6  160 110  3.9 2.875 17.02  0  1    4    4
names(someframe)[1] <- 'cyl" from #r2mt;DROP TABLE #r2mt;--'
qry <- paste("select", paste(dQuote(names(someframe)), collapse = ", "), "from #r2mt")
qry
# [1] "select \"cyl\" from #r2mt;DROP TABLE #r2mt;--\", \"b\" from #r2mt"

DBI::dbGetQuery(con, qry)
#   cyl
# 1   6
# 2   6
DBI::dbGetQuery(con, "select * from #r2mt")
# Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#r2mt'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
# <SQL> 'select * from #r2mt'

我要指出的是，虽然dQuote没有对此进行保护，但dbQuoteIdentifer却这样做了。

DBI::dbWriteTable(con, "#r2mt", mtcars[1:2,])
qry <- paste("select", paste(DBI::dbQuoteIdentifier(con, names(someframe)), collapse = ", "), "from #r2mt")
qry
# [1] "select \"cyl\"\" from #r2mt;DROP TABLE #r2mt;--\", \"b\" from #r2mt"

DBI::dbGetQuery(con, "select * from #r2mt")
#       row_names mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1     Mazda RX4  21   6  160 110  3.9 2.620 16.46  0  1    4    4
# 2 Mazda RX4 Wag  21   6  160 110  3.9 2.875 17.02  0  1    4    4
DBI::dbGetQuery(con, qry)
# Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'cyl" from #r2mt;DROP TABLE #r2mt;--'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'b'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
# <SQL> 'select "cyl"" from #r2mt;DROP TABLE #r2mt;--", "b" from #r2mt'

这里显示了qry的明显差异：

# [1] "select \"cyl\" from #r2mt;DROP TABLE #r2mt;--\", \"b\" from #r2mt"
# [1] "select \"cyl\"\" from #r2mt;DROP TABLE #r2mt;--\", \"b\" from #r2mt"

我无法击败dbQuoteIdentifier，以便在这种使用中阻止"的转义。