sql 注入预防_SQL注入：检测和预防-CSDN博客

sql 注入预防

摘要 (Summary)

With an understanding of what SQL injection is and why it is important to an organization, we can shift into a discussion of how to prevent it. We ultimately want systems where SQL injection is impossible or very difficult to pull off. We then want systems where exploiting bugs is slow, laborious, and likely to raise monitoring alarms within an organization when attempted. The trio of layered security, prevention, and alerting can provide an immense advantage against not only SQL injection, but other data security threats.

了解了什么是SQL注入以及为什么它对组织很重要之后，我们可以进入有关如何防止它的讨论。我们最终希望使用不可能或很难实现SQL注入的系统。然后，我们希望利用漏洞的系统缓慢，费力，并且在尝试时可能在组织内引发监视警报的系统。分层的安全性，预防和警报这三者可以提供巨大的优势，不仅可以抵御SQL注入，还可以抵御其他数据安全威胁。

防止SQL注入 (Preventing SQL Injection)

This conversation’s ultimate goal is to provide us with the context needed to prevent SQL injection whenever possible. The following are tips that assist in preventing SQL injection altogether. Some of these tips also help in reducing the scope of SQL injection so that exploiting security holes is more challenging, less lucrative, or impossible.

对话的最终目的是为我们提供防止SQL注入所需的上下文。以下是有助于完全阻止SQL注入的提示。这些技巧中的一些还有助于减小SQL注入的范围，从而利用安全漏洞更具挑战性，利润更少或不可能。

限制结果集 (Limit Result Sets)

Put limits on all result sets, such as:

对所有结果集设置限制，例如：

Implement a date range limit that ensures data is returned from a narrow date/time range
实施日期范围限制，以确保从狭窄的日期/时间范围返回数据
Limit row counts processed or returned. This prevents reading or returning too much data. In addition to preventing large result sets, limiting data processed can ensure good performance, regardless of parameters
限制处理或返回的行数。这样可以防止读取或返回太多数据。除了防止生成大型结果集之外，限制处理的数据还可以确保良好的性能，而与参数无关
Prevent blank searches. Allowing a user to search through everything with no filters or return all possible results will likely perform poorly and provide little value. If blank search criteria make no sense logically, then do not allow them
防止空白搜索。允许用户不使用过滤器搜索所有内容或返回所有可能的结果，可能会导致效果不佳且价值不大。如果空白搜索条件在逻辑上没有意义，则不要使用它们

By guiding users towards common use cases, we can improve performance and bolster security at the same time. Allowing users freedom to do what they want sounds noble, but ultimately leads to more bugs, security holes, and exploits. A user will be happier with a limited set of quality options than a massive set of buggy ones.

通过指导用户使用常见用例，我们可以同时提高性能和增强安全性。允许用户自由地做自己想做的事听起来很崇高，但最终会导致更多的错误，安全漏洞和攻击。与大量的越野车相比，使用有限的质量选项的用户会更快乐。

清理和验证自由格式的用户输入 (Cleanse and Validate Freeform User Input)

This is one of the most important steps to preventing SQL injection. Any data that a user can provide, whether via a web form, file, API, or other application needs to be cleansed and validated. This process will check user input for invalid characters, unacceptable length, or any other abnormalities prior to processing or storing it on any production systems.

这是防止SQL注入的最重要步骤之一。用户可以通过网络表单，文件，API或其他应用程序提供的任何数据都需要清除和验证。该过程将在处理或存储在任何生产系统上之前检查用户输入的无效字符，不可接受的长度或任何其他异常。

The simplest step is for the application UI to detect invalid characters and provide instant feedback. Our example form from earlier provided a solid example of this behavior:

对于应用程序UI，最简单的步骤是检测无效字符并提供即时反馈。我们前面的示例表单提供了这种行为的可靠示例：

Sample form with validation errors that protect the underlying code from SQL injection attacks.

Not only did the form refuse my bogus data, but it provided a clear error message as to why my input was unacceptable. Username and password fields are often the dubious targets for this treatment, but realistically, ALL freeform input should be scrutinized for validity.

该表格不仅拒绝了我的虚假数据，而且还提供了关于我的输入为何不可接受的明确错误消息。用户名和密码字段通常是此处理的可疑目标，但是实际上，应检查所有自由格式输入的有效性。

Being thorough and layering security means that we should also perform this exercise in TSQL as well. Here are a few examples of ways that we can cleanse inputs in TSQL to ensure that bad data does not get stored or actioned upon within our database:

全面而分层的安全性意味着我们也应该在TSQL中执行此练习。下面是一些我们可以在TSQL中清除输入以确保不良数据不会在数据库中存储或处理的方法的示例：

Use parameterized stored procedures to accept inputs for common searches. In addition to providing more security options to you, they can more easily be fine-tuned for performance, as needed

使用参数化存储过程来接受常见搜索的输入。除了为您提供更多安全选项之外，还可以根据需要更轻松地对其性能进行微调。
Parameterize dynamic SQL, when used. This provides far more resilience towards SQL injection. The following is a simple example of a search in which the input @search_criteria is parameterized, rather than hard-coded into the inline TSQL:

使用时，参数化动态SQL。这为SQL注入提供了更大的弹性。以下是一个简单的搜索示例，其中对输入@search_criteria进行了参数化，而不是将其硬编码到嵌入式TSQL中：
```
DECLARE @CMD NVARCHAR(MAX);
 
  SELECT @CMD = 'SELECT * FROM Person.Person
  WHERE LastName = @search_criteria';
  PRINT @CMD;
  EXEC sp_executesql @CMD, N'@search_criteria NVARCHAR(1000)', @search_criteria;
```
Note that the parameter @search_criteria is redefined within the dynamic SQL as an additional parameter list. By passing parameters from step to step, we avoid building TSQL by hand, as well as the need to check manually for apostrophes and other common SQL injection hacks
请注意，参数@search_criteria在动态SQL中被重新定义为其他参数列表。通过一步一步地传递参数，我们避免了手工构建TSQL，也无需手动检查撇号和其他常见SQL注入技巧
Use sp_executesql when executing dynamic SQL. This is a versatile stored procedure that provides immensely more flexibility than EXEC(). It is also safer and allows for built-in parameterization. Keep in mind that both the dynamic SQL statement and parameter list can be customized freely prior to passing into sp_executesql

执行动态SQL时，请使用sp_executesql 。这是一种通用的存储过程，比EXEC（）提供了更大的灵活性。它也更安全，并允许内置参数设置。请记住，在传递到sp_executesql之前，可以自由自定义动态SQL语句和参数列表。
If needed, use QUOTENAME to cleanse a variable of potentially unwanted characters. For example, the following TSQL delimits a string in apostrophes, replacing an attempt to break a string with apostrophes with a valid string:

如果需要，请使用QUOTENAME清除可能有害的字符变量。例如，以下TSQL用撇号分隔字符串，用有效的字符串替换用撇号将字符串断开的尝试：
```
DECLARE @form_input VARCHAR(250) = '''SELECT * FROM dbo.all_of_my_passwords';
SELECT @form_input;
SELECT QUOTENAME(@form_input, '''');
```
The results show apostrophes around the string. In addition, the apostrophe in the input string has been replaced by double-apostrophes, which ensure that strings that use it will not break:

结果显示字符串周围有撇号。此外，输入字符串中的撇号已由双撇号代替，这确保使用它的字符串不会破损：

There are many poor alternatives to using QUOTENAME, the worst of which is applying lots of REPLACE functions to manually adjust character combinations. The result often looks like this:

有许多使用QUOTENAME的较差的选择，最糟糕的是应用大量的REPLACE函数来手动调整字符组合。结果通常如下所示：
```
DECLARE @form_input VARCHAR(250) = '''SELECT * FROM dbo.all_of_my_passwords';
SELECT REPLACE(@form_input, '''', '''''');
```
This is a challenging way to sanitize inputs as we need to anticipate every bad move that a hacker will make and account for it here. If we are the poor saps that are executing dynamic SQL within another delimited string, then expect the volume of apostrophes to approach dizzying quantities that result in messy and error-prone code
这是一种清理输入的挑战性方法，因为我们需要预料黑客将采取的每一个不良举动，并在此处加以解决。如果我们是在另一个定界字符串中执行动态SQL的糟糕SAP，那么期望撇号的数量接近令人眼花quantities乱的数量，从而导致代码混乱且容易出错
Ensure that application and web code are also cleansing inputs. This provides added layers of protection that shield us from the negative effects of bad code, human error, or a security vulnerability
确保应用程序和Web代码也清除输入。这提供了额外的保护层，使我们免受不良代码，人为错误或安全漏洞的负面影响

尽可能删除自由格式的用户输入 (Remove Freeform User Input When Possible)

The easiest way to prevent SQL injection via form fields is to remove the users’ freedom from entering whatever they’d like. While providing users with additional options may seem a noble thing to do, we quickly learn that users that can enter anything will enter anything. Ask for a title and you will get Jedi. Ask for nationality and you’ll get Vulcan. Or Klingon perhaps. Freeform input is ideal when you truly want a user to provide anything, such as when enumerating notes, long-form text, or opinions.

防止通过表单字段进行SQL注入的最简单方法是消除用户输入所需内容的自由。尽管为用户提供其他选项似乎是一件神圣的事情，但我们很快了解到可以输入任何内容的用户将输入任何内容。要求标题，您将得到绝地武士。要求国籍，您将获得瓦肯人。或克林贡人。当您真正希望用户提供任何内容（例如枚举便笺，长格式文本或意见）时，自由格式输入是理想的选择。

Whenever possible, use drop-down menus, radio buttons, or other input methods that provide a set list of options. Customizable options can be easily created via configuration settings or user preferences. This allows flexibility while maintaining stability and predictability at runtime. An organization or user can choose a set of valid options via a configuration menu. Once entered and validated in this location, they are available elsewhere in the application via restricted menu structures.

只要有可能，请使用下拉菜单，单选按钮或其他提供一组选项列表的输入法。可通过配置设置或用户首选项轻松创建可自定义的选项。这在保持运行时稳定性和可预测性的同时提供了灵活性。组织或用户可以通过配置菜单选择一组有效选项。在此位置输入并验证后，即可通过受限菜单结构在应用程序的其他位置使用它们。

More importantly, removing freeform fields reduces the number of places that SQL injection will be used to target. As a bonus, removing freeform fields simplifies code and increases stability as the specter of unknown user input vanishes, leaving a set of known entries that are easy and safe to manage.

更重要的是，删除自由格式字段会减少SQL注入将用于定位的位置数。另外，删除自由格式字段可简化代码并提高稳定性，因为未知用户输入的影响消失了，留下了一组易于管理且安全的已知条目。

在处理之前验证数据 (Validate Data Prior to Processing)

Cleansing data removes invalid characters and ensures that inputs will not break code or become security vulnerabilities. Validation checks data to ensure it is logically correct and does not contain invalid or unwanted values. At first glance, validation may not seem relevant to security, but oftentimes validation errors will lead to the discovery of more interesting problems. These problems might involve bad data, code problems, poorly cleansed data, or a malicious user trying to find security holes.

清除数据会删除无效字符，并确保输入不会破坏代码或成为安全漏洞。验证检查数据以确保其在逻辑上正确，并且不包含无效或不需要的值。乍一看，验证似乎与安全性无关，但是验证错误通常会导致发现更多有趣的问题。这些问题可能涉及不良数据，代码问题，清理不良的数据或试图查找安全漏洞的恶意用户。

Validating data and logging results allows us to ensure that only valid data is stored in a table. As a bonus, we can log validation errors so that developers can research and improve code as time allows. Validating data in multiple places can provide deeper insights. For example, validating analytics, reporting, and data warehousing data can often uncover other data issues that may be indicative of application problems that have yet to manifest themselves as significant bugs.

验证数据和记录结果使我们能够确保仅将有效数据存储在表中。另外，我们可以记录验证错误，以便开发人员可以在时间允许的情况下研究和改进代码。在多个位置验证数据可以提供更深入的见解。例如，验证分析，报告和数据仓库数据通常可以发现其他数据问题，这些问题可能表明尚未表现为重大错误的应用程序问题。

At a higher level, in addition to validating data, consider validating metadata and metrics, such as table row counts, data size, and utilization changes over time. These can point out scenarios in which data is growing unacceptably fast and could help pinpoint a problem before it becomes an emergency.

在更高级别上，除了验证数据外，还考虑验证元数据和指标，例如表行数，数据大小和利用率随时间的变化。这些可以指出其中数据正在以惊人的速度增长的场景，并且可以帮助在问题紧急之前确定问题。

确保错误不是面向用户的 (Ensure Errors are Not User-Facing)

Error messages are an immediate hint to any app user that something is wrong. If the errors are caught and transformed into a friendly error that is intended for general consumption, then most people will either report it or move on. If the error is a SQL Server error, though, a hacker would immediately assume that they have found a hole into the application. If one error can inadvertently be thrown, can use of SQL injection force additional errors or worse, valid and unintended TSQL?

错误消息可立即向任何应用程序用户提示出现问题。如果发现错误并将其转换为适合一般使用的友好错误，那么大多数人将报告它或继续前进。但是，如果错误是SQL Server错误，则黑客会立即认为他们已经发现了应用程序漏洞。如果可以无意中引发一个错误，那么使用SQL注入是否可以强制附加错误或更严重，有效和意外的TSQL？

Ideally, errors should be caught and handled gracefully at all levels of an application. Sensitive TSQL can be executed within TRY and CATCH blocks. This provides an immediate chance to respond to exceptions before entering the application code. Once past SQL Server, all SQL errors need to be caught by the application, logged, and handled as peacefully. We can ask ourselves the following questions:

理想情况下，应该在应用程序的各个级别上都可以捕获和处理错误。可以在TRY和CATCH块内执行敏感的TSQL。这提供了在输入应用程序代码之前立即响应异常的机会。一旦通过SQL Server，所有SQL错误都需要由应用程序捕获，记录并以和平方式处理。我们可以问自己以下问题：

Does an error need to be communicated with the user? No is a better answer, if allowable
是否需要与用户沟通错误？如果可以的话，否是一个更好的答案
Do error details need to be shared with the user? A generic error message is far more secure than one with specifics. Only provide additional information when safe and necessary to do so
错误详细信息是否需要与用户共享？通用错误消息比带有特定错误消息的安全性要高得多。仅在安全和必要时提供其他信息
Are there common or standard error conditions that can quietly be responded to with a retry? For example, if a web search times out, can it be rerun a few times until it succeeds? If allowable, retrying on an error condition forgoes the need to communicate it with a user. It may result in latency, but if it is infrequent, it is likely preferable to an error condition
是否存在可以通过重试悄悄响应的常见或标准错误条件？例如，如果网络搜索超时，是否可以重新运行几次直到成功？如果允许，则在错误情况下重试将不再需要与用户进行通信。这可能会导致延迟，但是如果不常见，则可能比错误情况更可取
Can feedback be provided to the user to prevent errors in the future? For a known issue, this is useful. For example, if I run a wide-open message search of my entire email history, it will take a very, very long time to complete (if it ever finishes). A smarter email client would gently remind me that entering more search criteria, such as dates or subjects can greatly speed it up
是否可以向用户提供反馈以防止将来出错？对于一个已知问题，这很有用。例如，如果我对整个电子邮件历史记录进行开放式消息搜索，则将需要非常非常长的时间才能完成（如果完成了的话）。聪明的电子邮件客户端会轻轻提醒我，输入更多搜索条件（例如日期或主题）可以大大加快搜索速度

使用存储过程来抽象业务逻辑和控制参数 (Use Stored Procedures to Abstract Business Logic and Control parameters)

Stored procedures are more rigid than the application code. They accept a known set of parameters and return predictable results. For very common or sensitive processes, this can be a positive way to ensure that important code executes and performs the same way every time. In addition to writing secure TSQL within the proc, we can assign permissions to a stored procedure separately from underlying tables, allowing us to greatly restrict and customize who can or cannot access it.

存储过程比应用程序代码更严格。它们接受一组已知的参数并返回可预测的结果。对于非常普通或敏感的过程，这可能是一种确保重要代码每次都执行和执行相同方式的积极方法。除了在proc中编写安全的TSQL之外，我们还可以将存储过程的权限与基础表分开分配，从而使我们能够极大地限制和自定义谁可以访问或不能访问它。

谨慎使用LIKE运营商 (Use LIKE Operators Carefully)

When user input is passed into a query that utilizes a LIKE operator, we need to be certain that the resulting query only requests valid data. For example, we can do a simple search for people named “Edward” like this:

当用户输入传递给使用LIKE运算符的查询时，我们需要确定结果查询仅请求有效数据。例如，我们可以像这样简单地搜索名为“ Edward”的人：

DECLARE @Search_Criteria NVARCHAR(MAX) = 'Edward';
 
SELECT
  *
FROM Person.Person
WHERE Person.FirstName LIKE @Search_Criteria;

The results are a set of 72 people named Edward:

结果是一组名为Edward的72个人：

What if the user were to add a percent symbol to their search:

如果用户在搜索中添加百分号怎么办：

DECLARE @Search_Criteria NVARCHAR(MAX) = '%%' -- User searches for "Thom", and gets no results returned.
 
SELECT
  *
FROM Person.Person

While not an empty search, the query above will return the entire contents of Person.Person:

虽然不是空搜索，但上面的查询将返回Person.Person的全部内容：

Results of a hacked search that returns everything, regardless of the intended search text.

In this scenario, the result set is 19,972 rows. We either need to cleanse the input to ensure that the percent symbols are treated as character literals or disallow them altogether. If necessary, TSQL can be used to manipulate the string and turn the percent symbols into character literals:

在这种情况下，结果集是19,972行。我们要么需要清理输入内容以确保将百分号符号视为字符文字，要么完全禁止使用它们。如有必要，可以使用TSQL处理字符串并将百分号转换为字符文字：

DECLARE @Search_Criteria NVARCHAR(MAX) = '%%' -- User searches for "Thom", and gets no results returned.
SELECT @Search_Criteria = REPLACE(@Search_Criteria, '%', '[%]');
 
SELECT
  *
FROM Person.Person
WHERE Person.FirstName LIKE @Search_Criteria;

This is not an ideal solution as it only addresses a single symbol, but if that were the only character issue, then it would be acceptable. The key takeaway from this behavior is to exercise caution with LIKE comparisons and ensure that the input string cannot pass through unfiltered character or regex modifiers that might allow a user to return results that are not intended for them. Similarly, if a table is massive, we do not want a user to be capable of issuing an index scan against that large table as poring through billions of rows would present a performance problem, on top of a security problem.

这不是理想的解决方案，因为它仅处理单个符号，但是如果这是唯一的字符问题，则可以接受。此行为的主要好处是，在进行LIKE比较时要格外小心，并确保输入字符串不能通过未过滤的字符或regex修饰符，这些修饰符可能会使用户返回不适合他们的结果。同样，如果一个表很大，我们不希望用户能够对那个大表发出索引扫描，因为浏览数十亿行会带来性能问题，而且还会带来安全性问题。

限制使用xp_cmdshell和其他扩展存储过程 (Limit Use of xp_cmdshell and Other Extended Stored Procedures)

Extended stored procedures facilitate interaction between SQL Server and other server components, such as services and disk resources. This can be a huge convenience if you need to read a file, output a backup, or interact with operating system settings, but it also provides an additional security hole that can be exploited.

扩展的存储过程有助于SQL Server与其他服务器组件（例如服务和磁盘资源）之间的交互。如果您需要读取文件，输出备份或与操作系统设置进行交互，这可能会带来极大的便利，但同时也提供了可以利用的其他安全漏洞。

Instead of xp_cmdshell, consider using Powershell or other scripting protocols that are built for interactions between different systems. There, permissions can be carefully controlled and scripts isolated to areas that are not reachable by application code or database code.

考虑使用Powershell或为不同系统之间的交互而构建的其他脚本协议，而不是xp_cmdshell。在那里，可以仔细控制权限，并将脚本隔离到应用程序代码或数据库代码无法访问的区域。

If xp_cmdshell is truly needed or is a part of legacy code that cannot easily be removed, following best practices with regards to security can help when managing that access:

如果确实需要xp_cmdshell或它是无法轻易删除的旧代码的一部分，那么遵循有关安全性的最佳实践可以在管理该访问时提供帮助：

xp_cmdshell. Explicitly deny access to application users or others that should never have access to it xp_cmdshell的权限。明确拒绝对应用程序用户或永远不应该访问它的其他人的访问
xp_cmdshell, security falls back onto the xp_cmdshell时 ，安全性将退回到##xp_cmdshell_proxy_account## credential. If this is undesired behavior, then ensure that no proxy security exists for this extended stored procedure ## xp_cmdshell_proxy_account ##凭据。如果这是不良行为，请确保此扩展存储过程不存在代理安全性
When a sysadmin executes xp_cmdshell, it will do so using the security rights of the SQL Server service account. Ensure that the service account does not have any more rights than is needed for the operation of that specific SQL Server
当系统管理员执行xp_cmdshell时，它将使用SQL Server服务帐户的安全权限来执行此操作。确保该服务帐户没有比该特定SQL Server操作所需的权限更多的权限

Without a doubt, try at all costs to avoid using any “xp_” extended stored procedures. They provide links between SQL Server and the operating system that are hard to identify, quantify, and assess. For a server that is accessible to any user base beyond internal administrators, this can be a serious security hole, and one that can put an organization at risk if exploited.

毫无疑问，请不惜一切代价避免使用任何“ xp_”扩展存储过程。它们提供了难以识别，量化和评估SQL Server与操作系统之间的链接。对于内部管理员以外的任何用户群都可以访问的服务器，这可能是一个严重的安全漏洞，并且如果被利用，可能会使组织面临风险。

执行渗透测试 (Perform Penetration Tests)

Internal QA and security testing are important, but catching everything is next to impossible. Many third-party companies will perform tests against an application to test for many vulnerabilities, including SQL injection.

内部质量检查和安全测试很重要，但是捕获所有内容几乎是不可能的。许多第三方公司将针对应用程序执行测试以测试许多漏洞，包括SQL注入。

As companies whose sole purpose is to locate security flaws, they will tend to have a high level of success in uncovering vulnerabilities that were overlooked internally. Once documented, an organization can fix each vulnerability and a future test can validate the improvement over time.

作为唯一旨在查找安全漏洞的公司，他们将在发现内部被忽略的漏洞方面取得较高的成功率。一旦记录在案，组织可以修复每个漏洞，将来的测试可以验证随着时间的推移所进行的改进。

Common threats found by penetration-testing companies are:

渗透测试公司发现的常见威胁是：

Insecure Passwords
不安全的密码
Default users or passwords are used
使用默认用户或密码
Injection (including SQL injection)
注入（包括SQL注入）
Unpatched software vulnerabilities
未修补的软件漏洞
Out-of-support/end-of-life software
终止支持/报废软件
Configuration mistakes
配置错误
Cross-Site Scripting
跨站脚本

An organization benefits greatly from hiring a third-party company and allowing them to perform a site-wide security test. Even more important is to repeat these tests regularly to ensure that new vulnerabilities are found and dealt with quickly, so as to minimize the exposure period.

聘请第三方公司并允许他们执行站点范围的安全测试，将大大受益于组织。更重要的是要定期重复这些测试，以确保发现并Swift处理新的漏洞，以最大程度地减少暴露时间。

Even if you have a security officer or team, the ability to find everything on one’s own is practically impossible. An alternative to hiring a security firm would be to purchase penetration testing software and run the tests yourself. This can also be effective and removes the potential challenges of allowing third-party company access to your systems.

即使您有安全官员或团队，也几乎不可能独自找到所有东西。雇用安全公司的另一种选择是购买渗透测试软件并自己运行测试。这也很有效，消除了允许第三方公司访问您的系统的潜在挑战。

代码审查 (Code Review)

This is a critical step of the software development process. The code should always be reviewed prior to testing and release. In an ideal world, code would be reviewed by multiple people, each with a different area of expertise, such as security, performance, or application domain knowledge.

这是软件开发过程中的关键步骤。在测试和发布之前，应始终检查代码。在理想的情况下，代码将由多个人审阅，每个人都具有不同的专业领域，例如安全性，性能或应用程序领域知识。

Code review allows problems to be located and fixed early in the development lifecycle and long before they reach users. Code review cannot replace other security measures, but is an exceptionally important step that can quickly allow us to find bugs that could very well lead to security vulnerabilities (among other bugs). The time spent up-front on code review will save significantly more time later on bug identification and remediation.

通过代码审查，可以在开发生命周期的早期以及在用户遇到之前很长时间就定位并修复问题。代码审查不能替代其他安全措施，但它是非常重要的一步，可以使我们Swift找到很可能导致安全漏洞的漏洞（以及其他漏洞）。预先花在代码审查上的时间将为以后的错误识别和修复节省大量时间。

最小化SQL注入的影响 (Minimizing the Impact of SQL Injection)

In addition to preventing SQL injection, we would be negligent if we did not identify our ability to make mistakes and acknowledge the need to have other security measures as well. Building solid security, in general, helps in reducing the impact of SQL injection and ensures that we are not one coding mistake away from a data breach!

除了防止SQL注入外，如果我们没有确定自己犯错误的能力并且也承认需要采取其他安全措施，我们将是疏忽大意。通常，构建可靠的安全性有助于减少SQL注入的影响，并确保我们不是一个远离数据泄露的编码错误！

While writing better code is important, it alone will not prevent us from being the victim of SQL injection. Why?

尽管编写更好的代码很重要，但仅凭它本身并不能阻止我们成为SQL注入的受害者。为什么？

We do not have the time to review and re-review all code (and do so perfectly) to ensure that we have caught everything
我们没有时间审查和重新审查所有代码（并且做得很完美），以确保我们抓住了所有问题
We cannot control code outside of our domains, such as vendor code, OS code, microservices, or other apps that are maintained by other parties
我们无法控制域外的代码，例如供应商代码，操作系统代码，微服务或其他方维护的其他应用
Anticipating all bugs, past, present, and future is a futile task
预测所有错误，过去，现在和将来都是徒劳的
Applications evolve and the code that is secure today may not be so secure tomorrow. Now infamous Intel vulnerabilities Meltdown and Spectre underscore this fact!
应用程序在发展，今天安全的代码明天可能不会那么安全。现在臭名昭著的英特尔漏洞“崩溃”和“幽灵”凸显了这一事实！
People are human and make mistakes
人是人，会犯错误

The moral of this story is that we should have tight security on top of preventing SQL injection. This minimizing the impact of any mistakes or vulnerabilities we may encounter and ensures that even if someone gains unauthorized access, their success will be greatly limited and (hopefully) detected.

这个故事的寓意是，在防止SQL注入的基础上，我们应该具有严格的安全性。这样可以最大程度地减少我们可能遇到的任何错误或漏洞的影响，并确保即使有人获得了未经授权的访问，他们的成功也将受到极大的限制和（希望）被发现。

最小特权和登录安全性原则 (Principle of Least Privilege & Login Security)

Always limit server and database security the bare minimum of what is needed. The sysadmin role should truly be limited to those few people whose job it is to administer a server. The db_owner security role should be reserved for the small number of use-cases in which an operator needs full database control, which is generally rare.

始终将服务器和数据库安全性限制在最低限度。 sysadmin角色应真正仅限于管理服务器的工作人员。 db_owner安全角色应保留给操作员需要完全数据库控制的少数用例，这通常很少见。

Applications should not need either of these roles and should be able to operate within the confines of some amount of read/write/execute access to a specific database or set of databases. Vendor apps will occasionally request sysadmin or db_owner privileges for the purpose of installations or updates. These scenarios should be investigated thoroughly and if those permissions are indeed required, determine if they can be rescinded when the installation is complete. Allowing an app to have unfettered server access is dangerous. Allowing that access to an app that is out of your control is even riskier.

应用程序不需要这些角色中的任何一个，并且应该能够在对特定数据库或一组数据库的一定数量的读/写/执行访问的范围内运行。供应商的应用程序有时会请求sysadmin或db_owner特权，以进行安装或更新。这些情况应进行彻底调查，如果确实需要这些许可权，请确定在安装完成后是否可以取消这些许可权。允许应用具有不受限制的服务器访问权限很危险。允许访问您无法控制的应用程序的风险更大。

Different apps should use different logins. Login sharing is dangerous and makes it harder to identify the source of a connection. Ideally, Windows authentication is used whenever possible. In addition to being more secure, it is easier to control access as there are additional permissions that can be adjusted in Active Directory that can enable, disable, or alter users via user-specific or group policies.

不同的应用程序应使用不同的登录名。登录共享很危险，并且使识别连接源变得更加困难。理想情况下，尽可能使用Windows身份验证。除了更安全之外，由于在Active Directory中可以调整其他权限，这些权限可以通过特定于用户或组的策略启用，禁用或更改用户，因此更易于控制访问。

Similarly, different SQL Server services should have different logins. SQL Server, SQL Server Agent, SSRS, and SSIS should operate on different credentials than the applications that use them. Each of those services should also take advantage of unique logins, thus minimizing exposure if any one of those logins were compromised. These logins should also be distinct from those used for other servers, services, and file shares.

同样，不同SQL Server服务应具有不同的登录名。 SQL Server，SQL Server代理，SSRS和SSIS的操作凭据应与使用它们的应用程序不同。这些服务中的每一个都还应该利用唯一的登录名，从而在其中任何一个登录名被泄露的情况下将风险降至最低。这些登录名也应不同于其他服务器，服务和文件共享所使用的登录名。

Never use the sa login. After Windows is installed and configured, change the password for sa to something incredibly secure, disable the login, and do not look back 🙂

切勿使用sa登录名。安装并配置Windows后，将sa的密码更改为非常安全的密码，禁用登录名，并且不要回头看。

安全链接的服务器和数据源 (Secure Linked Servers and Data Sources)

Linked servers allow for access to other data sources outside of the current SQL Server:

链接服务器允许访问当前SQL Server之外的其他数据源：

Once created, they can be queried very similarly to other local objects. Linked servers can be created that access many types of data sources, including:

创建之后，就可以与其他本地对象非常类似地查询它们。可以创建链接服务器来访问许多类型的数据源，包括：

Other SQL Servers
其他SQL Server
MySQL, PostgreSQL, MongoDB, and other types of databases
MySQL，PostgreSQL，MongoDB和其他类型的数据库
SSIS
SSIS
SSAS
SSAS
Other ODBC data sources
其他ODBC数据源

Security on linked servers is completely customizable. Users can be mapped from local to remote and granular permissions assigned to control what access is allowed over a given linked server. The linked server properties allow you to control many aspects of data transfer, such as whether RPC (remote procedure calls) are allowed to and from the server, timeouts, and the behavior of DTC:

链接服务器上的安全性是完全可定制的。可以将用户从本地权限映射到远程权限，并可以分配细化权限，以控制在给定链接服务器上允许的访问权限。链接服务器的属性使您可以控制数据传输的许多方面，例如是否允许与服务器之间的RPC（远程过程调用），超时和DTC的行为：

Security properties and options for a linked server.

The ideal linked server provides minimal access to the target server needed to accomplish its goal. When managing users and logins, the optimal scenario is to give the linked sever a distinct login that is separate from those used by other processes on the target server. This not only makes it easy to differentiate linked server traffic from other activity, but allows permissions to be set at a granular level, ensuring that a linked server can only access the tables, procs, or views that are required to execute its tasks:

理想的链接服务器对实现目标所需的目标服务器的访问最少。在管理用户和登录名时，最佳方案是为链接的服务器提供与目标服务器上其他进程所使用的登录名不同的不同登录名。这不仅使区分链接服务器流量与其他活动变得容易，而且允许在粒度级别上设置权限，从而确保链接服务器只能访问执行其任务所需的表，proc或视图：

In this example, a single local login gets mapped to a distinct remote login. All other connection attempts will be refused. This prevents a hacker from finding some common user between the local and remote servers that could provide additional access. It also greatly restricts permissions to the minimum needed by a given application, further restricting data access, and therefore also restricting unauthorized data access.

在此示例中，单个本地登录名被映射到不同的远程登录名。所有其他连接尝试将被拒绝。这样可以防止黑客在本地服务器和远程服务器之间找到一些可以提供其他访问权限的普通用户。它还极大地将权限限制到给定应用程序所需的最低限度，进一步限制了数据访问，因此也限制了未授权的数据访问。

结论 (Conclusion)

The SQL injection is a massive topic that has continued to grow and evolve over time. It is a vulnerability that garners attention across all areas of data security and has shown no sign of any sort of industry-wide resolution. This will continue to be a major security concern for years to come and anyone developing software or systems needs to be aware of how it can put sensitive data at risk.

SQL注入是一个庞大的主题，并且随着时间的推移不断增长和发展。这个漏洞引起了数据安全所有领域的关注，并且没有任何迹象表明该行业存在任何解决方案。未来几年，这将继续是主要的安全问题，任何开发软件或系统的人都必须意识到它如何使敏感数据面临风险。

When writing software, use extra caution with user input. Ensure that freeform text is carefully handled and that any systems that consume that data cleanse and validate it first. In addition to handling data carefully, monitor and respond to security vulnerabilities throughout your organization. Maintain up-to-date software and remember that bugs and vulnerabilities anywhere in an organization can lead to data loss elsewhere.

编写软件时，请特别注意用户输入。确保对自由格式的文本进行了仔细的处理，并确保使用该数据的所有系统都首先清理并验证它。除了认真处理数据外，还监视和响应整个组织中的安全漏洞。维护最新的软件，并记住组织中任何地方的错误和漏洞都可能导致其他地方的数据丢失。

Lastly, maintain a thorough software development lifecycle that allows for sufficient code review, testing, and debugging to catch as many bugs as possible prior to release. With all of this in mind, I wish you good luck and happy bug hunting!

最后，保持完整的软件开发生命周期，以进行足够的代码审查，测试和调试，以在发布之前捕获尽可能多的错误。考虑到所有这些，祝您好运，并祝您早日寻宝！