MSSQL网站制作之[转载] 编写平安的 Transact-SQL

其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。平安编写平安的Transact-SQLBartDuncan
MicrosoftCorporation本页内容简介回护开辟SQLServer的平安以最低权限帐户身份举行开辟依照回护T-SQL的最好办法懂得具有共同平安思索事项的T-SQL命令小结参考材料简介
关于怎样以平安的体例部署SQLServer，存在大批很好的信息源。可是，这些资本的方针用户一般都是那些对已开辟好的使用程序实行回护义务的数据库办理员。别的，另有良多内容会商了怎样编写平安的.NET和ASP.NET代码，个中包含会见SQLServer的.NET代码。但是，良多如许的资本存眷的是在使用服务器上运转的数据会见代码，而不是在SQLServer中实行的Transact-SQL(T-SQL)代码。本专栏则将注重力投射到怎样开辟在SQLServer上平安运转的T-SQL代码。
前往页首回护开辟SQLServer的平安
开辟平安T-SQL的第一步是回护开辟SQLServer的平安。为何要千方百计地锁定一个不保留实在数据的SQLServer实例，而从不将它展现给终极用户呢？这是由于，如许会强迫您编写更平安的T-SQL，而且当将您的使用程序部署到临盆中时，也会加倍简单地回护该使用程序。上面是几个详细的步骤，接纳这些步骤您就能够疾速回护开辟服务器：
•
在开辟或测试SQLServer中，最少应当有一个正在运转的、最新的ServicePack和SQL平安修补程序，如许才干确保您的客户可以在此SQLServer版本上乐成运转您的使用程序。
•
默许情形下，SQLServer2000ServicePack3a会禁用称为“交织数据库一切权链接”的不平安功效。在开辟服务器上安装SP3时，假如让该ServicePack禁用“交织数据库一切权链接”，则有助于考证您正在基于平安的服务器设置编写T-SQL代码。
•
找出开辟SQLServer上罕见的平安设置成绩的一种烦琐办法是针对该服务器运转MicrosoftBaselineSecurityAnalyzer。除这类办法以外，还能够使用本专栏“参考材料”部排列出的资本；这些资本供应了一些附加步骤，可匡助您回护开辟SQLServer的平安。

一般情形下，回护开辟服务器平安的最好办法，就宛如彷佛它正在临盆情况中运转那样对它举行回护。您离这个方针越靠近，那末就能够越自傲于您开辟的代码能够在一个平安的临盆情况中一般运转。
前往页首以最低权限帐户身份举行开辟
在开辟过程当中，人人都入神于利用具有sysadmin或dboSQLServer权限的帐户，直到部署之前才转换为一个权限更低的帐户。利用这类办法存在着一个成绩：将计划职员的权限集复原为最低的所需权限集与在开辟使用程序过程当中编写这些权限集比拟，前者要坚苦很多。

鉴于部署使用程序之前您要决意能够作废哪些权限，以是请不要利用SQLsysadmin帐户开辟T-SQL代码。假如利用SQLsysadmin帐户，大概会形成如许的了局，即使用程序会以比所需权限更多的特权帐户运转。因而，开辟时请改成利用具有最低权限的帐户。

利用如许的帐户举行开辟时，您会渐渐地降低授与的特定权限，以EXEC（实行）一些必须的存储历程、从某些表举行SELECT（选择）等。请编写这些GRANT语句，以即可以将一样的最低权限轻松部署到临盆情况中，而不会呈现任何基于推测的操纵。

这类理念一样合用于测试。实行一时测试和布局加倍庞大的测试时，所利用帐户具有的权限集和用户权限应当与在临盆情况中所利用帐户具有的权限集和用户权限完整不异。

在开辟过程当中利用最低权限帐户的另外一个长处在于，您能够制止不当心编写出必要伤害权限或太高权限的代码。比方，假定您必要在T-SQL中与第三方COM组件举行交互。为此，一种办法是发送一个SQL批处置命令，它间接挪用sp_OACreate和sp_OAMethod来利用该COM工具。在使用程序利用sysadmin帐户毗连SQLServer的开辟情况中，上述办法效果很好。可是，当您实验将已开辟完成的使用程序筹办用于临盆部署时，您就会发明假如利用权限较低的帐户，那末该办法不会见效。为了让该使用程序可以利用非sysadmin帐户在临盆情况中一般运转，您必需针对sp_OACreate显式授与EXECUTE权限。请思索一下，假如某个用户终极找到了一个办法，可使用该使用程序登录实行恣意代码，并使用此权限针对SQLServer实例化一个相似Scripting.FileSystemObject的COM工具，将会发生如何的平安隐患？
前往页首依照回护T-SQL的最好办法
进攻一系列称为“SQL注进式”的平安毛病是相当主要的。一般情形下，您会利用多层防护来抵抗SQL注进式打击：
•
实行用户供应输出的考证（比方，强迫数据范例和最年夜字符串长度）。
•
本义对数据库引擎大概具有特别意义的字符序列。在T-SQL中，注进式打击中最经常使用的两个字符串为单引号字符()和正文字符序列(--)。
•
在T-SQL语句中，请不要将用户供应的值举行内联。请改成利用预处置语句和参数化。

SQL注进式打击在其他一些中央有具体的申明，以是在此我就不花大批工夫来会商这个成绩的细节了。可是要夸大一点，SQL注进式成绩其实不只限于在使用层构建的T-SQL查询。只需实行一个部分由用户供应值构建的T-SQL查询，便可能会产生SQL注进式成绩。这就是说，一个在外部构建查询字符串、并经由过程EXEC()命令或sp_executesql存储历程实行该查询的存储历程也大概会遭到打击。请参阅“参考材料”部分取得一些资本链接，这些资本供应了各类SQL注进式打击范例的示例，还供应了一些回护代码免受这些打击的技能。

另外一个最好办法是制止针对基表授与权限。关于您但愿用户可以实行的查询，您应当将其打包在存储过程当中，并只对这些存储历程授与EXECUTE权限。假如您依照本指南举行操纵，即便用户想法跳过了您的使用程序，间接登录到数据库，他们也没法躲避您已在存储过程当中构建的任何数据考证、考核、营业划定规矩大概行级平安限定。
前往页首懂得具有共同平安思索事项的T-SQL命令
有一些T-SQL命令和扩大，它们具有本人共同的平安思索事项。个中一个是sp_OACreate及其相干的体系历程系列（比方sp_OAMethod、sp_OAProperty等）。之前，我们已经研讨过一个潜伏的平安成绩，经由过程授与使用程序登录间接会见这些历程的权限，会带来该平安成绩。为了不此成绩的产生，请相对不要编写间接挪用sp_OA历程的使用程序代码，而要将对这些历程的一切援用都打包在您本人的T-SQL存储过程当中，并只授与会见这些包装存储历程的权限。别的，请不要同意使用程序代码将COM工具或办法的称号作为可由包装历程无前提挪用的字符串举行传送。

另外一个具有共同平安风险集的内置SQLServer扩大为xp_cmdshell。这个体系存储历程能够运转任何可实行文件或体系命令。因为一些很明显的缘故原由，xp_cmdshell上的EXEC权限默许情形下仅为sysadmin用户，必需显现地为其他用户授与该权限。假如您必要使用程序在SQLServer上运转某个特定的命令或有用程序，则请注重，不要在使用程序中构建一个xp_cmdshell间接会见的相干内容。如许的风险与间接会见sp_OACreate的风险类似。一旦为某个帐户授与了xp_cmdshell的EXEC权限，该帐户不仅可以实行您但愿其会见的特天命令，并且可以实行成百上千个操纵体系命令和其他可实行文件。与sp_OACreate类似，一直将xp_cmdshell挪用打包在另外一个存储过程当中，制止间接在xp_cmdshell上授与EXECUTE权限。

您还应当制止将任何用户供应的字符串参数大概使用程序供应的字符串参数与将要经由过程xp_cmdshell实行的命令举行串连。假如没法到达上述请求，则必需懂得，有一个专门针对xp_cmdshell的潜伏的代码注进式打击（最少在SQLServer中）。以上面的存储历程为例：

CREATEPROCEDUREusp_DoFileCopy@filenamevarchar(255)ASDECLARE@cmdvarchar(8000)SET@cmd=copychare+@filename+desthareEXECmaster.dbo.xp_cmdshell@cmdGOGRANTEXECONusp_DoFileCopyTOmyapplogin

经由过程将xp_cmdshell挪用打包在您本人的存储过程当中并只针对该usp_DoFileCopy存储历程授与EXEC权限，您已制止了用户间接挪用xp_cmdshell以实行恣意命令。但是，以上面的shell命令拔出为例：

EXECusp_DoFileCopy@filename=&del/S/Qdesthare&

利用这个@filename参数，将要实行的字符串为copychare&del/S/Qdesthare&desthare。和号(&)被操纵体系命令注释器处置为命令分开符，因而该字符串将被CMD.EXE视为三个互不相干的命令。个中第二个命令(del/S/Qdesthare)将实验删除desthare中的一切文件。经由过程使用该存储过程当中某个shell命令拔出毛病，用户仍旧能够实行恣意操纵体系命令。针对此类打击举行进攻的一种办法是将命令字符串打包在一个T-SQL函数中，以下所示。这个用户界说的函数会增加shell本义符(^)，对呈现的任何&字符或其他具有特别意义的字符举行本义。

--Function:fn_escapecmdshellstring--Description:Returnsanescapedversionofagivenstring--withcarets(^)addedinfrontofallthespecial--commandshellsymbols.--Parameter:@command_stringnvarchar(4000)--CREATEFUNCTIONdbo.fn_escapecmdshellstring(@command_stringnvarchar(4000))RETURNSnvarchar(4000)ASBEGINDECLARE@escaped_command_stringnvarchar(4000),@curr_charnvarchar(1),@curr_char_indexintSELECT@escaped_command_string=N,@curr_char=N,@curr_char_index=1WHILE@curr_char_index<=LEN(@command_string)BEGINSELECT@curr_char=SUBSTRING(@command_string,@curr_char_index,1)IF@curr_charIN(%,<,>,|,&,(,),^,")BEGINSELECT@escaped_command_string=@escaped_command_string+N^ENDSELECT@escaped_command_string=@escaped_command_string+@curr_charSELECT@curr_char_index=@curr_char_index+1ENDRETURN@escaped_command_stringEND

上面是打消了命令shell拔出毛病以后的存储历程：

CREATEPROCEDUREusp_DoFileCopy@filenamevarchar(255)ASDECLARE@cmdvarchar(8000)SET@cmd=copychare+dbo.fn_escapecmdshellstring(@filename)+desthareEXECmaster.dbo.xp_cmdshell@cmd

第三个具有共同平安思索事项的T-SQL命令集为那些同意实行静态构建的查询的命令：EXEC()和sp_executesql。SQL注进式打击的风险并非制止静态SQL的独一来由。任何经由过程这些命令静态实行的查询都将在以后用户的平安高低文中运转，而不是在该存储历程一切者的高低文中运转。这就意味着，利用静态SQL大概会强迫您授与用户间接会见基表的权限。以上面的存储历程为例：

CREATEPROCdbo.usp_RetrieveMyUserInfoASSELECT*FROMUserInfoWHEREUserName=USER_NAME()

此历程会限定以后用户，使其没法检察其他任何用户的数据。可是，假如此过程当中的SELECT语句是经由过程静态EXEC()或经由过程sp_executesql实行的，您则必需授与用户对UserInfo表的间接SELECT权限，这是由于这个静态实行的查询是在以后用户的平安高低文中运转的。假如用户可以间接登录服务器，他们则可使用此权限跳过该存储历程供应的行级平安，检察一切用户的数据。
前往页首小结
总而言之，上面的倡议将有助于您开辟在SQLServer中平安运转的T-SQL代码：
•
回护您的开辟SQLServer的平安，就仿佛它是一个临盆服务器一样。如许有助于确保您开辟平安的代码，还能够匡助您界说使用程序一般运转所需的最低权限集。
•
举行T-SQL开辟和测试时请利用具有最低权限的SQLServer帐户。不要利用sysadmin或dbo帐户。
•
关于同意T-SQL实行恣意内部代码的存储历程，要十分注重，如sp_OACreate和xp_cmdshell。假如必需利用这些扩大，则必定要思索它们共同的平安隐患。
•
请依照回护T-SQL开辟的最好办法，个中包含：将用户供应的数据以显式参数举行传送、编写可制止SQL注进式打击的代码、制止利用不用要的静态SQL、授与会见存储历程的权限而不要授与间接会见基表的权限。
•
平安的T-SQL才干组成平安的使用程序。使用上面的资本能够确保您的服务器举行了平安设置，并确保您具有一个平安的数据库客户端使用程序。
前往页首参考材料
SQLServerSecurityResourcePage

BuildingSecureASP.NETApplications:Authentication,Authorization,andSecureCommunication

SQLServer2000SP3SecurityFeaturesandBestPractices:SecureMulti-tierDeployment
提供多语言支持，常见的编码如中文的GB2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.

作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。