SQL Server防御性数据库编程

需积分: 3 196 浏览量更新于2024-07-31 收藏 3.55MB PDF 举报

"Defensive Database Programming with SQL Server" 是一本由 Alex Kuznetsov 撰写，Hugo Kornelis 技术审阅的书籍，专注于在 SQL Server 环境中进行防御性数据库编程的技术和策略。这本书由 SimpleTalk Publishing 于2010年首次出版，并保留了作者的版权。该书的核心理念是帮助读者理解如何构建健壮、安全且具有抵御攻击能力的 SQL Server 数据库系统。防御性数据库编程旨在预防错误、恶意行为或意外的数据破坏，确保数据的完整性和系统的稳定性。以下是一些可能涵盖的关键知识点： 1. **安全性与权限管理**：讨论如何设置和管理 SQL Server 中的角色、用户、登录和权限，以限制对敏感数据的访问，并防止未经授权的操作。 2. **错误处理和异常处理**：介绍如何编写健壮的 SQL 查询，以优雅地处理错误和异常，避免因程序崩溃导致的数据丢失。 3. **事务处理**：详细解释事务的概念，包括ACID属性（原子性、一致性、隔离性和持久性），以及如何正确使用事务来确保数据的一致性。 4. **备份与恢复策略**：讨论不同类型的备份（如完整备份、差异备份、日志备份）以及如何制定恢复计划，以应对数据丢失或系统故障。 5. **性能优化**：涵盖 SQL 查询优化技巧，包括索引设计、查询改写、存储过程的使用以及如何避免阻塞和死锁。 6. **审计和日志记录**：讲解如何通过 SQL Server 的内置审计功能监控数据库活动，以便检测潜在问题并追踪历史操作。 7. **安全性最佳实践**：提供关于最小权限原则、加密技术、安全更新应用等方面的指导，以增强数据库的安全性。 8. **防御SQL注入**：深入探讨如何防止恶意的SQL注入攻击，通过参数化查询、输入验证和安全编码技术来保护数据库。 9. **高可用性和灾难恢复**：介绍SQL Server的高可用性解决方案，如镜像、Always On 可用性组和复制技术，以及灾难恢复规划。 10. **性能监控与调优工具**：讨论 SQL Server 自带的性能监控工具，如SQL Server Profiler和动态管理视图（DMVs），以及如何使用它们来识别和解决问题。书中通过实例和实践经验，帮助读者理解和应用这些防御性编程技术，以提升 SQL Server 数据库系统的健壮性和安全性。通过学习本书，IT专业人员能够更好地保护他们的数据库，防止数据泄露，减少系统停机时间，并确保业务连续性。

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页未读，继续阅读

DoomLord

粉丝: 114
资源: 1318

SQL Server防御性数据库编程

defensive database programming

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

Java Defensive Programming-开源

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

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

Object- Oriented Programming with Ansi-C

secure-coding-defensive

defensive-ex-mxli417

The practice of defensive school psychology

合理编程Reasoned Programming

最新资源