SQL查询安全性及性能优化

SQL注入的原理 什么SQL注入 将SQL代码插入到应用程序的输入参数中，之后，SQL代码被传递到数据库执行。从而达到对应用程序的攻击目的。 注入原理 常见攻击方法 检测是否可以注入【检测注入点】 示例：http://192.168.0.1/temp.aspx?id=a or 1=1-- 如果上面语句可以执行说明可以实现注入，则可以 利用系统过程、系统表注入数据库 示例【给系统添加一个用户，黑客可以实现远程登录控制服务器】：http://192.168.0.1/temp.aspx?id=1;exec xp_cmdshell 'net user admin 123456 /add' 绕过程序的限制 示例：程序中往往限制单引号的数据，但是黑客传入单引号的ASCII码 跨站点注入 在Web页面挂某些恶意的HTML、JavaScript代码 防范SQL注入 限制错误信息的输出，避免错误信息的输出使得黑客知道网站的技术实现采用什么数据库，采用什么平台 示例：在Web.config文件中设置 <customErrors mode="On" defaultRedirect="error.aspx"> </customErros> 限制访问数据库账号的权限 在开发应用系统的时候就应该限制，给程序最小访问数据库的权限 使用参数命令传递参数 不要使用拼接字符串的方式构造SQL语句而采用参数命令 使用存储过程 存储过程在数据库中 只能执行存储过程中固定的代码 限制输入长度 防止黑客输入超大字符串，导致服务器瘫痪 防止黑客输入较长的恶意脚本等 实现方法：文本框的MaxLength属性 URL重写技术 示例： http://testWeb/news.aspx?id=111 重写成:http://testWeb/10101111.html 传递参数尽量不用字符串 http://testWeb/news.aspx?id=111 and 1=1【黑色部分给了SQL注入的机会】 SQL优化 为什么要优化 开发是对性能考虑不多【技术差、项目工期紧等原因没有考虑性能问题】 系统运行中，数据量扩大，访问量增多，蹩脚的SQL危害开始显露 低效SQL的危害 系统响应变慢，软件开发中的8秒定律，当打开一个软件或网页超过8秒时间还没有显示，则响应太慢。 死锁，当不同用户都访问某些资源的时候SQL语句不当导致死锁 客户失去信心，软件失败 低效SQL低效在哪里？  性能低下的根源  硬件原因，数据库的配置不合理，数据库的数据文件和日志文件没有分磁盘存放，会极大影响IO性能  没有建立索引，或者SQL没有走索引。在千万级数据的表上建索引是很有必要的。  SQL过于复杂，过长的SQL语句满足程序需求但是影响性能。子查询嵌套过多对性能有影响，查询关联的表特别多也影响性能  频繁访问数据等等 SQL如何被SQLServer执行的 SQL执行原理  解释：首先解释SQL语句【语法是否正确】  解析：检验语句的出现的对象是否有效【进行一个解析，要检查对象所有权的权限】  优化：【检查SQL语句是否能够使用索引，SQL语句如何执行效率高，制定一个执行计划】  编译：  执行SQL语句：【把编译过的查询要求提交并进行处理】 如何优化SQL 完善开发的管理 完善代码审核、测试机制，软件开发是艺术！ 检测SQL查询的效率 语法【对IO和Time对SQL执行进行统计】： SET STATISTICS IO ON SET STATISTICS TIME ON ------------SQL代码--------- SET STATISTICS IO OFF SET STATISTICS TIME OFF 注意：在检测之前要清理缓存，因为当我们执行SQL语句的时候查出的数据会在数据库中进行缓存，重新查询会返回缓存中的信息。 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE 经验：使用子查询嵌套不要过多，尽量使用表连接查询代替子查询，因为表连接查询效率稍高一点。 SQL优化工具 使用报表服务 通过Reporting Service查找低效SQL 选择 性能 - 批处理执行统计信息和性能相关的几个报表服务： 性能 - 对象执行统计信息 性能 - 按平均CPU时间排在前面的查询 性能 - 按平均IO次数排在前面的查询 性能 - 按总CPU时间排在前面的查询 性能 - 按IO总次数排在前面的查询 操作示范： 1. 数据引擎上点击右键—报表—标准报表—关心的方面 出现如下图： 说明：通过这个报表找到排在前10 的sql语句，如果这些语句是用户编写的sql语句，我可以对其进行优化。 我们可以根据这些信息筛选出需要优化的SQL语句进行优化 SQL优化经验  大表缺索引---必要字段上建立索引  索引碎片--维护阶段定期检测索引是否有碎片，如果过多就要对索引重新组织和重建  索引失效或者没有使用索引，例如：  or语句使用了没有索引的字段  对有索引的字段进行了某些函数操作 避免对索引字段进行数值操作 示例： SELECT * FROM TRAINEE WHERE TraineeNo+10 < 10000 --TraineeNo是索引字段 执行效果：表'Trainee'扫描计数1，逻辑读取189次，物理读取0次，预读0次 修改后： SELECT * FROM TRAINEE WHERE TraineeNo < 10000-10 执行效果：表'Trainee'扫描计数1，逻辑读取3次，物理读取0次，预读0次 使用Top语句限制返回的数据集 SELECT ID,TITLE,UNITPRICE FROM BOOKS WHERE AUTHOR = '马骏 主编' --数据库会扫描整个Books表的数据 修改为： SELECT TOP 1 ID,TITLE,UNITPRICE FROM BOOKS WHERE AUTHOR = '马骏 主编' --数据库只要找到一条符合条件的结果就立即返回了。  由于返回的数据量减少，IO性能提高了  前提：必须知道要返回的行数 select字段需要多少提取多少  按需检索字段，少使用Select *  使用*，返回了很多不需要的字段，增加IO负担。  使用*，由于没有明确指定要查询那些字段数据，数据库引擎要花费一定时间检索系统表，通过系统表[sysobjects、syscolumns]查询*代表那些字段  order by 后跟的字段尽量是索引字段,如果这个字段不是索引字段可以考虑时候可以给这个排序字段加上索引 使用存储过程优化 复杂的业务逻辑可以使用存储过程来实现 优点： 减少网络流量--将多种操作放在一个过程中，减少访问次数 速度快--执行是，数据库无需再次编译 适应性强--可以被多种应用程序调用 执行计划 什么是执行计划？ 执行计划可以理解为SQL语句的执行路径，通过图表的方式查看SQL的执行步骤 并不需要SQL语句真正执行，是优化器估算的计算 来自数据库的统计信息，统计信息月准确，执行计划月精确 说明：网络经验：后者效率高于前者，但是以上两个查询语句的性能通过查询计划可以看出其实是一样的，后者不比前者性能好。可能是SQLServer的后期版本进行了优化。 示例二： 网络经验：网络经验：三个效率是不一样的，但是我们发现经过SQLServer优化后性能一样。但我们并不能以此推翻前人经验。 执行计划总结 主要图表 检索表的所有行，当查询没有使用到索引时，查询所采用的形势即为表扫描 排序运算符可以读所有传入的行进行排序 扫描查询计划的列中指定的聚集索引 利用索引的查找功能从聚集索引中检索行 执行多表联接逻辑操作，嵌套循环联接通常使用索引在内部表中搜索外部表的每一行 从列中指定的非聚集索引中扫描所有行 利用索引的查找功能从非聚集索引中检索行 扫描和查找 聚集索引有扫描和查找 非聚集索引也有扫描和查找 它们的区别： 扫描：可以理解为对数据进行顺序访问，并未使用索引进行查找 查找：可以理解为用索引进行查找 因此查找效率高于索引扫描效率 执行计划的意义 对于我们开发高质量SQL是很有帮助的 首先可以帮助我们查看SQL语句是否利用到索引，比如很复杂的SQL语句中有些用到索引，但是执行计划中却没有索引查找，我们就可以分析相应的原因 然后我们可以对比不同的执行计划，比如达到同样效果，两个开发人员编写的不同SQL语句谁的效率更高我们就可以对比它们的执行计划来分析。执行计划比较复杂的ＳＱＬ语句质量就不是很高 我们还可以结合时间统计【set statistics TIME ON..】一起使用,通过和时间统计结合使用可以更好地发挥执行计划的作用 有了执行计划和执行时间我们就很容易判断一条SQL语句执行效率高不高