SQL từ cơ bản đến nâng cao
Trang chủ / Module 14
🛡️ Module 14 / 16 · 6-7 giờ

SQL trong ứng dụng: injection, N+1, hàm/trigger, quyền, bulk

Cách dùng SQL an toàn và hiệu quả từ code ứng dụng: tham số hoá chống SQL injection, tránh N+1 của ORM, PL/pgSQL và trigger, phân quyền tối thiểu, timeout, bulk insert, connection pool và kiểm thử SQL.

SQL InjectionN+1TriggerPhân quyền
📌 Vì sao module này quan trọng

Phần lớn sự cố bảo mật và hiệu năng của hệ thống web đến từ ranh giới giữa code ứng dụng và SQL: chuỗi SQL nối tay (injection), vòng lặp gọi 1.000 truy vấn nhỏ (N+1), tài khoản DB quyền quá rộng, truy vấn không có timeout. Một người viết SQL giỏi nhưng hiểu sai ranh giới này vẫn tạo ra hệ thống dễ bị tấn công và chậm.

🎯 Mục tiêu học tập

  • Giải thích và phòng tránh SQL injection bằng truy vấn tham số hoá; biết giới hạn (định danh, ORDER BY động)
  • Nhận diện N+1 và sửa bằng JOIN / = ANY / json_agg / batch loading
  • Viết hàm PL/pgSQL và trigger đúng chỗ (updated_at, audit) và biết khi nào không nên dùng
  • Áp dụng quyền tối thiểu (role, GRANT), timeout, connection pooling, bulk insert/UPSERT và cách kiểm thử SQL

SQL injection: lỗi nghiêm trọng nhất và cách chặn tận gốc

Injection xảy ra khi dữ liệu người dùng được nối vào chuỗi SQL và trở thành một phần của lệnh. Ứng dụng muốn: SELECT ... WHERE email = '$input'. Kẻ tấn công nhập: x' OR '1'='1.

-- Chuỗi SQL cuối cùng mà ứng dụng "ngây thơ" đã ghép ra khi input là:  x' OR '1'='1
SELECT count(*) AS so_dong_lo_ra
FROM customers
WHERE email = 'x' OR '1'='1';
Kết quả
 so_dong_lo_ra
---------------
            60
(1 row)
-- Tham số hoá: giá trị đi KÈM nhưng KHÔNG BAO GIỜ được phân tích như SQL
PREPARE tim_khach (text) AS SELECT count(*) AS so_dong FROM customers WHERE email = $1;
EXECUTE tim_khach('x'' OR ''1''=''1');          -- cả chuỗi tấn công chỉ là một giá trị email (không khớp ai)
EXECUTE tim_khach('user7@example.com');
Kết quả
PREPARE
 so_dong
---------
       0
(1 row)

 so_dong
---------
       1
(1 row)

Cách dùng đúng ở các ngôn ngữ — luôn tách câu lệnh và dữ liệu:

# Python (psycopg): dấu %s KHÔNG phải định dạng chuỗi, driver gửi tham số riêng
cur.execute("SELECT id, full_name FROM customers WHERE email = %s", (email,))

# Node.js (node-postgres): $1, $2 ... và mảng giá trị
await client.query("SELECT id, full_name FROM customers WHERE email = $1", [email])

# SAI — ở mọi ngôn ngữ: nối / nội suy chuỗi
cur.execute(f"SELECT ... WHERE email = '{email}'")           # ❌ injection
await client.query(`SELECT ... WHERE email = '${email}'`)   # ❌ injection
❌ Lỗi thường gặp — Tin rằng 'escape thủ công' hay 'lọc từ khoá' là đủ

Danh sách đen (DROP, --, dấu nháy) luôn bị vượt qua (mã hoá, biến thể Unicode, chuỗi con...). Chỉ có tham số hoá mới loại bỏ lỗi từ gốc vì dữ liệu không bao giờ đi qua bộ phân tích cú pháp SQL. ORM thường tham số hoá sẵn — nhưng các hàm "raw query", .order_by(user_input), .where("name = '" + x + "'") vẫn nguy hiểm.

❌ Lỗi thường gặp — Tham số hoá được giá trị nhưng không tham số hoá được định danh

Tên bảng, tên cột, hướng sắp xếp (ORDER BY $1) không thể là tham số. Với "sắp xếp theo cột người dùng chọn": dùng danh sách trắng ánh xạ giá trị đầu vào sang định danh cố định trong code. Nếu buộc phải sinh động: format('%I', ten_cot) (trích dẫn định danh) và format('%L', gia_tri) (trích dẫn literal) trong PL/pgSQL.

SELECT format('SELECT %I FROM %I WHERE id = %L', 'full_name', 'customers', '7') AS cau_lenh_an_toan,
       format('%I', 'ten cot; DROP TABLE x') AS dinh_danh_bi_trich_dan;
Kết quả
                cau_lenh_an_toan                | dinh_danh_bi_trich_dan
------------------------------------------------+-------------------------
 SELECT full_name FROM customers WHERE id = '7' | "ten cot; DROP TABLE x"
(1 row)
🧠 Cách chuyên gia — Phòng thủ nhiều lớp
  • Tham số hoá mọi giá trị (lớp bắt buộc).
  • Quyền tối thiểu: tài khoản ứng dụng không phải owner/superuser, không có DROP, CREATE; chỉ SELECT/INSERT/UPDATE/DELETE trên bảng cần thiết (phần dưới).
  • Kiểm tra đầu vào (kiểu, độ dài, định dạng) ở tầng ứng dụng — chống lỗi logic, không thay cho tham số hoá.
  • Không trả lỗi SQL thô cho người dùng cuối (lộ cấu trúc bảng); ghi log nội bộ.

N+1 query: kẻ giết hiệu năng của ORM

Mẫu hình: lấy danh sách 100 đơn (1 truy vấn), rồi với mỗi đơn truy vấn chi tiết hàng (100 truy vấn) → tổng 101 round-trip. Mỗi truy vấn nhanh (1ms) nhưng 101 lần đi-về qua mạng làm tổng thời gian và tải DB tăng vọt, và tệ dần theo dữ liệu.

# ❌ N+1 (giả mã — ORM làm điều này ngầm khi bạn truy cập quan hệ trong vòng lặp "lazy loading")
orders = db.query("SELECT * FROM orders WHERE customer_id = %s", (cid,))       # 1 truy vấn
for o in orders:
    o.items = db.query("SELECT * FROM order_items WHERE order_id = %s", (o.id,))   # + N truy vấn

# ✅ Cách 1: một JOIN
SELECT o.id, oi.product_id, oi.qty FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.customer_id = $1;

# ✅ Cách 2: nạp theo lô (eager loading / "IN batch") — đúng 2 truy vấn bất kể N
SELECT * FROM orders WHERE customer_id = $1;
SELECT * FROM order_items WHERE order_id = ANY($1::int[]);     -- truyền mảng id từ truy vấn trước

# ✅ Cách 3: để DB trả JSON lồng nhau — 1 truy vấn, 1 kết quả cho API
-- (xem ví dụ jsonb_agg ở Module 13)
-- Cách 2 ở dạng SQL thật: lấy chi tiết của nhiều đơn bằng một tham số mảng
SELECT order_id, count(*) AS so_dong, sum(qty) AS tong_sl
FROM order_items
WHERE order_id = ANY (ARRAY[1, 2, 3, 4, 5])
GROUP BY order_id ORDER BY order_id;
Kết quả
 order_id | so_dong | tong_sl
----------+---------+---------
        1 |       2 |       4
        2 |       3 |       6
        3 |       4 |       8
        4 |       1 |       3
        5 |       2 |       3
(5 rows)
🧠 Cách chuyên gia — Cách phát hiện N+1
  • Bật log truy vấn ở môi trường dev và đếm số truy vấn mỗi request; test tự động khẳng định "không quá K truy vấn" cho endpoint quan trọng.
  • pg_stat_statements: một truy vấn có calls cực lớn và mean_exec_time rất nhỏ là dấu hiệu kinh điển.
  • Công cụ APM/trace hiển thị chuỗi truy vấn giống hệt nhau lặp lại.
  • Trong ORM: dùng eager loading (select_related/prefetch_related, include, joinedload/selectinload) có chủ đích.
❌ Lỗi thường gặp — Sửa N+1 bằng JOIN rồi bị nhân dòng (fan-out) hoặc kéo quá nhiều dữ liệu

JOIN một-nhiều làm lặp các cột của bảng cha; JOIN hai quan hệ một-nhiều cùng lúc nhân chéo nhau (module 5). Với danh sách lớn dùng nạp theo lô (2 truy vấn) thay vì JOIN khổng lồ, và chỉ chọn cột cần thiết.

Hàm PL/pgSQL và trigger

Logic nằm trong database (hàm, trigger) có ưu điểm: chạy ngay cạnh dữ liệu (ít round-trip), áp dụng cho mọi đường ghi (kể cả script, công cụ admin). Nhược điểm: khó kiểm thử/triển khai/quan sát hơn code ứng dụng, dễ thành "logic ẩn". Nguyên tắc: dùng cho những quy tắc toàn vẹn dữ liệu và việc kỹ thuật đơn giản (cập nhật updated_at, audit), không nhét logic nghiệp vụ phức tạp.

-- Hàm SQL thuần (ưu tiên khi đủ dùng): tính tiền hàng của một đơn
CREATE FUNCTION tien_hang(p_order_id int) RETURNS numeric
LANGUAGE sql STABLE AS $$
  SELECT COALESCE(round(sum(qty * unit_price * (100 - discount_pct) / 100), 2), 0) FROM order_items WHERE order_id = p_order_id
$$;

SELECT id, status, tien_hang(id) AS tien FROM orders WHERE id <= 3 ORDER BY id;
Kết quả
CREATE FUNCTION
 id |  status   |    tien
----+-----------+-------------
  1 | delivered |  2650000.00
  2 | delivered | 69630000.00
  3 | delivered | 37866000.00
(3 rows)
-- Trigger giữ updated_at luôn đúng — mọi UPDATE từ bất kỳ nguồn nào đều qua đây
ALTER TABLE customers ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END $$;

CREATE TRIGGER trg_customers_updated BEFORE UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

UPDATE customers SET city = 'Huế' WHERE id = 1 RETURNING id, city, updated_at > now() - interval '1 minute' AS vua_cap_nhat;
Kết quả
ALTER TABLE
CREATE FUNCTION
CREATE TRIGGER
 id | city | vua_cap_nhat
----+------+--------------
  1 | Huế  | t
(1 row)

UPDATE 1
-- Trigger audit: ghi lại ai đổi giá sản phẩm, từ bao nhiêu sang bao nhiêu
CREATE TABLE gia_log (product_id int, gia_cu numeric, gia_moi numeric, luc timestamptz DEFAULT now(), boi text DEFAULT current_user);

CREATE FUNCTION log_gia() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.price IS DISTINCT FROM OLD.price THEN
    INSERT INTO gia_log (product_id, gia_cu, gia_moi) VALUES (OLD.id, OLD.price, NEW.price);
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER trg_log_gia AFTER UPDATE ON products FOR EACH ROW EXECUTE FUNCTION log_gia();

UPDATE products SET price = price * 1.05 WHERE id IN (1, 2);
UPDATE products SET name = name WHERE id = 3;                 -- không đổi giá => không ghi log
SELECT product_id, gia_cu, gia_moi FROM gia_log ORDER BY product_id;
Kết quả
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
UPDATE 2
UPDATE 1
 product_id |   gia_cu    |   gia_moi
------------+-------------+-------------
          1 | 12990000.00 | 13639500.00
          2 |  6490000.00 |  6814500.00
(2 rows)
❌ Lỗi thường gặp — Trigger gây hiệu ứng phụ khó thấy
  • Trigger chạy cho từng dòng — UPDATE 1 triệu dòng kích hoạt 1 triệu lần hàm: chậm. Cân nhắc trigger mức câu lệnh (FOR EACH STATEMENT với bảng chuyển tiếp) khi xử lý hàng loạt.
  • Chuỗi trigger gọi trigger khác → vòng lặp, khó gỡ lỗi. Tài liệu hoá mọi trigger của bảng; hạn chế nhiều trigger trên cùng bảng.
  • Dev mới không biết có trigger → "tại sao cột này tự đổi?". Giữ ít, đơn giản, đặt tên rõ (trg_bang_muc_dich).

Quyền tối thiểu, timeout, pool và bulk

Role và GRANT: nguyên tắc quyền tối thiểu

CREATE ROLE bao_cao_doc NOLOGIN;
GRANT SELECT ON customers, orders, order_items, products TO bao_cao_doc;     -- chỉ đọc, chỉ vài bảng

SET LOCAL ROLE bao_cao_doc;
SELECT count(*) AS doc_duoc FROM orders;
DELETE FROM orders WHERE id = 1;
Lỗi PostgreSQL trả về
CREATE ROLE
GRANT
SET
 doc_duoc
----------
      400
(1 row)

ERROR:  permission denied for table orders
CREATE ROLE bao_cao_doc NOLOGIN;
GRANT SELECT ON customers TO bao_cao_doc;
SET LOCAL ROLE bao_cao_doc;
SELECT count(*) FROM payments;
Lỗi PostgreSQL trả về
CREATE ROLE
GRANT
SET
ERROR:  permission denied for table payments
  • Tách tài khoản: owner/migration (chạy DDL), app (DML trên bảng cần), readonly/BI (SELECT), không bao giờ dùng superuser cho ứng dụng.
  • Che cột nhạy cảm bằng quyền cột (GRANT SELECT (id, full_name) ON customers) hoặc VIEW; cần chặn theo từng dòng (multi-tenant) → Row-Level Security (CREATE POLICY).

Timeout — lưới an toàn cho mọi truy vấn

SET LOCAL statement_timeout = '200ms';
SELECT pg_sleep(1);
Lỗi PostgreSQL trả về
SET
ERROR:  canceling statement due to statement timeout
💡 Các timeout nên đặt

statement_timeout (truy vấn chạy quá lâu), lock_timeout (chờ khoá quá lâu), idle_in_transaction_session_timeout (quên commit). Đặt mặc định theo role ứng dụng (ALTER ROLE app SET statement_timeout = '30s') và nới riêng cho job báo cáo.

Connection pooling

Mỗi kết nối PostgreSQL là một tiến trình tốn RAM (vài MB); 1.000 kết nối đồng thời làm server chậm hơn dùng 50 kết nối. Dùng pool phía ứng dụng (kích thước nhỏ, ví dụ 10–20/instance) và/hoặc PgBouncer (transaction pooling) khi có nhiều instance. Lưu ý với transaction pooling: không dùng SET kéo dài phiên, advisory lock theo phiên, LISTEN, prepared statement phụ thuộc phiên (trừ khi PgBouncer hỗ trợ).

Ghi hàng loạt hiệu quả

-- ❌ 10.000 câu INSERT riêng: 10.000 round-trip.  ✅ Một câu với mảng tham số (unnest):
CREATE TEMP TABLE diem_danh (customer_id int, ngay date, ghi_chu text);
INSERT INTO diem_danh (customer_id, ngay, ghi_chu)
SELECT * FROM unnest(ARRAY[1, 2, 3], ARRAY[date '2024-07-01', '2024-07-01', '2024-07-02'], ARRAY['có mặt', 'trễ', NULL]);
SELECT * FROM diem_danh;
Kết quả
CREATE TABLE
INSERT 0 3
 customer_id |    ngay    | ghi_chu
-------------+------------+---------
           1 | 2024-07-01 | có mặt
           2 | 2024-07-01 | trễ
           3 | 2024-07-02 | NULL
(3 rows)
🧠 Cách chuyên gia — Chọn cách ghi theo quy mô
Quy môCách
1–vài chục dòngINSERT nhiều dòng VALUES (...), (...)
Hàng nghìn, từ ứng dụngINSERT ... SELECT FROM unnest($1::t[], $2::t[], ...) hoặc batch trong 1 transaction
Hàng triệu / file lớnCOPY vào staging rồi INSERT ... SELECT

Giới hạn ~65.535 tham số mỗi câu lệnh (giao thức PostgreSQL): với batch lớn dùng mảng/unnest hoặc COPY.

🧠 Cách chuyên gia — Kiểm thử SQL
  • Chạy test trong transaction rồi ROLLBACK (mỗi test một transaction) — nhanh, sạch, không cần dọn dữ liệu.
  • Dùng database thật (container PostgreSQL) cho test tích hợp thay vì SQLite/mock — hành vi khác nhau nhiều (kiểu, múi giờ, khoá, JSONB).
  • pgTAP cho test ngay trong database; kiểm tra schema (ràng buộc có tồn tại, index đủ chưa).
  • Giữ truy vấn đối soát chất lượng dữ liệu (module 12) chạy định kỳ như monitoring.

🏋️ Bài tập thực hành

Sửa đoạn code dễ bị injectionDễ

Hãy viết lại (bằng lời hoặc pseudo-code) đoạn sql = "SELECT * FROM orders WHERE status = '" + status + "' ORDER BY " + sortCol sao cho an toàn. Chú ý có hai nguồn đầu vào.

Xem lời giải & giải thích

Giá trị status: tham số hoá (WHERE status = $1, truyền [status]). Định danh sortCol không tham số hoá được: dùng danh sách trắng {'ordered_at': 'ordered_at', 'id': 'id'} ánh xạ đầu vào người dùng sang tên cột cố định (từ chối nếu không có trong danh sách); hướng sắp xếp cũng chỉ chấp nhận ASC/DESC. Và chỉ chọn cột cần thiết thay vì SELECT *.

Trigger kiểm tra tồn khoTrung bình

Viết trigger BEFORE INSERT trên order_items từ chối (RAISE EXCEPTION) nếu qty lớn hơn tồn kho hiện có của sản phẩm. Thử chèn thành công và thất bại.

Xem lời giải & giải thích
CREATE FUNCTION kiem_tra_ton() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.qty > (SELECT qty_on_hand FROM inventory WHERE product_id = NEW.product_id) THEN
    RAISE EXCEPTION 'Không đủ hàng cho sản phẩm %', NEW.product_id USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER trg_ton BEFORE INSERT ON order_items FOR EACH ROW EXECUTE FUNCTION kiem_tra_ton();

INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (400, 20, 1, 450000) ON CONFLICT DO NOTHING;
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (400, 19, 9999, 7490000);
Lỗi PostgreSQL trả về
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
ERROR:  Không đủ hàng cho sản phẩm 19
CONTEXT:  PL/pgSQL function kiem_tra_ton() line 4 at RAISE

Lưu ý: kiểm tra rồi ghi trong trigger vẫn có race condition khi nhiều phiên đồng thời (module 10) — muốn tuyệt đối đúng phải trừ kho nguyên tử (UPDATE ... WHERE qty_on_hand >= n) và có CHECK (qty_on_hand >= 0).

Phát hiện N+1Trung bình

Một API 'danh sách 50 khách kèm số đơn của mỗi khách' đang chạy 51 truy vấn. Viết một truy vấn duy nhất trả đúng dữ liệu đó cho 50 khách đầu.

Xem lời giải & giải thích
SELECT c.id, c.full_name, count(o.id) AS so_don
FROM (SELECT id, full_name FROM customers ORDER BY id LIMIT 50) c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.full_name
ORDER BY c.id
LIMIT 5;
Kết quả
 id |    full_name    | so_don
----+-----------------+--------
  1 | Trần Quốc Dũng  |      7
  2 | Lê Ngọc Lan     |      7
  3 | Phạm Đức Phúc   |      8
  4 | Hoàng Minh Châu |      7
  5 | Vũ Gia Huy      |      8
(5 rows)

Giới hạn 50 khách trước khi JOIN (subquery) để không tổng hợp cả bảng; LEFT JOIN + count(o.id) để khách chưa có đơn vẫn hiện số 0.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Mọi giá trị từ người dùng đều đi qua tham số; định danh động dùng danh sách trắng
  • Phát hiện và sửa được N+1 bằng JOIN / nạp theo lô / JSON lồng
  • Biết viết hàm SQL/PLpgSQL, trigger updated_at/audit và các cạm bẫy của trigger
  • Ứng dụng dùng tài khoản quyền tối thiểu, có timeout, pool kết nối và ghi hàng loạt đúng cách