将SQL保存在c#源代码或Stored Procs中有哪些优点/缺点?我一直在和一个朋友讨论这个问题,我们正在做一个开源项目(c# ASP。网论坛)。目前,大多数数据库访问都是通过在c#中构建内联SQL并调用SQL Server DB来完成的。所以我在试着确定,对于这个特定的项目,哪个是最好的。

到目前为止,我有:

in Code的优点:

更容易维护-不需要运行SQL脚本来更新查询 更容易移植到另一个DB -没有pros到移植

存储Procs的优点:

性能 安全


当前回答

@Terrapin - scprocs同样容易受到注射攻击。正如我所说:

总是参数化所有的查询-永远不要内联用户输入的东西,你会没事的。

这适用于sprocs和动态Sql。

我不确定不重新编译你的应用程序是一个优势。我的意思是,在重新上线之前,您已经针对该代码(应用程序和DB)运行了单元测试。


@Guy -是的,你是对的,sproc确实让你控制应用程序用户,这样他们就只能执行sproc,而不是底层的操作。

我的问题是:如果所有人都通过你的应用程序访问它,使用连接和用户只有有限的更新/插入权限等,这个额外的级别是否增加了安全性或额外的管理?

我的观点是后者。如果他们已经破坏了您的应用程序,可以重新编写它,那么他们还有很多其他的攻击可以使用。

如果动态内联代码,Sql注入仍然可以针对这些spprocs执行,所以黄金法则仍然适用,所有用户输入必须始终参数化。

其他回答

存储过程的性能优势通常可以忽略不计。

存储过程的更多优点:

防止反向工程(当然,如果使用加密创建的话) 更好地集中数据库访问 能够透明地更改数据模型(无需部署新客户端);当多个程序访问相同的数据模型时尤其方便

我在其他答案中没有发现的一点是:

如果在您的环境中,数据库及其模式是体系结构的核心,应用程序的作用更小,那么更多地使用存储过程可能是有意义的,这可能有助于为所有需要访问DB的应用程序提供一个级别基础,从而减少代码重复(例如,您确定所有访问DB的应用程序都是用c#或其他。net语言编写的吗?)

另一方面,如果应用程序具有更核心的角色,而DB更多地充当应用程序的备份存储,那么减少存储过程的使用并通过提供一个公共持久性层(可能基于ORM工具/框架)来减少代码重复可能是明智的。

在这两种情况下,重要的是不要将DB视为存储过程的方便存储库。将它们保存在版本控制系统的源文件中,并尽可能地尝试自动化它们的部署(这实际上对所有与模式相关的构件都有效)。

@Keith

安全?为什么scprocs会更安全?

正如Komradekatz所建议的,您可以禁止访问表(对于连接到DB的用户名/密码组合),而只允许SP访问。这样,如果有人获得了数据库的用户名和密码,他们可以执行SP,但不能访问表或数据库的任何其他部分。

(当然,执行scproc可以给他们所有他们需要的数据,但这将取决于可用的scproc。给他们访问表的权限,他们就能访问所有东西。)

我参加的Microsoft TechEd安全会议的建议之一是,通过存储过程进行所有调用,并拒绝直接访问表。这种方法被宣传为提供额外的安全性。我不确定仅仅为了安全性是否值得这样做,但如果您已经在使用存储过程,那么这样做也无妨。

在我看来,你不能在这个问题上投赞成票或反对票。这完全取决于应用程序的设计。

我完全反对在三层环境中使用sp,在这种环境中,前端是应用服务器。在这种环境中,应用服务器用于运行业务逻辑。如果你额外使用sp,你就开始在整个系统中分配业务逻辑的实现,谁负责什么就会变得非常不清楚。最终你将得到一个基本上只会做以下事情的应用服务器:

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

因此,最终你的中间层运行在这个非常酷的4个服务器集群上,每个服务器都配备了16个cpu,实际上它什么都不会做!太浪费了!

如果你有一个臃肿的gui客户端,直接连接到你的数据库或甚至更多的应用程序,这是一个不同的故事。在这种情况下,SPs可以充当某种伪中间层,将应用程序与数据模型解耦,并提供可控的访问。