组合SQL语句与参数的问题

Question

我知道一般情况下需要防止SQL注入的话，都用参数将变量带入SQL语句再执行，而不用组合SQL语句的方式，但今天听人说，参数的方式会导致SQL执行性能下降，而且新手才用参数的方式，高手以及大型项目都用组合SQL语句的方式，对此表示很不理解……而且据我所知，SQL会将编译后的SQL语句缓存在内存中，遇到相同的语句不再编译，但如果是组合SQL语句的方式，会导致每次都要编译，性能降低。想听听各位关于这个问题的看法……

Answer 1

动态sql每次都要编译，安全性也低，编写的时候调试起来也不好用，我是没听说过用组合sql反而更好的。个人还是强烈建议用参数。其实你可以用两种方法建两个查询，然后看看执行计划。

Answer 2

个人比较鄙视说这句话的人：但今天听人说，参数的方式会导致SQL执行性能下降，而且新手才用参数的方式，高手以及大型项目都用组合SQL语句的方式

Answer 3

我看的《sql编程规范》有这么一句话：动态sql（就是你说的组合sql）是sql注入的元凶。

Answer 4

exec 动态语句会导致重编译
而相对的，使用sp_executesql可以有效的避免这种情况
以下是测试代码，这个代码并不是我写的动态语句exec与sp_executesql执行计划区别 
--測試如下(Windows2003+SQL2005 sp3) 
USE tempdb 
go 
IF OBJECT_ID ( 'Test' ) IS NOT NULL 
    DROP TABLE Test 
SELECT * INTO Test FROM syscolumns 
go --step1: 
DBCC FREEPROCCACHE -- 清空緩存中的執行計劃 
--step2:( 執行3次 ) 
DECLARE @TabName sysname , @ID INT , @SQL NVARCHAR ( 4000) 
SET @TabName= 'Test' 
SET @ID= 102--@ID{101,102,103} -- 輸入次值，執行3次 
SET @SQL= N'SELECT * FROM ' + @TabName+ ' WHERE ID=' + RTRIM ( @ID)+ ' order by ID asc' 
EXEC ( @SQL) -- 查看產執行計劃 
SELECT dbName= DB_NAME ( dbID ) , Cacheobjtype, objtype, objid , sql 
FROM sys.syscacheobjects WHERE cacheobjtype= 'Compiled Plan'   AND objtype IN( 'Adhoc' , 'prepared' ) AND sql LIKE '%Test%' AND sql NOT LIKE '%syscacheobjects%' AND sql NOT LIKE '%msdb.%' -- 執行計劃產生3次 
/* 
dbName  Cacheobjtype    objtype objid   sql 
tempdb  Compiled Plan   Adhoc   319724907   SELECT * FROM Test WHERE ID=102 order by ID asc 
tempdb  Compiled Plan   Adhoc   513201771   SELECT * FROM Test WHERE ID=103 order by ID asc 
tempdb  Compiled Plan   Prepared    475976984   (@1 tinyint)SELECT * FROM [Test] WHERE [ID]=@1 ORDER BY [ID] ASC 
tempdb  Compiled Plan   Adhoc   674221447   SELECT * FROM Test WHERE ID=101 order by ID asc 
*/ go 
--step1: 
DBCC FREEPROCCACHE -- 清空緩存中的執行計劃 --step2:( 執行三次 ) 
DECLARE @TabName sysname , @ID INT , @SQL NVARCHAR ( 4000) 
SET @TabName= 'Test' 
SET @ID= 103--@ID{101,102,103} -- 輸入次值，執行三次 
SET @SQL= N'SELECT * FROM ' + @TabName+ ' WHERE ID=@ID order by ID asc' 
exec sp_executesql @stmt= @SQL, @Params= N'@ID int' , @ID= @ID 
SELECT dbName= DB_NAME ( dbID ) , Cacheobjtype, objtype, objid , sql FROM sys.syscacheobjects WHERE cacheobjtype= 'Compiled Plan'   AND objtype IN( 'Adhoc' , 'prepared' ) AND sql LIKE '%Test%' AND sql NOT LIKE '%syscacheobjects%' AND sql NOT LIKE '%msdb.%' -- 執行計劃產生1次 
/* 
dbName  Cacheobjtype    objtype objid   sql 
tempdb  Compiled Plan   Prepared    421211796   (@ID int)SELECT * FROM Test WHERE ID=@ID order by ID asc 
*/

Answer 5

这个绝对顶1楼的，拼接SQL语句安全性，效率是非常低的。
拼接SQL好处可能就是对于不太熟悉SQL语句的程序员容易上手一点，以及某种意义上的系统移植方便一点。除此之外，一无是处。

Answer 6

动态sql对于那些复杂的逻辑、并且不能用case when来替代的部分，的确具有其优势，但是每次给别人找问题，一大片红色的字，看的我眼痛

Answer 7

还有一个说法不知道对不对：当年的那些程序员，受到思想方式、开发工具等多种因素的限制，只能使用拼接SQL语句的方式写程序，并且养成了习惯，而如今这些人也都做到公司管理层一级，接收新事物新思潮的能力相对弱些，他们更愿意使用保守的方式做事。