Bài 59: sqlx Dynamic Query — QueryBuilder + Conditional WHERE

Sau bài học, bạn sẽ:

• Hiểu khi nào KHÔNG dùng query!/query_as! macro — dynamic SQL fail compile vì macro yêu cầu literal string.
• Phân biệt query() runtime vs QueryBuilder vs manual format!() (nguy hiểm).
• Hiểu cơ chế parameter binding chống SQL injection — Postgres parse SQL trước, bind value sau.
• Implement QueryBuilder<Postgres> với push() + push_bind() API stateful.
• Implement products search endpoint hoàn chỉnh với 6 filter: name LIKE, tag, price range min/max, sort, pagination.
• Hiểu pitfall ORDER BY với user input — whitelist mapping là bắt buộc.
• Lock pattern reuse apply_filter helper cho cả SELECT + COUNT query (DRY).

Macro sqlx::query! / sqlx::query_as! (lock B53) yêu cầu SQL string là literal compile-time — vì macro mở rộng lúc compile gọi PREPARE Postgres hoặc đọc .sqlx/ cache (lock B58) để biết kiểu cột + nullable. String runtime không tồn tại lúc compile → macro KHÔNG thể inspect.

Ví dụ KHÔNG compile được:

// File: crates/shop-db/src/products.rs (KHÔNG compile)
let mut sql = String::from("SELECT * FROM products WHERE 1=1");

if let Some(name) = filter.name {
    sql.push_str(" AND name ILIKE '%' || $1 || '%'");
}

// ERROR: sqlx::query! yêu cầu literal string
sqlx::query_as!(ProductRow, sql)
    .fetch_all(pool)
    .await
//                   ^^^ expected a string literal

Use case dynamic SQL thực tế trong Shop API:

• Search filter có 5-10 field optional (catalog endpoint).
• Sort theo column + direction user chọn (price_asc, created_at_desc, ...).
• Cursor pagination với WHERE conditional (B96).
• Admin filter audit log theo nhiều dimension (G27).

3 solution dynamic SQL — bảng quyết định:

┌────────────────────────┬──────────────┬──────────────┬─────────────────┐
│ Approach               │ Compile-time │ SQL injection│ Verbosity       │
├────────────────────────┼──────────────┼──────────────┼─────────────────┤
│ sqlx::query() runtime  │ KHÔNG check  │ AN TOÀN bind │ Trung bình      │
│ sqlx::QueryBuilder     │ KHÔNG check  │ AN TOÀN bind │ Thấp (sạch)     │
│ format!() interpolate  │ KHÔNG check  │ NGUY HIỂM    │ Thấp nhưng cấm  │
└────────────────────────┴──────────────┴──────────────┴─────────────────┘

Shop API lock dùng QueryBuilder cho mọi endpoint search/filter động — API stateful sạch hơn manual string + bind chain.

TUYỆT ĐỐI KHÔNG dùng format!() để interpolate user input vào SQL string. Đây là lỗ hổng SQL injection cổ điển:

// File: crates/shop-db/src/products.rs (NGUY HIỂM — KHÔNG dùng)
let user_input = "'; DROP TABLE products; --";
let sql = format!(
    "SELECT * FROM products WHERE name = '{}'",
    user_input
);

// SQL được gửi tới Postgres:
// SELECT * FROM products WHERE name = ''; DROP TABLE products; --'
//
// Postgres parse là 2 statement:
//   1. SELECT * FROM products WHERE name = ''
//   2. DROP TABLE products
//   3. -- ' (comment phần còn lại)
//
// → bảng products bị xóa

Lý do: user input chèn vào string nên có thể chứa SQL command (dấu nháy, semicolon, comment). PostgreSQL parser KHÔNG phân biệt được phần nào là "code do dev viết" với phần nào là "data do user gửi" — tất cả đều là SQL text.

Parameter binding (qua query() hoặc QueryBuilder) giải bài này bằng cách:

1. Postgres nhận SQL với placeholder $1, $2, ... và parse trước (chuẩn bị execution plan).
2. Postgres nhận giá trị bind sau qua wire protocol (binary, có metadata kiểu).
3. Value bind được coi là data thuần không thể đổi cấu trúc SQL — dù chứa dấu nháy, semicolon, DROP keyword cũng chỉ là string literal.

Lock decision Shop API:

• KHÔNG bao giờ format!() SQL với user input — mọi value đi qua .bind() hoặc push_bind().
• Chỉ allow format!() cho column name / table name từ WHITELIST (ORDER BY column, xem Bước 7) — vì identifier KHÔNG bind được qua placeholder, phải nội suy text.
• Mọi PR review reject ngay nếu thấy format!() SQL với biến từ DTO request.

sqlx::query() và sqlx::query_as::<_, T>() là runtime function nhận SQL string động + bind value qua .bind() chain. KHÁC macro query!:

• KHÔNG compile-time check — sai schema chỉ phát hiện lúc chạy (runtime error).
• Generic over connection type — chạy được với PgPool, PgTransaction, PgConnection.
• Struct mapping phải có #[derive(sqlx::FromRow)] (lock B53) thay vì macro tự generate FromRow impl.

Ví dụ tìm sản phẩm theo name (chưa dynamic):

// File: crates/shop-db/src/products.rs
use sqlx::PgPool;

async fn find_products_by_name(
    pool: &PgPool,
    name: &str,
) -> Result<Vec<ProductRow>, sqlx::Error> {
    let rows = sqlx::query_as::<_, ProductRow>(
        "SELECT id, name, slug, price, stock, description, tags, created_at, updated_at
         FROM products WHERE name ILIKE $1"
    )
    .bind(format!("%{}%", name))
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

ILIKE là toán tử PostgreSQL case-insensitive LIKE (lock Shop API cho mọi fuzzy text search). Lưu ý format!("%{}%", name) ở đây là format Rust string tạo pattern %iphone% cho LIKE — KHÔNG phải interpolate vào SQL. Giá trị %iphone% đi qua .bind() nên vẫn là parameter binding an toàn.

Bind chain pattern với nhiều param theo thứ tự $1, $2:

sqlx::query_as::<_, ProductRow>(
    "SELECT id, name FROM products
     WHERE price >= $1 AND stock > $2 AND tags && $3"
)
.bind(min_price)         // $1
.bind(min_stock)         // $2
.bind(required_tags)     // $3 — &[String]
.fetch_all(pool)
.await

Mạnh cho dynamic SQL nhưng phải tự build string conditional với String::push_str + tự đếm $N placeholder — verbose và dễ sai. QueryBuilder ở Bước 5 giải bài này.

sqlx cung cấp QueryBuilder — state machine append SQL fragment + bind param tự manage $N placeholder. API chính:

• QueryBuilder::new(prefix): khởi tạo với prefix SQL literal (vd "SELECT ... WHERE 1=1").
• .push(literal): append literal text (SQL fragment cố định, KHÔNG đi từ user input).
• .push_bind(value): append $N placeholder + bind value tương ứng. Tự tăng N nội bộ.
• .build_query_as::<T>(): build thành Query mapping T: FromRow.
• .build_query_scalar::<T>(): build cho scalar (vd COUNT(*) trả i64).
• .build_query(): build cho anonymous record (rare).

Ví dụ minh họa pattern (rút gọn — bản full Bước 8):

// File: crates/shop-db/src/products.rs
use sqlx::{Postgres, QueryBuilder, PgPool};
use rust_decimal::Decimal;

pub async fn search_products_v1(
    pool: &PgPool,
    name: Option<String>,
    min_price: Option<Decimal>,
    tag: Option<String>,
    per_page: u32,
    page: u32,
) -> Result<Vec<ProductRow>, sqlx::Error> {
    let mut qb: QueryBuilder<Postgres> = QueryBuilder::new(
        "SELECT id, name, slug, price, stock, description, tags,
                created_at, updated_at
         FROM products WHERE 1=1"
    );

    if let Some(n) = name {
        qb.push(" AND name ILIKE ");
        qb.push_bind(format!("%{}%", n));
    }

    if let Some(min) = min_price {
        qb.push(" AND price >= ");
        qb.push_bind(min);
    }

    if let Some(t) = tag {
        qb.push(" AND ");
        qb.push_bind(t);
        qb.push(" = ANY(tags)");  // GIN index B52
    }

    qb.push(" ORDER BY created_at DESC ");
    qb.push(" LIMIT ");
    qb.push_bind(per_page as i64);
    qb.push(" OFFSET ");
    qb.push_bind(((page - 1) * per_page) as i64);

    qb.build_query_as::<ProductRow>()
        .fetch_all(pool)
        .await
}

So sánh với query() runtime:

• Pros: state machine quản lý $N placeholder index tự động (không tự đếm + tự match value). Code dễ đọc hơn vì separation: SQL fragment riêng vs bind value riêng.
• Cons: vẫn không có compile-time check (giống query()). Lỗi schema phát hiện runtime — bù lại bằng integration test với DB thật (lock B58 pattern 1).

Pattern = ANY(tags) dùng Postgres array operator (lock B52 với GIN index): kiểm tra value bind có nằm trong array tags TEXT[] không. Index GIN scan rất nhanh ngay cả với hàng triệu row.

Extend file crates/shop-common/src/dto/product.rs (lock B42 folder structure) — thêm DTO query string cho search endpoint:

// File: crates/shop-common/src/dto/product.rs
use serde::Deserialize;
use validator::{Validate, ValidationError};
use rust_decimal::Decimal;

#[derive(Debug, Clone, Deserialize, Validate)]
pub struct ProductSearchQuery {
    #[serde(default)]
    pub name: Option<String>,

    #[serde(default)]
    pub tag: Option<String>,

    #[serde(default)]
    pub min_price: Option<Decimal>,

    #[serde(default)]
    pub max_price: Option<Decimal>,

    #[serde(default = "default_sort")]
    #[validate(custom(function = "validate_sort"))]
    pub sort: String,

    #[serde(default = "default_page")]
    #[validate(range(min = 1))]
    pub page: u32,

    #[serde(default = "default_per_page")]
    #[validate(range(min = 1, max = 100))]
    pub per_page: u32,
}

fn default_sort() -> String { "created_at_desc".to_string() }
fn default_page() -> u32 { 1 }
fn default_per_page() -> u32 { 20 }

fn validate_sort(sort: &str) -> Result<(), ValidationError> {
    const ALLOWED: &[&str] = &[
        "created_at_desc", "created_at_asc",
        "name_asc", "name_desc",
        "price_asc", "price_desc",
    ];
    if !ALLOWED.contains(&sort) {
        return Err(ValidationError::new("invalid_sort"));
    }
    Ok(())
}

7 field — đủ cho search endpoint thực tế:

• name, tag, min_price, max_price: 4 filter optional — Option<T> + #[serde(default)] để missing key → None (lock B23 convention).
• sort: enum string với custom validator + whitelist 6 option (xem Bước 7). Default created_at_desc đồng nhất convention list endpoint Shop API (B51 lock).
• page, per_page: pagination — validate range 1..=100 cho per_page anti-DoS (lock B23 cap 100), page tối thiểu 1.

Re-export top-level qua shop-common/src/dto/mod.rs:

// File: crates/shop-common/src/dto/mod.rs
pub use product::{
    CreateProductDto, UpdateProductDto, ProductResponseDto,
    ProductListResponse, ProductSearchQuery,  // NEW B59
};

Validator crate (lock B41) tự convert error qua impl From<ValidationErrors> for AppError → 422 envelope chuẩn lock B3.

PITFALL: parameter binding KHÔNG áp dụng cho identifier (column name, table name, ORDER direction). Postgres protocol bind chỉ chấp nhận value ở vị trí $1, KHÔNG cho phép ORDER BY $1 với $1 là tên cột — sẽ fail "syntax error at or near $1".

Hậu quả: nếu interpolate user input sort thẳng vào SQL bằng format!():

// NGUY HIỂM — KHÔNG dùng
qb.push(format!(" ORDER BY {}", user_sort));
// user gửi sort = "name; DROP TABLE products; --"
// SQL: ORDER BY name; DROP TABLE products; --
//      → SQL injection vẫn xảy ra DÙ dùng QueryBuilder

Solution: whitelist mapping qua function match trả &'static str literal. Vì giá trị return là literal định sẵn lúc compile, user input không thể đẩy text lạ vào SQL.

// File: crates/shop-db/src/products.rs
fn sort_to_sql(sort: &str) -> &'static str {
    match sort {
        "created_at_desc" => " ORDER BY created_at DESC",
        "created_at_asc"  => " ORDER BY created_at ASC",
        "name_asc"        => " ORDER BY name ASC",
        "name_desc"       => " ORDER BY name DESC",
        "price_asc"       => " ORDER BY price ASC",
        "price_desc"      => " ORDER BY price DESC",
        // Defensive fallback — validate_sort B41 đã reject 422
        // trước khi đến đây, nhưng vẫn fallback an toàn.
        _ => " ORDER BY created_at DESC",
    }
}

// Trong search_products:
qb.push(sort_to_sql(&filter.sort));   // ← literal string từ whitelist

2 lớp phòng thủ Shop API lock vĩnh viễn:

1. Validate sort field ở DTO layer qua validator crate #[validate(custom(function = "validate_sort"))] (B41 lock) — reject 422 trước khi đến handler.
2. Whitelist mapping function ở DB layer trả &'static str literal — phòng dev quên gọi .validate() hoặc thêm sort mới mà không update validate list.

Pattern này áp dụng cho mọi endpoint search Shop API tương lai có sort by user input — orders (B66), categories (B91), users (G14), reviews (G14). KHÔNG bao giờ format!() ORDER BY với user input.

Extend file crates/shop-db/src/products.rs — thêm ProductFilter struct + search_products function + apply_filter helper reuse cho SELECT + COUNT.

// File: crates/shop-db/src/products.rs
use sqlx::{Postgres, QueryBuilder, PgPool};
use rust_decimal::Decimal;

#[derive(Debug, Clone)]
pub struct ProductFilter {
    pub name: Option<String>,
    pub tag: Option<String>,
    pub min_price: Option<Decimal>,
    pub max_price: Option<Decimal>,
    pub sort: String,
    pub page: u32,
    pub per_page: u32,
}

pub async fn search_products(
    pool: &PgPool,
    filter: ProductFilter,
) -> Result<(Vec<ProductRow>, u64), sqlx::Error> {
    // SELECT query
    let mut qb: QueryBuilder<Postgres> = QueryBuilder::new(
        "SELECT id, name, slug, price, stock, description, tags, \
         created_at, updated_at FROM products WHERE 1=1"
    );
    apply_filter(&mut qb, &filter);
    qb.push(sort_to_sql(&filter.sort));
    qb.push(" LIMIT ").push_bind(filter.per_page as i64);
    qb.push(" OFFSET ")
      .push_bind(((filter.page - 1) * filter.per_page) as i64);

    let rows = qb
        .build_query_as::<ProductRow>()
        .fetch_all(pool)
        .await?;

    // COUNT query — reuse apply_filter
    let mut count_qb: QueryBuilder<Postgres> = QueryBuilder::new(
        "SELECT COUNT(*) FROM products WHERE 1=1"
    );
    apply_filter(&mut count_qb, &filter);

    let total: i64 = count_qb
        .build_query_scalar()
        .fetch_one(pool)
        .await?;

    Ok((rows, total as u64))
}

fn apply_filter(qb: &mut QueryBuilder<'_, Postgres>, filter: &ProductFilter) {
    if let Some(name) = &filter.name {
        qb.push(" AND name ILIKE ");
        qb.push_bind(format!("%{}%", name));
    }
    if let Some(min) = filter.min_price {
        qb.push(" AND price >= ");
        qb.push_bind(min);
    }
    if let Some(max) = filter.max_price {
        qb.push(" AND price <= ");
        qb.push_bind(max);
    }
    if let Some(tag) = &filter.tag {
        qb.push(" AND ");
        qb.push_bind(tag.clone());
        qb.push(" = ANY(tags)");
    }
}

fn sort_to_sql(sort: &str) -> &'static str {
    match sort {
        "created_at_desc" => " ORDER BY created_at DESC",
        "created_at_asc"  => " ORDER BY created_at ASC",
        "name_asc"        => " ORDER BY name ASC",
        "name_desc"       => " ORDER BY name DESC",
        "price_asc"       => " ORDER BY price ASC",
        "price_desc"      => " ORDER BY price DESC",
        _                 => " ORDER BY created_at DESC",
    }
}

Reuse apply_filter helper cho cả SELECT + COUNT là pattern lock vĩnh viễn (DRY) — đảm bảo total page count khớp số row thực sự match filter.

Refactor handler list_products ở crates/shop-api/src/routes/products.rs — đổi từ Pagination skeleton (lock B52) sang ProductSearchQuery đầy đủ:

// File: crates/shop-api/src/routes/products.rs
use axum::{extract::State, Json};
use shop_common::{
    dto::{ProductListResponse, ProductResponseDto, ProductSearchQuery},
    error::AppError,
};
use shop_db::products as db;
use validator::Validate;

use crate::{extractors::AppQuery, state::AppState};

pub async fn list_products(
    State(state): State<AppState>,
    AppQuery(query): AppQuery<ProductSearchQuery>,
) -> Result<Json<ProductListResponse>, AppError> {
    // 1. Validate sort whitelist + range page/per_page (B41 lock)
    query.validate()?;

    // 2. Build ProductFilter từ DTO
    let page = query.page;
    let per_page = query.per_page;
    let filter = db::ProductFilter {
        name: query.name,
        tag: query.tag,
        min_price: query.min_price,
        max_price: query.max_price,
        sort: query.sort,
        page,
        per_page,
    };

    // 3. Gọi service — trả (rows, total)
    let (rows, total) = db::search_products(&state.db, filter).await?;

    // 4. Map ProductRow → ProductResponseDto (lock B47)
    let items: Vec<ProductResponseDto> =
        rows.into_iter().map(Into::into).collect();

    // 5. Build envelope ProductListResponse (lock B47)
    let total_pages = ((total as f64 / per_page as f64).ceil() as u32).max(1);
    Ok(Json(ProductListResponse {
        items,
        total,
        page,
        per_page,
        total_pages,
    }))
}

Signature dùng AppQuery<ProductSearchQuery> (custom extractor lock B32) thay AppQuery<Pagination> skeleton trước đó — parse fail trả 400 envelope chuẩn. Sau .validate(), sort chắc chắn thuộc whitelist 6 option nên sort_to_sql không bao giờ rơi vào nhánh fallback.

Verify endpoint qua curl:

# Search theo name (ILIKE fuzzy)
curl 'http://localhost:3000/api/v1/products?name=iPhone' | jq

# Filter price range
curl 'http://localhost:3000/api/v1/products?min_price=1000000&max_price=50000000' | jq

# Filter by tag — GIN index B52 sẽ dùng
curl 'http://localhost:3000/api/v1/products?tag=apple' | jq

# Sort + paginate
curl 'http://localhost:3000/api/v1/products?sort=price_asc&page=2&per_page=10' | jq

# Invalid sort → 422 ValidationFailed
curl -i 'http://localhost:3000/api/v1/products?sort=hack'
# HTTP/1.1 422 Unprocessable Entity
# content-type: application/json; charset=utf-8
# {"error":"validation failed","code":"VALIDATION_FAILED",...
#  "fields":{"sort":["invalid_sort"]}}

Combo filter (mọi field cùng lúc):

curl 'http://localhost:3000/api/v1/products?\
name=iphone&\
tag=apple&\
min_price=10000000&\
max_price=50000000&\
sort=price_asc&\
page=1&\
per_page=20' | jq '.total, .total_pages, (.items | length)'

SQL được build thực tế (xem qua log sqlx::query):

SELECT id, name, slug, price, stock, description, tags,
       created_at, updated_at
FROM products WHERE 1=1
  AND name ILIKE $1
  AND price >= $2
  AND price <= $3
  AND $4 = ANY(tags)
ORDER BY price ASC
LIMIT $5 OFFSET $6;
-- $1 = '%iphone%', $2 = 10000000, $3 = 50000000,
-- $4 = 'apple', $5 = 20, $6 = 0

Suggested commit cho B59: B59: products search endpoint — ProductSearchQuery + search_products QueryBuilder + sort whitelist. KHÔNG quên chạy cargo sqlx prepare --workspace rồi commit .sqlx/ kèm (lock B58 workflow 6 bước).

• query!/query_as! macro static — KHÔNG handle dynamic SQL (yêu cầu literal string lúc compile).
• 3 solution dynamic SQL: query() runtime, QueryBuilder (recommended Shop API), format!() (NGHIÊM CẤM).
• format!() SQL với user input = SQL injection vulnerability — TUYỆT ĐỐI KHÔNG.
• Parameter binding (qua .bind() / push_bind()) — user input là data, không execute như code.
• QueryBuilder::new(prefix) + .push(literal) + .push_bind(value) — API stateful tự manage $N placeholder.
• State machine $N placeholder tự manage — sạch hơn query() runtime tự đếm.
• build_query_as::<T>() cho struct mapping; build_query_scalar() cho COUNT.
• ORDER BY whitelist pattern: validate sort field (B41 validator) + match function trả &'static str literal.
• ProductSearchQuery 7 field: name, tag, min_price, max_price, sort, page, per_page.
• Reuse apply_filter helper cho SELECT + COUNT — DRY pattern lock vĩnh viễn.
• tag = ANY(tags) dùng GIN index B52 — Postgres array operator scan rất nhanh.
• ILIKE case-insensitive LIKE search lock cho name/description fuzzy match.
• Refactor list_products → search_products hoàn chỉnh 6 filter — không còn skeleton.
• File path lock: extend crates/shop-db/src/products.rs + crates/shop-common/src/dto/product.rs.

Tự trả lời, đáp án ở cuối:

1. Tại sao query! macro KHÔNG handle dynamic SQL? Cho ví dụ fail compile cụ thể.
2. SQL injection với format!() — minh họa attack vector cụ thể (payload + SQL được build).
3. Parameter binding chống SQL injection ra sao? Cơ chế Postgres parse + bind chia làm mấy bước?
4. ORDER BY với user input — tại sao phải whitelist? format!() ở đây có an toàn không?
5. QueryBuilder.push_bind() vs query().bind() khác nhau ra sao? Khi nào chọn cái nào?

Bài 60: PostgreSQL JSONB + Array Indexing — bài CUỐI Group 6: JSONB operator chi tiết (->, ->>, @>, ?, ?|, ?&), JSONB index (GIN, jsonb_path_ops), array operator (= ANY, @>, &&), GIN array index, áp dụng metadata search Shop API + payment_payload query patterns.