打印由DBI :: dbBind创建的sql语句 [英] Print sql statement craeted by DBI::dbBind
问题描述
我想在创建安全的参数化查询时打印使用DBI :: dbBind创建的sql语法:
I want to print the sql syntax created with DBI::dbBind while creating safe parametrized query:
conn <- #create connection
stmt <- "select * from dbo.mytable where mycolumn = ?"
params = list("myvalue")
query <- DBI::dbSendQuery(conn, stmt)
DBI::dbBind(query, params) # how print created sql syntax?
在最后一行创建sql语法.如何查看?
in the last line the sql syntax is created. How to view it?
推荐答案
我将把我的评论形式化为答案.
I'll formalize my comment into an answer.
绑定"不会更改查询，它只是将对象仅被视为数据的对象增强"查询，并与代码混合在一起.
"Binding" does not change the query, it merely "augments" a query with objects that are treated solely as data, vice intermingled with code.
由于以下几个原因，后一种混合可能会出现问题:
The latter intermingling can be problematic for a few reasons:
-
恶意代码部署"SQL注入"( https://xkcd.com/327/，以及 Wiki解释).简而言之，它利用了关闭带引号的字符串或文字并直接执行SQL代码的优势，可能删除或修改数据，也可能提取数据.
Malevolent code deploying "SQL Injection" (https://xkcd.com/327/, and a wiki explanation). In short, it takes advantage of closing out a quoted string or literal and executing SQL code directly, perhaps to delete or modify data, perhaps to extract data.
非常无辜的是，如果您添加到查询中的数据"包含无法正确转义的引号，则您可能会无意间执行自己的SQL注入，尽管可能不太可能像#1"那样执行错误的事情"会做到的.
Innocently enough, if the "data" you add to the query contains quotes that are not escaped properly, you could inadvertently perform your own SQL injection, though perhaps less likely to do as "Bad Things" as #1 would do.
SQL代码的优化.大多数DBMS将分析和优化SQL查询，以使其性能更好，利用键等.它们经常记住查询，因此无需重新分析重复的查询，从而节省了时间.如果您将数据/参数与原始SQL查询文本混合在一起，那么当您对其进行更改(即使参数中仅一位数字)时，DBMS可能将需要重新分析查询.效率低下.
Optimization of SQL code. Most DBMSes will analyze and optimize a SQL query so that it performs better, takes advantage of keys, etc. They often remember queries, so that a repeated query does not need to be re-analyzed, saving time. If you intermingle data/parameters in with the raw SQL query text, then when you change one thing about it (even just one digit in a parameter), the DBMS will likely need to re-analyze the query. Inefficient.
有一些函数可以方便地转义或引用文字和字符串，如果您觉得必须在SQL查询中放入文字，那么我建议您使用它们.这些包括(但不限于)DBI::dbQuoteString，DBI::dbQuoteLiteral和DBI::dbQuoteIdentifier.
There are functions that facilitate escaping or quoting literals and strings, and if you feel you must put literals in your SQL query, then I urge you to use them. These include (but are not limited to) DBI::dbQuoteString, DBI::dbQuoteLiteral, and DBI::dbQuoteIdentifier.
另一个这样的函数是glue::glue_sql，该函数处理正确的引号/转义文字和标识符，以使构建SQL语句的安全简单" (引自 github存储库).这是只是"字符串插值，因此尽管它可以保护您免受上面的#1和#2的侵害，但不一定允许/鼓励#3.
Another such function is glue::glue_sql, which handles correct quoting/escaping of literals and identifiers, to "make[s] constructing SQL statements safe and easy" (quoted from the github repo). This is "just" string interpolation, so while it should protect you just fine against #1 and #2 above, it does not necessarily permit/encourage #3.
(仅需引起一个错误的引用即可提醒您哪个用于特定的DBMS.)
(It only takes one mis-quoting to remind you which is used where for your particular DBMS.)
出于记录目的，绑定非常简单，如其文档中所述:
For the record, binding is rather simple, as provided in its documentation:
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > ?")
dbBind(iris_result, list(2.3))
results <- dbFetch(iris_result)
如果需要，您可以像
中一样重复使用相同的res (至少在某些DBMS中，未对所有DBMS进行测试).
If you want, you can re-use the same res (at least in some DBMSes, not tested on all), as in
# same iris_result as above
dbBind(iris_result, list(2.5))
dbFetch(iris_result)
dbBind(iris_result, list(3))
dbFetch(iris_result)
dbBind(iris_result, list(3.2))
dbFetch(iris_result)
根据需要进行多次，最终完成
As many times as you need, ultimately finishing with
DBI::dbClearResult(iris_result)
这篇关于打印由DBI :: dbBind创建的sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
