Dynamic SQL Applications, Performance, and Security
This book is an introduction and deep-dive into the many uses of dynamic SQL in Microsoft SQL Server. Dynamic SQL is key to large-scale searching based upon user-entered criteria. It’s also useful in generating value-lists, in dynamic pivoting of data for business intelligence reporting, and for cus...
| Autor principal: | |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
Berkeley, CA :
Apress
2016.
|
| Edición: | 1st ed. 2016. |
| Colección: | Expert's Voice
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009630205406719 |
Tabla de Contenidos:
- Contents at a Glance; Contents; About the Author; About the Technical Reviewer; Acknowledgments; Introduction; Chapter 1: What Is Dynamic SQL?; Understanding Dynamic SQL; A Simple Example; The EXEC Statement; Data Types to Use; Dynamic Execution Process; Dynamic SQL in Action; Advantages of Dynamic SQL; Optional or Customized Search Criteria; Customizable Everything; Optimize SQL Performance; Generate Large Amounts of TSQL or Text, Fast!; Execute SQL Statements on Other Servers or Databases; Do the Impossible!; Dynamic SQL Considerations; Apostrophes Can Break Strings; NULL Can Break Strings
- Difficult to Read and DebugPermissions and Scope Are Different; Dynamic SQL Cannot Be Used in Functions; Dynamic SQL Style; Document Thoroughly; Debugging Dynamic SQL; Write Dynamic SQL Just Like Standard TSQL; String Sizes and Truncation; Management Studio Text Display; Sp_executesql; Building Strings via Concatenation; Notes on Apostrophes; Conclusion; Chapter 2: Protecting Against SQL Injection; What Is SQL Injection?; Cleansing Inputs; Parameterizing Dynamic SQL; Schema Name and Square Brackets; Effective Spacing; Properly Type Inputs; Blind SQL Injection; Detection and Prevention
- Security TestingScanning of Application Traffic; Log Review; Code Review; Software Patching; Limiting URL Length; Use Views and/or Masking for Sensitive Data; Conclusion; Chapter 3: Large Scale Searching; Why Use Dynamic Searches?; Custom Search Grids; Search Grid Considerations; Disallowing Blank Searches; Data Paging; Conditional Paging; Search Limitations; Input-Based Search; Result Row Counts; Additional Filtering Considerations; Conclusion; Chapter 4: Permissions and Security; The Principle of Least Privilege; Granular Permissions vs. Role Permissions; Dynamic SQL and Ownership Chaining
- Changing Security Context On-the-FlyWhere Do Security Disasters Come From?; Users, Passwords, and Inconvenience; Dynamic SQL Maintenance; Cleaning House; Login and User Usage; Auditing Users and Logins; Memory Consumption; Conclusion; Chapter 5: Managing Scope; What Is Scope?; Managing Scope in Dynamic SQL; Using OUTPUT in Dynamic SQL; Table Variables and Temporary Tables; Table Variables; Temporary Tables; Global Temporary Tables; Using Permanent Tables for Temporary Storage; Output Data Directly to a Table from Dynamic SQL; Conclusion; Cleanup; Chapter 6: Performance Optimization
- Query ExecutionParsing; Binding; Optimization; Execution; Optimization Tools; Query Execution Plan; STATISTICS IO; Objects; Scan Count; Logical Reads; Physical Reads; STATISTICS TIME; Use All of These Tools!; Dynamic vs. Standard SQL; Query Parsing and Binding; Execution Plan Caching; Simplifying Queries; Paging Performance; Filtered Indexes; Cardinality; Statistics; Trace Flag 2371; Back to Dynamic SQL; Query Hints; Conclusion; CLEANUP; Chapter 7: Scalable Dynamic Lists; What Is a Dynamic List; Using XML to Create a Dynamic List; Set-Based String Building; Revisiting Security; Conclusion
- Chapter 8: Parameter Sniffing
