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

最近看到一篇“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：会导致ＳＱＬ注入漏洞(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 没有什么用处。