# 数据库原生查询

## 使用场景

数据模型 SDK 提供了模型化的数据操作接口，为了进一步满足用户在特定场景下的需求：

- 利用数据库特定的功能或优化策略
- 数据模型 SDK 尚未提供的功能
  - 对于底层为 NoSQL 云数据库类型的模型，我们推荐使用云数据库的 SDK 中的 [聚合搜索](/minigame/dev/wxcloud/guide/database/aggregation/aggregation)、[事务](/minigame/dev/wxcloud/guide/database/transaction)等来完成数据模型尚未提供的功能，使用数据模型创建的 NoSQL 类型的模型，仍可以使用云数据库的所有能力。

对于底层为 MySQL 数据库类型的模型，我们提供了 MySQL 类型的原生数据库查询语句。

MySQL 数据库类型的模型，数据模型 SDK 提供两种模式的查询方法：

- `$runSQL`: 预编译模式, 通过参数化查询来避免 SQL 注入风险
- `$runSQLRaw` 原始模式, 更加灵活的模式，SQL 语句会当做原始字符串进行查询，存在 SQL 注入的风险

> 注意：
>
> 1. `runSQL`、`runSQLRaw` 接口仅支持在服务端调用，如云函数/云托管/服务器等场景，不支持小程序/web 端直接调用
> 2. 建议优先采用预编译模式，避免 SQL 注入风险
> 3. 当前仅开放了 `select` 语句，如果有其他 SQL 语句需求，请联系我们

### 预编译模式 `$runSQL`

预编译模式下使用参数化查询设计，结合静态模板语法和动态运行时参数，以实现灵活的数据交互。

允许开发者通过 Mustache 变量绑定语法（`{{ }}`）直接在 SQL 查询中嵌入静态参数，同时也支持在运行时通过 `$runSQL()` 方法执行时动态传递参数，可以避免直接拼接字符串导致 SQL 注入的风险。

详情可参考 [`$runSQL`](../../reference-sdk-api/model/model#runsql) 文档

例如：

1. **查询标题为"hello"的记录**：

   ```js
   const result = await models.$runSQL(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE title = {{title}} limit 10",
     {
       title: "hello",
     }
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"27"},"requestId":"16244844-19fe-4946-8924-d35408ced576"}
   ```

2. **查询阅读次数大于 1000 的记录**：

   ```js
   const result = await models.$runSQL(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE read_num > {{num}} limit 10",
     {
       num: 1000,
     }
   );

   console.log(result);
   // {"data":{"total":0,"executeResultList":[],"backendExecute":"23"},"requestId":"2f06b68f-e869-45cb-bb0d-82d50b3dcde0"}
   ```

3. **查询最后更新时间在某个特定时间戳之后的记录**（例如：2024-06-01 00:00:00）：

   ```js
   const result = await models.$runSQL(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE updatedAt > UNIX_TIMESTAMP({{timestamp}})",
     {
       timestamp: "2024-06-01 00:00:00",
     }
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
   ```

4. **查询拥有特定 banner 图片的记录**：

   ```js
   const result = await models.$runSQL(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE banner = '{{url}}';",
     {
       url: "cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png",
     }
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
   ```

5. **查询作者联系电话以"1858"开头的记录**：

   ```js
   const result = await models.$runSQL(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE author_tel LIKE '{{tel}}';",
     {
       tel: "1858%",
     }
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
   ```

6. **查询并计算发布状态为 true 的记录数量**：

   ```js
   const result = await models.$runSQL(
     "SELECT COUNT(*) FROM `lcap-wzcs_iuujo7p` WHERE is_published = {{isPublished}};",
     {
       isPublished: true,
     }
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"COUNT(*)":1}],"backendExecute":"1717"},"requestId":"f323d96a-8863-48db-a132-ed0fb3fbc727"}
   ```

7. **查询并返回所有记录的标题和阅读次数**：

   ```js
   const result = await models.$runSQL(
     "SELECT read_num,title FROM `lcap-wzcs_iuujo7p`"
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"read_num":997,"title":"hello"}],"backendExecute":"1916"},"requestId":"845d3fd4-05ce-4277-9a73-2cdd9b5ce04f"}
   ```

### 原始模式 `$runSQLRaw`

在某些情况（例如动态表名等）下可能希望关闭预编译模式，我们也支持直接传入原始的 SQL 语句的方式来执行 SQL，这种情况下需要自行处理 SQL 注入的防范。

详情可参考 [`$runSQLRaw`](../../reference-sdk-api/model/model#runsqlraw) 文档

示例：

1. **查询标题为"hello"的记录**：

   ```js
   const result = await models.$runSQLRaw(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE title = 'hello' limit 10"
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"27"},"requestId":"16244844-19fe-4946-8924-d35408ced576"}
   ```

2. **查询阅读次数大于 1000 的记录**：

   ```js
   const result = await models.$runSQLRaw(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE read_num > 1000 limit 10"
   );

   console.log(result);
   // {"data":{"total":0,"executeResultList":[],"backendExecute":"23"},"requestId":"2f06b68f-e869-45cb-bb0d-82d50b3dcde0"}
   ```

3. **查询最后更新时间在某个特定时间戳之后的记录**（例如：2024-06-01 00:00:00）：

   ```js
   const result = await models.$runSQLRaw(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE updatedAt > UNIX_TIMESTAMP('2024-06-01 00:00:00')"
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
   ```

4. **查询拥有特定 banner 图片的记录**：

   ```js
   const result = await models.$runSQLRaw(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE banner = 'cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png';"
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
   ```

5. **查询作者联系电话以"1858"开头的记录**：

   ```js
   const result = await models.$runSQLRaw(
     "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE author_tel LIKE '1858%';"
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"
hello world
","title":"hello","type":"[\"test\",\"test\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\n\n\n\n","author_email":"a@qq.com","json":"{\"a\":\"1\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
   ```

6. **查询并计算发布状态为 true 的记录数量**：

   ```js
   const result = await models.$runSQLRaw(
     "SELECT COUNT(*) FROM `lcap-wzcs_iuujo7p` WHERE is_published = TRUE;",
     {
       isPublished: true,
     }
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"COUNT(*)":1}],"backendExecute":"1717"},"requestId":"f323d96a-8863-48db-a132-ed0fb3fbc727"}
   ```

7. **查询并返回所有记录的标题和阅读次数**：

   ```js
   const result = await models.$runSQLRaw(
     "SELECT read_num,title FROM `lcap-wzcs_iuujo7p`"
   );

   console.log(result);
   // {"data":{"total":1,"executeResultList":[{"read_num":997,"title":"hello"}],"backendExecute":"1916"},"requestId":"845d3fd4-05ce-4277-9a73-2cdd9b5ce04f"}
   ```

### SQL 注入防范

在使用 `$runSQLRaw` 原始模式时，由于 SQL 语句会被当做原始字符串进行查询，因此开发者需要自行确保 SQL 语句的安全性，避免 SQL 注入的风险。以下是一些防范 SQL 注入的措施：

1. **使用预编译模式**：如非必要，优先使用 `$runSQL` 预编译模式，利用参数化查询来避免 SQL 注入。

2. **对用户输入进行验证**：在将用户输入的数据用于 SQL 语句之前，进行严格的验证和过滤，确保输入数据的合法性。

3. **使用白名单验证**：对于用户可以输入的值，使用白名单来验证，只允许预定义的安全值。

4. **转义特殊字符**：对于无法使用参数化查询的情况，确保对用户输入的数据进行转义，特别是 SQL 语句中的特殊字符，如单引号 `'`。

5. **错误处理**：合理处理数据库查询错误，避免将详细的错误信息暴露给用户，这可能会泄露数据库结构信息，增加 SQL 注入的风险