SQLSere11-存储过程.ppt

存储过程 本讲内容 SQL注入以及解决方法了解存储过程的优点掌握如何创建存储过程掌握如何调用存储过程 SQL风险分析 数据库结构的暴露有多大危险 等于一个富人告诉了小偷他家的位置 还告诉了他家有几个门 每个门锁有何特征 小偷剩下要做的就只剩配钥匙了 非法用户如何顺利逃避验证 奇怪现象 奇怪现象分析 输入值分析用户名 sino 用户名 or1 1 用户名 or1 1 updatesystemusersetuserpwd 22 根源 SQL注入攻击 SQL注入攻击SQLinjection 台湾称作SQL资料隐码攻击 简称注入攻击是发生于应用程序之数据库层的安全漏洞 简而言之 是在输入的数据字符串之中夹带SQL指令 在设计不良的程序当中忽略了检查 那么这些夹带进去的指令就会被数据库服务器误认为是正常的SQL指令而运行 因此招致到破坏 解决办法 封装后 存放数据库服务器 前台调用 福建师范大学软件人才培养基地 若该SQL语句的代码量很多select fromaccountwhereusername anduserpassword 同时在某个时刻有成千上万的用户要登陆这个系统 这时候将增大网络的流量 封装的意义 封装的意义 福建师范大学软件人才培养基地 封装的意义 存储过程概念 存储过程 StoredProcedure 类似于C语言中的函数是一组编译好的 存储在服务器上的 能完成特定功能的T SQL语句集合 设计数据库的一种对象 存储过程可以带参数 也可以返回结果 存储过程概念 存储过程可以包含数据操纵语句 变量 逻辑控制语句等 使用存储过程与函数优点 可重用性一旦命名并保存在数据库以后 任何应用都可以调用抽象和数据屏蔽用户只需知道存储过程与函数对外提供的功能 而无需知道其内部实现安全性通过存储过程与函数提供数据对象的操作权限 而不必给出存储过程与函数涉及到每个对象的权限 提高了安全性执行速度快通过缓存计划并在重复执行时重用来降低t sql代码的编译开销 减少网络流量 疑问 存储过程和用户自定义函数都是将sql代码组织成为可以重复调用的代码块 在服务器端编译执行 那他们有什么区别呢 存储过程和函数 函数缺陷一 对于外部数据库的业务操作例如 增删改 无法通过自定义函数封装 大家试想函数都是通过select调用 其无法独立运行 而相比存储过程可以独立执行业务处理 存储过程和函数 函数缺陷二 函数返回结果集不能返回多个 而存储过程可以 换句话说存储过程实现了函数不可实现的操作 创建存储过程 创建存储过程语法形式如下 createPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 存储过程调用Exec存储过程名 参数1 参数2 案例一创建一存储过程显示用户信息 创建一存储过程 实现用户登录验证 该存储过程有待登录框验证用户合法性使用 创建存储过程createprocedureproc check unamechar 10 upasswordchar 10 asselect fromuserswhereuname unameandupsd upasswordgo 调用存储过程 测试封装语句是否正确实现功能 execproc check jyl 123 案例二创建存储过程显示用户信息 2 创建如下存储过程实现用户信息查询a 存储过程p list 实现 查询all 将所有用户信息显示b 存储过程实现 查询 模糊查询指定用户名信息 createprocp listasselect fromaccountgoexecp list exec执行存储过程 createprocp list1 unamevarchar 10 asselect fromaccountwhereusernamelike uname goexecp list1 su 思考 普通用户无法理解提示的含义 所以需要开发人员转化成可理解的业务提示 可选解决办法 通过状态返回 前台读取状态值转换为用户可理解的错误提示 带参数输出存储过程 修改存储过程p add 如果输入的用户存在 返回0 表示失败 否则实施插入语句 返回1 表示注册成功 案例要点 存储过程如何返回输出状态 注意 return只能返回一个值 一般用其返回状态值 带参数传递的存储过程举例 为计费系统创建一个实现输入一个有效客户ID 查询其联系人的存储过程 分析 输入参数 接收给定的客户id 输出参数 输出联系人姓名 两个参数类型要与建表时候对应的列的数据类型一致 如何调用该存储过程 如查询客户id为20100146客户的联系人 带参数传递的存储过程举例 如果输入的客户id不存在 调用存储过程时返回空消息 如何改进存储过程 使得不存在的客户返回 不存在 信息 带参数输出存储过程 修改存储过程p add 如果输入的用户存在 返回0 表示失败 否则实施插入语句 返回1 表示注册成功 输出存储新纪录的标识 以便进行调用的应用程序可立即访问自动生成的ID号 提示 通过 identity获取新纪录的标识值 案例要点 存储过程如何带多个输出值 带参数输出存储过程 27 常用的系统存储过程4 1 28 EXECsp databasesEXECsp renamedb Northwind Northwind1 USEstuDBGOEXECsp tablesEXECsp columnsstuInfoEXECsp helpstuInfoEXECsp helpconstraintstuInfoEXECsp helpindexstuMarksEXECsp helptext view stuInfo stuMarks EXECsp stored procedures 常用的系统存储过程4 2 修改数据库的名称 单用户访问 列出当前系统中的数据库 当前数据库中查询的对象的列表 返回某个表列的信息 查看表stuInfo的信息 查看表stuInfo的约束 查看表stuMarks的索引 查看视图的语句文本 查看当前数据库中的存储过程 演示 常用的存储过程 29 常用的扩展存储过程 xp cmdshell可以执行DOS命令下的一些的操作以文本行方式返回任何输出调用语法 EXECxp cmdshellDOS命令 NO OUTPUT 常用的系统存储过程4 3 30 常用的系统存储过程4 4 USEmasterGOEXECxp cmdshell mkdird bank NO OUTPUTIFEXISTS SELECT FROMsysdatabasesWHEREname bankDB DROPDATABASEbankDBGOCREATEDATABASEbankDB GOEXECxp cmdshel