防御性数据库编程：SQL Server实战

需积分: 1 95 浏览量更新于2024-07-31 收藏 3.23MB PDF 举报

"防御性数据库编程" 防御性数据库编程是一种策略，旨在预防错误、安全漏洞和性能问题，而不是仅仅依赖于事后修复。本书《防御性数据库编程 with SQL Server》由 Alex Kuznetsov 撰写，技术审阅由 Hugo Kornelis 完成，由 SimpleTalk Publishing 在 2010 年首次出版。书中深入探讨了如何在 SQL Server 环境中实施防御性编程技巧，以确保数据库系统的稳定性和安全性。 1. **防御性编程基础** 防御性编程的核心理念是编写能够处理预期和非预期输入的代码，从而减少程序出错的可能性。在数据库编程中，这可能包括验证用户输入、使用参数化查询防止 SQL 注入攻击，以及确保事务处理的正确性。 2. **SQL Server 安全性** 书中可能会详细讨论 SQL Server 的安全性特性，如角色权限管理、登录认证、防火墙设置、透明数据加密（TDE）以及审计功能，这些都是构建安全数据库环境的关键要素。 3. **性能优化** 作者可能分享了如何通过编写高效的 SQL 查询、合理使用索引、优化存储过程和事务管理来提升 SQL Server 的性能。此外，也可能讨论了监控工具和性能计数器的使用，以识别和解决性能瓶颈。 4. **异常处理和错误恢复** 防御性编程还包括了错误处理和恢复机制的设计。书中可能涵盖了如何使用 TRY-CATCH 语句捕获和处理错误，以及如何设计容错系统，使得即使在出现问题时，系统也能继续运行或进行优雅的故障切换。 5. **备份与恢复策略** 数据库的安全性也体现在备份和恢复策略上。书中可能会涵盖最佳实践，如定期备份、差异备份和日志备份，以及如何快速有效地从灾难中恢复。 6. **数据完整性** 数据库中的数据完整性是防御性编程的重要组成部分。可能涉及的主题包括约束（如 PRIMARY KEY 和 FOREIGN KEY）、触发器和检查约束，以防止非法数据的插入和更新。 7. **SQL 编程最佳实践** 书中的内容可能还会强调遵循 SQL 编程的最佳实践，如避免使用动态 SQL、减少嵌套查询和提高代码可读性，以降低出错风险。 8. **自动化和脚本** 自动化工具和脚本的使用可以帮助减轻手动操作带来的风险。书中可能涵盖如何使用 PowerShell 或其他工具自动执行常见的数据库维护任务。 9. **安全编码与开发流程** 安全编码原则贯穿整个开发周期，从需求分析到测试和部署。书中可能讨论如何将这些原则融入敏捷开发流程，以确保安全性和防御性编程成为团队文化的一部分。 10. **案例研究与实战经验** 作者可能会分享实际案例，展示防御性编程在解决实际问题中的应用，帮助读者更好地理解和应用所学知识。《防御性数据库编程 with SQL Server》是一本全面指导如何在 SQL Server 环境下实现安全、高效数据库编程的书籍，对于数据库开发者和管理员来说，是一份宝贵的参考资料。

Introduction

• if handling errors on SQL Server, keep it simple where possible; set XACT_ABORT to

ON and use transactions in order to roll back and raise an error

• if you wish to use TRY…CATCH, learn it thoroughly, and watch out for problems such

as errors that cannot be caught, doomed transactions, the need to change the error

number when raising errors, and so on.

Ch. 09: Surviving Concurrent Queries (Paid editions only)

A query that works splendidly in isolation can often fail miserably when put to work in

a live OLTP system, with real life concurrency. To make a bad situation worse, in many

cases such errors are subtle and intermittent, and therefore very dicult to reproduce

and understand. This chapter considers the case of reporting queries running against

tables that are being simultaneously modiﬁed, demonstrates how inconsistent results

can be returned, assesses the impact of various isolation levels, and considers how best

the defensive programmer can defend data integrity, while minimizing deadlocks.

Ch. 10: Surviving Concurrent Modiﬁcations (Paid editions only)

Just like queries, modiﬁcations that work perfectly well in the isolated world of the

test database, can suddenly start misbehaving intermittently when run in a production

environment under conditions of concurrent access. The chapter covers some of the

problems that might occur when "competing" connections try to simultaneously update

the same data, and how to avoid them:

• lost modiﬁcations, a.k.a. lost updates – such problems occur when modiﬁcations

performed by one connection are overwritten by another; they typically occur

silently, and no errors are raised.

• resource contention errors – such as deadlocks and lock timeouts

• primary key and unique constraint violations – such problems occur when

diﬀerent modiﬁcations attempt to insert one and the same row.

Introduction

What this book does not cover

Throughout the book I stress the importance of creating testable and fully-tested code

modules. However, the focus of this book is on writing resilient T-SQL code, not on the

implementation of unit tests. In some cases, I will describe which unit tests are required,

and which checks must be wrapped as unit tests and must run automatically. However, I

will not provide any speciﬁc details about writing unit tests.

When many people think of defensive programming, they tend to think in terms

of vulnerabilities that can leave their code susceptible to "attack." A classic example

is the SQL Injection attack, and the coding techniques that reduce the likelihood

of a successful SQL Injection attack are excellent examples of defensive database

programming. However, there already are lots of very useful articles on this subject,

most notably an excellent article by Erland Sommerskog, T C  B 

D SQL. The focus of this book is on very common, though much less publicized

vulnerabilities that can aﬀect the resilience and reliability of your code.

Due to the ﬁrm focus on defensive coding techniques, there is also no coverage in this

book of what might be termed the "documentation" aspects of defensive programming,

which would include such topics as documenting requirements, establishing code

contracts, source control, versioning, and so on.

Finally, in this book I stay focused on practical examples. While some background

material is occasionally required, I've strenuously tried to avoid rehashing MSDN.

If you are not familiar with the syntax of some command that is used in the book, or you

are unfamiliar with some terminology, MSDN is the source to which you should refer.

Code examples

Throughout this book are code examples demonstrating various defensive programming

techniques. All examples should run on all versions of SQL Server from SQL Server 2005

upwards, unless speciﬁed otherwise. To download all the code samples presented in this

book, visit the following URL:

:.-.RGBAKD_C.

剩余301页未读，继续阅读

hwequal

粉丝: 0
资源: 1

防御性数据库编程：SQL Server实战

DefensiveDatabaseProgrammingWithSQLServer.pdf 英文原版

Defensive.Database.Programming.with.SQL.Server

matlab导入excel代码-utl_sas_defensive_programming_and_error_checking:SAS防御性

Java Defensive Programming-开源

难得的经典——《Defensive Programming for RedHat Enterprise Linux》

防御式编程 Defensive Programming.PPT完整版(精品课件)

secure-coding-defensive

The practice of defensive school psychology

defensive-ex-mxli417

合理编程Reasoned Programming

最新资源