带参数的查询

机器翻译

本页由英文原文机器翻译而来，尚未经过母语审校，欢迎在 GitHub 上提出修改。内容如有出入，以英文原文为准。

任务： 安全地列出名字中包含 URL 所要求内容（/?name=mouse）的商品。

yaml

# Recipe: a query that takes a value from the URL, bound as a parameter.
paths:
  components: ./components
  migrations: ./migrations

datasources:
  db:
    driver: sqlite
    database: ./data/shop.db

sql

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Notebook', 3500.0), ('Mouse', 80.0), ('Monitor', 1200.0), ('Mousepad', 25.0);

SQL 中的每个 :name 都绑定到同名的 q:param：值与 SQL 文本分开发送到数据库，并先按它的 type 转换。没有 q:param 的 :name 无法通过解析，所以不可能意外地把一个值粘贴进 SQL。

xml

<q:component name="Products">
  <!-- ?name=mouse from the URL; empty when it is not there. -->
  <q:set name="term" value="{query.name}" default="" />

  <!-- :pattern is bound to the q:param: the value is sent to the database
       apart from the SQL, so it can never change what the SQL does. -->
  <q:query name="products" datasource="db">
    SELECT name, price FROM products WHERE name LIKE :pattern ORDER BY price
    <q:param name="pattern" value="%{term}%" type="string" />
  </q:query>

  <ui:window title="Products">
    <ui:text>{products_result.recordCount} products</ui:text>
    <ui:table source="{products}">
      <ui:column key="name" label="Name" />
      <ui:column key="price" label="Price" />
    </ui:table>
  </ui:window>
</q:component>

最后一个测试在 URL 中发送 SQL。它只是一段要搜索的文本：没有哪个商品的名字包含它，数据表也完好无损。

xml

<q:test name="without a filter, every product" page="/">
  <test:visit />
  <test:expect text="4 products" />
  <test:expect text="Notebook" />
</q:test>

<q:test name="the value from the URL filters" page="/">
  <test:visit name="mouse" />
  <test:expect text="2 products" />
  <test:expect text="Mousepad" />
  <test:expect no-text="Monitor" />
</q:test>

<q:test name="SQL in the URL is only text to search for" page="/">
  <test:visit name="' OR '1'='1" />
  <test:expect text="0 products" />
  <test:expect table="products" count="4" />
</q:test>

text

tests/products.test.q
  PASS  without a filter, every product
  PASS  the value from the URL filters
  PASS  SQL in the URL is only text to search for
3 passed, 0 failed

参见 DB-1。

带参数的查询 ​

带参数的查询