MS Access传递查询到SQL Server我需要什么字符卫生 [英] What character sanitation do I need for an MS Access Pass Through Query to SQL Server
问题描述
我正在尝试将MS Access与SQL Server进行接口连接，并且我想确保自己不容易受到SQL Injection攻击.
I am attempting to interface MS Access with SQL Server and I want to make sure I am not vulnerable to SQL Injection attacks.
我已经看到使用ADO创建查询的参数化版本的建议，但是我想学习如何使用DAO传递对输入进行清理.
I have seen suggestions to use ADO to create a paramaterized version of the queries, but I wanted to learn how to sanitize my input with a DAO pass through.
此刻，我正在转义单引号和反斜杠.
At the moment I am escaping single quotations and backslashes.
VBA中是否有标准的SQL Server注入清理方法?
Are there any standard SQL Server injection sanitation methods in VBA?
推荐答案
VBA中是否有标准的SQL Server注入清理方法?
不.我会非常担心是否有一个.我不使用PHP，但是我读过有关其标准SQL注入卫生方法"的恐怖故事.只需查看带有代码审查问题即可.显示标记为'php'"rel =" tag> php 和准备好的语句，否则答案总是否".
Nope. And I would be very worried if there was one. I don't do PHP, but I've read horror stories about its "standard SQL injection sanitation methods". Just peek at Code Review questions tagged with php and sql - quite a lot are asking a variant of "is this code secure?", and unless they're using prepared statements, the answer is invariably "no".
');--DROP TABLE Users;--是一个相当可疑的值(您认为答案的文本存储在哪里?)，但是如果您正确编写了数据访问代码，则没有理由即使您的登录被授权(服务器端)执行这样的语句，它也可能引起混乱.
');--DROP TABLE Users;-- is a rather questionable value to see in a UserName database field (where do you think this very answer's text is being stored?), but if you've written your data access code properly, there's no reason for it to cause any mayhem, even if your login is authorized (server-side) to execute such a statement.
问题不在于用户的输入.问题是将用户输入连接到可执行SQL中，从而将用户输入作为可执行代码进行处理.
The problem isn't the user's input. The problem is concatenating user input into executable SQL and thus treating user input as executable code.
您想要 执行一条恒定 SQL语句.当您这样做时:
What you want is to execute a constant SQL statement. When you do this:
' *** DON'T DO THIS ***
Dim sql As String
sql = "SELECT Id FROM dbo.Users WHERE UserName = '" & pName & "';"
然后，每次将SQL语句发送到服务器时，它都是一个不同的查询:服务器甚至不知道所涉及的参数，它所看到的只是这样:
Then every time the SQL statement is sent to the server, it's a different query: the server doesn't even know a parameter is involved, all it sees is this:
SELECT Id FROM dbo.Users WHERE UserName = ''');--DROP TABLE Users;--';
注意多余的单引号；如果您在想嘿，SQL注入被阻止了，您就忘记了不是因为您无法击败您的输入清理，而是NOBODY可以.许多公司都使用这种注入预防"来面向世界的网站，并且认为他们的代码是安全的.当他们在Twitter上吹嘘他们十年来没有被黑客入侵"时，全球脚本小子需要几分钟的时间才能将其删除.不必费心去寻找链接，但是这个确切的故事是在最近发生的.
Notice the extra single quote; if you're thinking hey, SQL injection was prevented, you're forgetting that it's not because YOU can't defeat your input sanitization, that NOBODY can. A lot of companies have world-facing websites using this kind of "injection prevention", and think their code is secure. And when they brag on Twitter that they "haven't been hacked in over a decade of being online with it", it takes a few minutes for script kiddies around the globe to take it down. Can't be bothered to dig up a link, but this exact story happened, rather recently.
当您通过ADO发送参数化查询时，这是服务器收到的信息:
When you send a parameterized query through ADO, this is what the server receives:
SELECT Id FROM dbo.Users WHERE UserName = ?;
请注意，查询不知道甚至不需要关心参数的类型及其周围的单引号.
Notice the query doesn't know or even need to care for the type of the parameter and its surrounding single quotes.
请注意，无论用户输入的内容是什么，它都是相同的语句.
Notice it's the same identical statement regardless of what the user input is.
请注意，参数值甚至没有接近该查询的执行计划中的任何地方.参数是分别接收的，并由数据库服务器以完全安全的方式处理，由由从事此工作的人编写.
Notice the parameter values don't even come close to being involved anywhere in the execution plan of that query. Parameters are received separately, and handled by the database server in a way that's completely secure, written by people that do this for a living.
无论您多么努力，都无法保证您或任何人都能想到的消毒效果.
如果用户输入是可执行SQL语句的一部分，则不安全.
If user input is part of an executable SQL statement, it's not secure, period.
学习使用ADODB Command和Parameter对象.它们具有相当简单的API，并且正确，一致地使用它们将击败任何字符串连接的伪消毒SQL字符串.
Learn to use ADODB Command and Parameter objects. They have a fairly simple API, and using them properly and consistently will beat any string-concatenated-pseudo-sanitized SQL string.
这是一个冗长的示例:
Const sql As String = "SELECT Id FROM dbo.Users WHERE UserName = ?;"
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "{connection string}"
conn.Open
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandType = adCommandText
cmd.CommandText = sql
cmd.ActiveConnection = conn
Dim userNameParam As ADODB.Parameter
Set userNameParam = New ADODB.Parameter
userNameParam.Type = adVarChar
userNameParam.Size = 60
userNameParam.Direction = adParamInput
userNameParam.Value = pUserName '<~ user input
cmd.Parameters.Append userNameParam
Dim result As ADODB.Recordset
Set result = cmd.Execute
MsgBox result("Id").Value
conn.Close
我说 verbose ，因为当您明确指定所有内容时，您就是这样做的.我个人使用自己的ADODB包装器类，与它们一起，这些代码与上面的代码完全等效:
I say verbose, because that's how you do it when you explicitly specify everything. Personally I use my own ADODB wrapper classes, and with them this code is entirely equivalent to what's above:
Const sql As String = "SELECT Id FROM dbo.Users WHERE UserName = ?;"
Dim result As Long
result = SqlCommand.QuickSelectSingleValue(sql, pUserName)
MsgBox result
如您所见，使用适当的面向对象的代码为您封装所有管道，向数据库服务器发送正确参数化且完全安全的查询根本不是一件容易的事.
As you can see, with proper object-oriented code that encapsulates all the plumbing for you, sending a properly parameterized and entirely secure query to your database server isn't hard at all.
方法如下:
- Creating ADODB parameters on the fly
- Materializing any ADODB query
在VBEX信息库中的GitHub上可以找到具有某些错误和其他功能的类 ，其中也恰好包含大量其他有用的工具(根据GPLv3许可).请注意，尽管我确实编写了某些代码，或者某些代码启发了其中的内容，但我并不主张与VBEX存储库有任何形式的隶属关系.我 did 将这些类的原始版本上传到我自己的 VBTools 存储库中，以及所有Stack Exchange内容均根据CC-by-SA许可.
The classes, with some bugs fixed and other features, can be found on GitHub in the VBEX repository, which also happens to contain a truckload of other useful tools (licensed under GPLv3). Note, while I did author some of that code, or some code that inspired what's on there, I do not claim affiliation of any kind with the VBEX repository. I did upload the original versions of these classes to my own VBTools repository though, licensed under CC-by-SA, as is any Stack Exchange content.
这篇关于MS Access传递查询到SQL Server我需要什么字符卫生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
