SQL查询不是在一个地方,而是在传递多个系统层时动态组装的。这是个好习惯吗?

如果调试是一场噩梦,那么您已经有了答案:这不是一个最佳实践,将来应该避免这种模式。

就其价值而言,动态SQL并不一定是一件坏事,而且在大型业务应用程序中非常常见。如果做得正确,它可以提高代码的可维护性:

• 大多数像Hibernate这样的窗体都使数据库访问对程序员透明。例如,当您请求类似 User.GetByID(12) ORM将动态构造SQL,执行它,将字段映射到对象,然后返回它。作为一名程序员,您的大量时间被释放出来,因为您不再需要编写SQL查询,您可以只关注编写应用程序。 您可以查询数据库,而不必在应用程序的任何位置看到、触摸或闻到硬编码的SQL字符串,这样就可以在业务逻辑和数据访问层之间保持硬分离,而无需编写存储过程。

• 如果动态构造的SQL被很好地抽象,那么您可以交换数据库而不更改数据访问逻辑。例如,nhibernate根据配置文件中列出的任何数据库供应商生成正确的SQL,它只是简单地工作。 原则上,您可以对任何数据库供应商使用相同的数据访问逻辑。

• 我见过一些应用程序,其中包含1000个表示简单查询(即简单的选择、插入、更新和删除)的存储过程。维护这些数据库是非常麻烦的,尤其是当数据库模式频繁更改时。使用编写良好的DynmamicSQL,只需几个函数(即传递表名和一些参数的函数)就可以表示1000个等效查询。表单已经为您提供了这种抽象,所以对数据库模式的更改不需要更改应用程序代码(当然,除非您希望将新字段添加到业务对象中)。 您可以更改数据库模式(添加新字段、更改列的数据类型等),而无需更改应用程序代码或数据访问逻辑。

• 还有一些小的好处:

  • 不管有什么策略要求程序员或CMS将存储过程复制到源代码管理中,我们都忘记了。如果SQL是以编程方式构建的,那么它总是在源代码管理中。

• 关于存储过程相对于动态SQL的优势,有很多误解:

  • “动态SQL容易被SQL注入,存储过程不容易被注入”-这是不正确的。动态SQL参数化比较简单,事实上,参数化动态SQL比使用过多字符串连接的动态SQL更容易编写。即使如此,如果你和一个疏忽大意以至于写不安全的动态SQL的程序员一起工作,那么他也有可能写得同样糟糕(参见 this as a case study )

  • “存储过程是预编译的,因此运行速度更快。SQL Server无法缓存动态SQL的执行计划-至少在SQL Server中,这是绝对错误的。从SQL Server 7.0开始, a stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. “

  • “存储过程允许您在不重新部署应用程序的情况下更改SQL。”—这对于非常小的调整是正确的,但在95%的情况下,对数据库的更改仍然需要更改应用程序。

动态SQL不错 本身 但是有很多警告标志表明你什么时候做错了:

• 你应该 从未 请参阅regex解析SQL语句。
• 你应该 从未 将SQL块传递到数据访问层。例如,带有签名的方法 User GetUser(string whereclause) 非常脆弱。如果数据库模式更改,应用程序逻辑也会更改。
• 数据访问应该是类型安全的。您不应该传递未类型化的数据集或字符串——或者,如果传递了,您需要在将这些对象返回给用户之前将它们包装在一个类型化的对象中。
• 在滚动您自己的ORM之前,请始终考虑使用现有的ORM。

你可能已经注意到我在这篇文章中谈论了很多关于ORM的内容。这是因为,一般来说,硬编码SQL字符串是 坏事 . 使用动态SQL的正确方法是拥有一个动态生成SQL的工具,这正是许多ORM能够并且已经做到的。

我看到这种方法的一个大问题是没有明确的责任分离。如果多个层拥有创建SQL查询的逻辑的不同部分,那么您的应用程序将无法很好地扩展,并且很难修改。

动态查询还可以为您打开SQL注入。将它们与参数一起保存在存储过程中有助于降低这种风险。

如果您确实需要/希望在代码中动态地进行查询,我建议将它们都放在一个数据类或类似的类中。这样,如果需要任何更改,它们都在一个地方,另外,在创建新查询之前,您可以查看是否已经存在现有查询。如果您需要使用一个新的数据库,或者想要切换到存储过程,那么sicne会将所有内容打包在单个位置的调用中。

您没有错,我个人认为您应该使用存储过程,或者使用一个专门用于与数据库通信的类。只要在代码中“任何地方”组装查询,就会产生麻烦。

存储过程是必须的。这里有两个原则:良好的工程和安全

数据库既不是OOP,也不是函数式编程语言(尽管它们假装是)。数据库的强项在于它的酸性。为了利用这种力量,星展银行真的需要自己的一层来坚持这些原则。

其次,正如其他人提到的,数据注入攻击是一个巨大的威胁。当您在线上有数百万美元时,如果您的股东或客户不通过使用存储过程或某种类型的安全数据绑定来保护这些数据,则将被视为刑事过失。

如果必须动态构建查询,使其安全的最佳方法是只允许将输入转换为已知的整数/枚举值。没有自由文本输入。

规范化一点用户输入并对照数组中的已知值进行检查是很容易的。我通常跨多个层执行此操作,以便多次检查输入并对其进行规范化。

但又一次。存储过程是必须的。

虽然每个人似乎都指出SQL注入攻击是一个很大的安全漏洞,但我认为这并不是动态SQL造成的最大安全漏洞。如果前端代码编写正确,并且没有对存储过程进行正确编码,那么可以防止SQL注入攻击,但仍然会使自己处于易受攻击的状态。

我认为使用动态SQL最大的安全漏洞是现在前端用户需要访问底层表。这意味着有一个用户,不管是Windows身份验证还是SQL登录,都可以在数据库中胡乱使用几乎没有规则来阻止他们。我甚至无法计算我进入一家新商店并发现登录信息以明文形式保存在配置文件中的次数。即使不是这样,开发人员几乎总是知道帐户的用户名/密码。

大多数公司的安全漏洞(从长远来看)都是在公司内部——不满的员工,想通过销售SSN快速获利的人,或者那些认为为高中前女友查找医疗信息没有任何问题的人。此外,当开发人员 当然 他在开发服务器上。然后,您还可以让登录人员在某个地方向表中添加一行以“修复”问题,但他们不理解,每当您向表X添加一行时,都需要向表Y写入一行。如果通过存储过程强制执行了所有访问,则他们将无法执行该操作。

我想补充一点,现在我们有了Hibernate,而且在大多数项目中,LINQ存储过程都是多余的。我理解,在某些情况下,由于性能限制,存储过程不可避免,因为在应用服务器和数据库之间来回传送大量数据非常昂贵。

我当前的项目使用的是SP,而我以前的作业使用的是Hibernate。在当前的工作中,有更多的bug,如果我们使用ORM的话,这些bug将永远不会发生。另外,在sp项目中,我们有一个SQL注入错误,它在存储过程中。我从来没有在我以前研究过的代码库上看到过SQL注入问题:)似乎人们开始编写存储过程时大脑就会关闭。

为什么要有更多的bug并做更多的工作来取悦一些错误的宗教原则:“我们必须使用存储过程”。