动态 SQL 及安全性(Birt vs 润乾)

静态 SQL 与动态 SQL

对于题目中提到的动态 SQL,显然应该有一种静态 SQL 与之相对,那么两者应该怎么理解?我们先来个基本的认识。

静态 SQL 之“静态”,意味着在执行之前就已经明确了该 sql 在数据库执行后的业务含义,也就是对于做啥事儿我们是清楚的,只不过需要知道这事儿的具体内容有哪些。比如“select userID,username from users where deptName=’销售部’”,意思就是查销售部的所有用户 ID 及用户名。 再稍微灵活一点,可以写作“select userID,username from users where deptName=?”,此时语句中的“?”传递哪个部门名称,相对应的就查哪个部门的用户信息。但,sql 本身所要做的“查询某部门下的用户信息”这个事情我们是完全明确的,不确定的只是用户有多少,各自的信息是什么。

而动态 SQL 之所以称为“动态”,就是在是否“明确业务含义”这一点上是“不明确”的,也就是说 sql 应该执行出啥结果,我们本身并没有明确的预期,包括查什么、用哪些条件以及怎么组合这些条件等——原则上,这些都可以随意选择组合。

比如:“select userID,userName from users where ${condition}”,此处的 condition 可以随意指定合理的查询条件,当传入“deptName=’xx 部门’”时,执行结果是查询某部门的用户信息;当传入“roleName=’技术主管’”时,则是按照角色筛选复合条件的用户信息。尽管都是返回用户信息,但不同条件下,我们可以认为是两个不同的业务,这里的 sql 我们就称之为是动态的,显然,这样的 sql 执行后得到的结果的业务含义也是完全不确定的。

上面动态 sql 的例子是条件不固定,实际上动态 sql 还允许返回字段不确定,如“select userID,${fields} from users where …”, 需要查询哪些字段,完全由动态指定的”fields”来确定,可以查 deptName,也可以查 userName 和 deptName,非常自由、灵活。

同理,使用的物理表也可能不固定,例如定义为“select … from ${table} where …”时,”table”给定啥值就从哪个表查数据。

结合两者的具体含义,可以分析得到各自的优劣所在:

l 静态 SQL: 功能固定,比较传统,但相对比较安全。

l 动态SQL 自由灵活,但同时不得不提到sql植入风险,一旦被攻击者利用可能的sql漏洞,会有相当严重的安全问题,如窃取用户信息、篡改数据等等。

而在常用的报表工具使用场景中,报表开发人员一般都没有DBA的功力,因此难以对sql的安全性问题考虑周全,动态SQL可能带来的风险也就很难排除。而要规避动态SQL风险,无非是下面两种方法:

1、 dba配合工作,尽可能协助提供安全性高的sql

2、 希望报表工具可以提供规避sql注入风险的方法。

方法 1 需要依赖外部DBA的配合,不是总能满足,所以,比较可行的,还是在考察报表产品时,考虑报表工具是否提供防sql植入风险的功能支持。不然,在安全第一的前提下,就只能不用动态sql,退而选择静态sql了,毕竟安全还是最重要的,一旦造成信息泄露,责任很难承担。

下面,我们就对比一下常见的两种报表工具,Birt和润乾报表,看看它们各自在动态SQL以及安全性方面的表现如何:

Birt 动态 sql 实现

Birt 动态 sql 的做法有两种:

1、 数据集中通过script拼接 sql

2、 应用程序来实现: 这个太麻烦,需要有Java开发能力,咱们就不介绍了,感兴趣的可以到网上搜索自行研究。

下面我们通过实例来讲解一下在数据集中通过script脚本拼接sql的方式。

注:例子以“员工表”为例,员工表内存放有“工资”及“应发工资”两个字段,我们通过动态SQL实现由终端用户选择应该查询“工资”列还是“应发工资”列

1png

1、 新建报表并新增数据源(hsql)

2png

2、 新增数据集

如,数据集名称为“dt,query text为“select * from 员工”。实际这里可以定义为空,但为了保存等操作不报错,这里需要随便写个 sql。

3png

3、 利用script,在data setbefore open事件中实现动态sql

点击 dt 数据集,选中 script 页,选择 beforeOpen 并编辑如下

4png

脚本如下(含义为当qField传进来是“工资”时,拼入工资字段查数据,为“应发工资”时则对应查询,默认查询工资列):

var query = this.queryText; //获取query text内定义的sql
query = "";
query ="SELECT 编号,部门,姓名 ,性别,出生日期,入职日期,籍贯,";
var queryField = params\["qField"\].value;
if(queryField!=null&&queryField!=""){
 query += queryField ;
}
else{
 query +=" 工资" ; //如果不给值或默认状态,查工资列
}
query += " as salary from 员工表";
this.queryText = query;

4、 报表模板设计

5png

前面 6 列为常规列,拖拽字段到 dataRow 即可,第 7 列位动态列,这里当根据传入参数的不同,显示不同的标题(header row)及数值(data row),其中

Header row 表达式为:params[“qField”]

6png

Data row 表达式为:dataSetRow[params[“qField”]]

7png

5、 结果展现

(1) 当输入qField参数为“应发工资”时

8png

结果

9png

(2) 当输入qField参数为“工资”时

10png

结果

11png

分析小结

暂且不考虑上述操作涉及的复杂的制作过程,即便辛苦做出了效果,我们也不难发现其中留有很大漏洞,那就是这种拼串的方法很容易出现 sql 植入问题。

比如,当攻击者尝试给 qFiled 参数传入“身份证号”,而恰好又存在该字段的话,用户的身份证号信息就全部暴露了,同样,用类似的方法还可以猜到“电话号码”、“家庭住址”,等等等等,这些敏感信息的全面泄露,意味着安全保障的不堪一击。

而针对脚本这种做法,想要全面规避风险非常麻烦,可能的方式是在脚本里加入大量的判断,尽力排除所有 “身份证”、“电话号码”、“手机号码”等各种情况。对于一两个报表这么搞还行,多了肯定就费劲了,并且后期维护也得蒙圈。

润乾报表动态sql实现

同样以“员工表”为例,员工表内存放有“工资”及“应发工资”,我们来实现由终端用户选择应该查询“工资”列还是“应发工资”列。

润乾报表采用动态参数实现:

(1) 连接demo数据源

(2) 用向导生成报表

通过向导,一步生成如下网格式报表模板

12png

(3) 修改数据集,增加动态列

A、增加报表参数“qFiled”

13png

B、 数据集sql中增加动态列

采用宏的方式引入,sql改为如下

14png

(4) 修改报表模板,增加动态列

15png

G1:=@qFiled

G2:=ds1.${qFiled}

(5) 效果展示

A、 qFiled传入“应发工资”

16png

结果

17png

B、 qFiled传入“工资”

18png

结果

19png

分析小结

很显然,仅从做法而言,润乾报表就比birt要简单的多,不需要写任何脚本即可实现,对开发人员的技术要求也不高,对应后期维护也就很轻松了。

当然这里也存在sql植入的风险,但是润乾报表作为一款商业软件,厂商已经为用户考虑到了,可以通过专门的配置规避风险,具体使用也很简单,但功能很强大。

关于sql植入及报表规避的有专门的文章做详细的介绍,可参考《报表的SQL植入风险及规避方法》url:** 报表的 SQL 植入风险及规避方法

总结

不管是开源还是商业报表,可能对于某功能的实现都没什么问题,但制作方法的简便性、考虑问题是否周全(比如安全性问题)等方面的差距就可能会非常明晰。

开源报表固然有它的优点——不用花钱!!! 但工作量相对要大很多,服务或支持也没有保障,只能靠用户自己埋头苦干去研究了。而商业报表则是一条捷径,虽然要花一点点 Money,但考虑到效率及安全性方面,那就真是完全可忽略的成本了。

更多报表及安全问题请查看:基础部署相关问题分类导航 报表类相关问题分类导航
* 报表的 SQL 植入风险及规避方法
* 动态交叉表头报表的制作
* 如何制作动态层分组报表
* 动态列报表的制作

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾