sql - 是否直接执行SQL错误的应用程序设计？

Question

我正在开发一个iOS应用程序，它是另一个项目的管理器/查看器。这个想法是该应用程序将能够将存储在数据库中的数据处理为许多可视化文件-总体效果类似于cacti。我正在使可视化完全可由用户配置:用户定义了她想要查看的内容并添加了限制。

例如，她可能会指定使用最近处于 Activity 状态且不在美国的用户帐户来绘制过去三周的指标图表。

我的问题是，我唯一想到的设计或多或少是将直接SQL从iOS应用传递到后端服务器，以针对数据库执行。我知道这是不好的做法，所有内容都应使用存储过程来编写。但是，我还应该如何保持足够的灵活性以保留完全由用户定义的查询？

当应用程序确实组成了SQL时，直接SQL不会被用户看到或注入。所有这些都在UIDateTimeChoosers，UIPickerViews等中抽象了。

Answer 1

数据库中的所有数据是否对所有用户都可用，还是只允许每个用户访问数据的子集？如果是后者，仅将数据库登录限制为只读访问权限还不足以保护您的数据。

作为一个简单的示例，用户可能会破坏您的界面，以便提交查询SELECT password, salt FROM users WHERE login = 'admin'，劫持响应以获取原始数据，并强行使用您的管理员密码。随着应用程序的普及，恶意用户的数量呈线性增长，直到最终其集体智慧超过了您团队的智慧为止。您不应该让自己陷入失败的境地。

您可以采用客户端应用程序发送的SQL查询，并尝试在服务器端解析该查询，以便对查询施加适当的限制，以使用户无法使用。但是到达那里需要您在服务器代码中编写一个小型SQL解析器，谁想做所有的工作？编写可以编写SQL的代码比编写可以读取SQL的代码要容易得多。

我的团队为一个相当复杂的Web应用程序中的报告界面解决了类似的问题，我们的方法如下:

由于您已经打算使用图形界面来构建查询，因此将来自界面元素的原始数据转换为表示用户输入(进而是查询)的数据结构将相当容易。例如，用户可能会使用您的界面指定条件，要求他们将结果限制在除John外的其他人于2010年5月5日收集的结果之内。 (假设John的UserID为3。)使用我们团队使用的JSON格式的一种变体，您只需将UI中的数据提取为类似以下内容的内容:

{ "ConditionType": "AND",
  "Clauses": [
    { "Operator": "Equals",
      "Operands": [
        { "Column": "CollectedDate" },
        { "Value": "2010-05-05" }
      ]
    },
    { "Operator": "NotEquals",
      "Operands": [
        { "Column": "CollectedByUserID" },
        { "Value": 3 }
      ]
    }
  ]
}

在客户端，创建这种数据结构与创建SQL查询的任务几乎是同构的，并且可能会更容易一些，因为您不必担心SQL语法。

我在这里掩盖了一些微妙之处。这仅代表查询的WHERE部分，并且必须存在于更大的对象( { "Select": ..., "From": ..., "Where": ..., "OrderBy": ... })中。同样，更复杂的情况也是可能的。例如，如果您要求用户能够指定多个表以及它们如何联接在一起，则在WHERE子句中将列指定为操作数时，必须更加具体。但是同样，所有这些都是您必须直接构建查询所要做的工作。

然后，服务器将反序列化此结构。 (值得指出的是，用户提供的列名不应太脏-我们将它们映射到应用程序中允许的列的列表中；如果该列不在列表中，则反序列化将失败，并且用户将错误消息。)使用简单的对象结构，对查询进行更改几乎是微不足道的。服务器应用程序可以修改WHERE子句列表以应用适当的数据访问限制。例如，您可能会说(用伪代码) Query.WhereClauses.Add(new WhereClause(Operator: 'Equals', Operands: { 'User.UserID', LoggedInUser.UserID } ))。

然后，服务器代码将对象传递到一个相对简单的查询生成器中，该查询生成器遍历对象并拆分回SQL查询字符串。这比听起来容易，但要确保所有用户提供的参数都可以干净地传递。不要清理-使用参数化查询。

由于以下几个原因，这种方法最终对我们非常有效:

它使我们能够分解从图形界面编写查询的复杂性。

确保用户生成的查询不会脏执行。

它使我们能够向查询中添加任意子句以获取各种访问限制。

它具有足够的可扩展性，因此我们能够做一些漂亮的事情，例如允许用户搜索自定义字段。

从表面上看，这似乎是一个复杂的解决方案，但是我的团队发现这样做有很多好处，并且实现是干净且可维护的。