Documentdb Sql 简明教程

DocumentDB SQL - Parameterized

在关系数据库中，参数化查询是一个在其中为参数使用占位符，并在执行时提供参数值的查询。DocumentDB 也支持参数化查询，并且参数化查询中的参数可以使用熟悉的 @ 符号表示。使用参数化查询的最重要原因是避免 SQL 注入攻击。它还可以提供对用户输入的可靠处理和转义。

In relational databases, a parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. DocumentDB also supports parameterized queries, and parameters in parameterized query can be expressed with the familiar @ notation. The most important reason to use parameterized queries is to avoid SQL injection attacks. It can also provide robust handling and escaping of user input.

让我们来看一个将使用 .Net SDK 的示例。以下是将删除集合的代码。

Let’s take a look at an example where we will be using the .Net SDK. Following is the code which will delete the collection.

private async static Task DeleteCollection(DocumentClient client, string collectionId) {
   Console.WriteLine();
   Console.WriteLine(">>> Delete Collection {0} in {1} <<<",
   collectionId, _database.Id);
   var query = new SqlQuerySpec {
      QueryText = "SELECT * FROM c WHERE c.id = @id",
      Parameters = new SqlParameterCollection { new SqlParameter { Name =
         "@id", Value = collectionId } }
   };

   DocumentCollection collection = client.CreateDocumentCollectionQuery(database.SelfLink,
      query).AsEnumerable().First();

   await client.DeleteDocumentCollectionAsync(collection.SelfLink);

   Console.WriteLine("Deleted collection {0} from database {1}",
      collectionId, _database.Id);
}

参数化查询的构造如下。

The construction of a parameterized query is as follows.

var query = new SqlQuerySpec {
   QueryText = "SELECT * FROM c WHERE c.id = @id",
   Parameters = new SqlParameterCollection { new SqlParameter { Name =
      "@id", Value = collectionId } }
};

我们不会对 collectionId 进行硬编码，因此此方法可用于删除任何集合。我们可以使用“@”符号为参数名称添加前缀，类似于 SQL Server。

We are not hardcoding the collectionId so this method can be used to delete any collection. We can use ‘@’ symbol to prefix parameter names, similar to SQL Server.

在上述示例中，我们通过 Id 查询一个特定集合，其中 Id 参数在此 SqlParameterCollection 中定义，该 SqlParameterCollection 已分配给此 SqlQuerySpec 的参数属性。然后，SDK 负责为 DocumentDB 构造最终查询字符串，其中嵌入了 collectionId。我们运行查询，然后使用其 SelfLink 删除集合。

In the above example, we are querying for a specific collection by Id where the Id parameter is defined in this SqlParameterCollection assigned to the parameter’s property of this SqlQuerySpec. The SDK then does the work of constructing the final query string for DocumentDB with the collectionId embedded inside of it. We run the query and then use its SelfLink to delete the collection.

以下是 CreateDocumentClient 任务实现。

Following is the CreateDocumentClient task implementation.

private static async Task CreateDocumentClient() {
   // Create a new instance of the DocumentClient
   using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
      database = client.CreateDatabaseQuery("SELECT * FROM
         c WHERE c.id = 'earthquake'").AsEnumerable().First();

      collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
         "SELECT * FROM c WHERE c.id = 'myfirstdb'").AsEnumerable().First();

      await DeleteCollection(client, "MyCollection1");
      await DeleteCollection(client, "MyCollection2");
   }
}

当代码执行时，它会产生以下输出。

When the code is executed, it produces the following output.

**** Delete Collection MyCollection1 in mydb ****
Deleted collection MyCollection1 from database myfirstdb

**** Delete Collection MyCollection2 in mydb ****
Deleted collection MyCollection2 from database myfirstdb

让我们来看另一个示例。我们可以编写一个将姓氏和地址州作为参数的查询，然后根据用户输入为 lastname 和 location.state 的各种值执行该查询。

Let’s take a look at another example. We can write a query that takes last name and address state as parameters, and then executes it for various values of lastname and location.state based on the user input.

SELECT *
FROM Families f
WHERE f.lastName = @lastName AND f.location.state = @addressState

然后可以将此请求作为一个参数化 JSON 查询发送给 DocumentDB，如下面的代码所示。

This request can then be sent to DocumentDB as a parameterized JSON query as shown in the following code.

{
   "query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND
      f.location.state = @addressState",

   "parameters": [
      {"name": "@lastName", "value": "Wakefield"},
      {"name": "@addressState", "value": "NY"},
   ]
}