评“CPQuery, 解决拼接SQL的新方法”

2012-09-11 10:06

 

最近看到一篇“CPQuery, 解决拼接SQL的新方法”(http://www.cnblogs.com/fish-li/archive/2012/09/10/CPQuery.html),由于里面的思路基本是错误的,很担心影响园里的初学者。我在帖子里回复了几次,给博主 fish-li 一点小小的建议,没有想到,这位老兄辩论不过我,就直接删除我的回帖,包含别人在贴中对我的发问,如此恶劣行径,实属罕见。鄙人不才,不能保证每次发帖回帖都正确,也有在园中说错话道歉的时候,但从不删说错的、道歉的贴/回复。

 

特另单写一篇博文,阐述我的观点,也欢迎大家用踊跃讨论。

 

就事论事,这篇博文的错误有以下几点:

1. 该 CPQuery 尝试绕开 Ado.net 的 command.Parameters.Add() ,另写函数来对付 SQL 注入。这是很错误的做法。

很多人都以为,对付SQL 注入很容易,只要把单引号处理掉就行了,容易。其实,这种想法是非常错误的。

对于类似这样的代码:


query = query + " and ProductName like '" + p.ProductName + "'";
		

转换字符数据中间的特殊字符,实际上是一件相当困难的事情。

 

PHP 中有个 addslashes 函数,发布多年,漏洞不断,补丁也不断:

Addslashes() 漏洞(2004年,%00)

http://www.ugia.cn/?p=23

 

addslashes:会导致SQL注入漏洞(2006年,0xbf27)

http://blog.csdn.net/felio/article/details/1226569

 

GBK字符集下addslashes函数的注入漏洞及BUG的解决办法(2011 年,addslashes函数在进行转义的时候,只对二进制字符串操作二不考虑字符集)

http://www.itlearner.com/article/4824

 

从上面可以从侧面看出其中的难度。

在 SQL 注入(http://msdn.microsoft.com/zh-cn/library/ms161953.aspx) 这篇文章中,提到:


如果可能,拒绝包含以下字符的输入:
		

输入字符

在 Transact-SQL 中的含义

;

查询分隔符。

'

字符数据字符串分隔符。

--

注释分隔符。

/* ... */

注释分隔符。服务器不对 /* 和 */ 之间的注释进行处理。

xp_

用于目录扩展存储过程的名称的开头,如 xp_cmdshell

 

然而,这仅仅是 SQL Server 所需要处理的特殊字符,如果是其他数据库,又有别的字符需要处理,难度不是一点点。

如果用 Ado.net ,它所带的驱动程序,已经替我们做了这些事情,我们只需要调用 cmd.Parameters.Add(p); 即可。

 

当然了,这篇微软的文章,还提到,


"应检查所有调用 EXECUTE、EXEC 或 sp_executesql 的代码 ...在选择的每个存储过程中,验证是否对动态 Transact-SQL 中使用的所有变量都进行了正确处理..."。
		

这个难度就大了。存储过程中,难以调用 cmd.Parameters.Add(p) 来转换参数。看来在存储过程中,处理字符串起来,要格外小心。

尝试自己写 SQL 特殊字符处理函数,是一个特别没有“投资收益比”的事情,很难写好,就算写好了,也没有什么特别的收益,因为数据库的驱动程序,已经把这个做好了。无论是 C# 还是 Java 的程序员,都不应自己写 addslashes 函数。

 

在 Java 中,Apache 有个 http://commons.apache.org/lang/ ,本来提供了一个 StringEscapeUtils.escapeSql() 函数,但是后来主动去掉了这个函数,理由是:


StringEscapeUtils.escapeSql has been removed from the API as it was misleading developers to not use PreparedStatement
		

也就是说,这类 escapeSql() 函数,误导开发者不用开发语言本身的 PreparedStatement,是不好的。开发者主动去掉了此函数。

所以,最好不要写此类函数。

 

2. CPQuery 的另一个,据博主 fish-li 所说,“优点”之一,就是拼接 SQL 方便。然而,使用 cmd.Parameters.Add(p) 一样拼接 SQL 方便:


using (DbCommand cmd = this.mCon.CreateCommand()) 
{ 
    cmd.Transaction = this.mTrans; 

    string sql = "select ...  from ... where 1=1 and ... "; 

    if (uploadFlag != null) 
    { 
        sql += " and t." + TtDataVeccTransferBaseDao.COL_UPLOADFLAG + "= ?"; 

        DbParameter p = cmd.CreateParameter(); 
        p.ParameterName = "@COL_UPLOADFLAG"; 
        p.Value = uploadFlag; 
        cmd.Parameters.Add(p); 
    } 
    ...
}
		

以下几行,可以写在一个公共函数中:


DbParameter p = cmd.CreateParameter(); 
p.ParameterName = "@COL_UPLOADFLAG"; 
p.Value = uploadFlag; 
cmd.Parameters.Add(p); 
		

这样最后变成只有两行:


sql += " and t." + TtDataVeccTransferBaseDao.COL_UPLOADFLAG + "= ?"; 
addParameterValue(cmd,uploadFlag); 
		

 

从这两方面看来,CPQuery 本身出现的意义就有疑问。

一般来说,另写数据库访问组件/库,如果有以下几个方面的优势,是可以考虑的:

a. 代码自动生成,可以提高开发效率

b. 封装后更不容易出错,这也可以做,毕竟很多开发队伍中,都有新手。

c. 减少代码行数,实现同样的功能。

d. 提供 Ado.net 或者数据库驱动没有的额外功能

比如 Hibernate 的缓存(单表按主键查询,查询多次,实际只会执行一次,这里是假设数据在短时间内不会被其他人更改,利用缓存提高性能)。又比如,Ado.net 之后,微软相继发布 linq to sql, EF, 但是这两个都只支持 SQL Server ,于是很多人做出类似的、支持其他数据库的 linq/EF,拿出来卖,也不错。

 

从“CPQuery, 解决拼接SQL的新方法” 这篇文章所说,以上几点,它都没有做到。因此,这个 CPQuery 是没有什么用处的。

至于该文提到的几点,都是错误的:


2. 可能会影响性能:每条SQL语句都需要数据库引擎执行[语句分析]之类的开销。
3. 影响代码的可维护性:SQL语句与C#混在一起,想修改SQL就得重新编译程序,而且二种代码混在一起,可读性也不好。
		

在数据库系统中,SQL 的解析时间,基本可以忽略不计。相对于 SQL 及结果数据在网络中传输、SQL查询数据的搜索时间,那么一点点 SQL 的解析时间,根本可以忽略。所谓 SQL 解析时间要优化,纯粹是学院派的胡说。

至于 SQL语句与C#混在一起,这个要看个人的写程序风格了。用 Ado.net 还是用 CPQuery,都有这个问题。因此,这不是 CPQuery 的优点。

 

用 Ado.net 也可以写成这样的函数:

GetUploadData(uploadFlag), 然后把上面的代码放在里面。这样仅仅数据库相关的代码在一起,其他代码都在这个 GetUploadData 外面,也没有什么“SQL语句与C#混在一起”的问题。

至于“想修改SQL就得重新编译程序”,这是避免不了的。如果从业务逻辑上看,需要把 SQL 中的 > 改成 >= ,确实需要重新编译程序。用 CPQuery 还是用 Ado.net ,都是如此。

 

我个人觉得,CPQuery 的作者 fish-li ,对 Ado.net 的不熟悉,才导致了他写出了 CPQuery 。这本没有什么。Hibernate 的作者也是不愿意手工写 SQL 才弄出了一个 Java ORM,但是人家有自己的卖点:通过反射来把结果集数据,填充到 java 对象里,不用一个个字段赋值;通过缓存,单表按主键查询,查询多次,实际只会执行一次,提高性能。这两个卖点,都是 Java JDBC 本身没有的。

CPQuery 提供了什么 Ado.net 缺乏的东西么?看不到。

所以说,这个 CPQuery 没有什么用处。

 

 

欢迎转载,转载请注明出处: https://www.zheguisoft.com/staff_blogs/jacklondon_chen/2012, 及 https://www.cnblogs.com/jacklondon/archive/2012/09/11/2679738.html