Prepared statement trong SQLite: prepare, bind, step, finalize

Prepared statement thật ra là gì

Khi bạn đưa cho SQLite một chuỗi SQL, nó phải làm khá nhiều việc trước khi bất kỳ hàng nào được xử lý: tách token, phân tích cú pháp, kiểm tra các bảng và cột có tồn tại không, lập kế hoạch thực thi, rồi biên dịch kế hoạch đó thành bytecode cho máy ảo của SQLite. Chỉ sau đó truy vấn mới thật sự chạy.

Prepared statement là thứ bạn có được khi dừng ở bước "đã biên dịch thành bytecode" và giữ lại kết quả đó. Chương trình đã biên dịch có các ô trống, tức placeholder, nơi giá trị thật sẽ được điền vào sau. Bạn có thể chạy cùng một chương trình đã biên dịch nhiều lần với các giá trị khác nhau, và chạy nó an toàn với cả những giá trị đến từ đầu vào không đáng tin.

Hãy hình dung sự khác biệt giữa việc đưa cho ai đó một công thức để đọc to mỗi lần nấu, so với việc dạy họ công thức một lần rồi đến hôm nấu chỉ cần nói tên nguyên liệu.

Vòng đời: prepare, bind, step, finalize

Mọi driver SQLite trong mọi ngôn ngữ đều bọc cùng bốn lời gọi C API. Biết tên chúng vẫn có ích dù bạn sẽ không bao giờ viết C, vì thông báo lỗi và tài liệu đều dùng bộ từ vựng này:

1. sqlite3_prepare_v2: biên dịch một chuỗi SQL thành statement handle.
2. sqlite3_bind_*: điền giá trị vào các placeholder (mỗi kiểu một hàm).
3. sqlite3_step: chạy chương trình. Với SELECT, gọi lặp lại để duyệt qua các hàng. Với INSERT/UPDATE/DELETE, một lần gọi là xong việc.
4. sqlite3_finalize: giải phóng chương trình đã biên dịch khi bạn dùng xong.

Giữa các lần chạy, sqlite3_reset tua lại một statement đã chạy xong để bạn bind lại và thực thi lại mà không cần prepare lần nữa.

Placeholder trong SQL

Bên trong chuỗi SQL, bạn đánh dấu mỗi chỗ cần giá trị bằng một placeholder thay vì ghép giá trị vào. SQLite hỗ trợ vài dạng:

-- Anonymous, positional:
INSERT INTO users (name, email) VALUES (?, ?);

-- Numbered:
INSERT INTO users (name, email) VALUES (?1, ?2);

-- Named:
INSERT INTO users (name, email) VALUES (:name, :email);
INSERT INTO users (name, email) VALUES (@name, @email);
INSERT INTO users (name, email) VALUES ($name, $email);

? là dạng phổ biến nhất trong code ở tầng driver. Placeholder có tên (:name) dễ đọc hơn khi có nhiều tham số hoặc khi cùng một giá trị xuất hiện nhiều lần. Hãy chọn một kiểu cho mỗi dự án và dùng nhất quán.

Điều không nên làm là dựng truy vấn bằng cách nối chuỗi:

-- DO NOT DO THIS:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

Đó là con đường dẫn tới SQL injection, và nó cũng làm mất lợi ích tái sử dụng bytecode mà bạn sắp đọc tới.

Ví dụ minh họa bằng SQL

Để thấy cơ chế mà không cần ngôn ngữ chủ, đây là phiên bản tương đương của prepare/bind/step chỉ dùng các tính năng SQL mà SQLite cung cấp. Tạo một bảng và chèn một hàng với placeholder kiểu tham số được điền bằng giá trị literal:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name, email) VALUES ('Boris', 'boris@example.com');

SELECT id, name, email FROM users ORDER BY id;

Trong một ứng dụng thật, bạn sẽ không viết giá trị trực tiếp: bạn prepare câu INSERT một lần với placeholder ?, ?, rồi bind cặp tên và email cho từng người dùng và step. Bytecode đã biên dịch giống hệt nhau ở mọi lần gọi; chỉ giá trị được bind thay đổi.

Tái sử dụng statement (lợi ích về hiệu năng)

Đây là mẫu mà driver cho phép bạn viết. Đây là mã giả, mỗi ngôn ngữ viết hơi khác một chút, nhưng hình dạng thì giống nhau ở mọi nơi:

-- prepared once:
INSERT INTO users (name, email) VALUES (?, ?);

-- then, in a loop:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

Prepare phân tích và biên dịch SQL một lần. Mỗi lần lặp chỉ chạy bytecode và sao chép giá trị vào các ô. Với insert hàng loạt (ví dụ nhập 100.000 hàng), cách này nhanh hơn hẳn so với thực thi 100.000 câu lệnh được phân tích riêng lẻ, thường nhanh hơn cả một bậc độ lớn, nhất là khi bọc trong một giao dịch duy nhất.

Một lỗi hay gặp: gọi prepare bên trong vòng lặp. Như vậy là vứt bỏ toàn bộ lợi ích. Hãy prepare bên ngoài vòng lặp, còn bind và step bên trong.

Vì sao đây là cách an toàn

Tham số được bind không phải là chuỗi được thay vào SQL. Chúng là giá trị được đưa cho chương trình bytecode qua các ô có kiểu: ô số nguyên, ô văn bản, ô blob. SQLite không bao giờ phân tích lại chúng như SQL, nên không giá trị nào có thể thay đổi cấu trúc của truy vấn.

So sánh:

-- Vulnerable. If user_input is:  '); DROP TABLE users;--
-- the query becomes destructive.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- Safe. user_input is bound as a TEXT value and only ever
-- compared as a string, no matter what it contains.
SELECT * FROM users WHERE name = ?;

Dạng thứ hai vẫn an toàn ngay cả khi user_input là '); DROP TABLE users;--. SQLite sẽ ngoan ngoãn tìm một người dùng có tên đúng là chuỗi (kỳ quặc) đó, không tìm thấy ai và trả về 0 hàng. Không có gì trong cấu trúc truy vấn có thể bị thay đổi dựa trên giá trị.

Chúng ta sẽ đi sâu hơn về injection ở một trang sau, nhưng điều cần nhớ là: prepared statement không chỉ là một cách phòng chống SQL injection, mà là cách phòng chống.

Câu lệnh trả về hàng

Với SELECT, step trả về từng hàng một. Driver thường lặp cho đến khi nó báo "xong":

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

INSERT INTO products (name, price) VALUES
    ('Notebook', 4.50),
    ('Pen',      1.20),
    ('Eraser',   0.80);

SELECT name, price FROM products WHERE price < 2.00 ORDER BY price;

Trong code ứng dụng, driver sẽ prepare câu SELECT đó với ? thay cho 2.00, bind giá trị ngưỡng, rồi gọi step trong vòng lặp, đọc một hàng mỗi lần gọi. Sau hàng cuối cùng, step báo hoàn tất, và driver hoặc reset statement (để chạy lại với ngưỡng mới) hoặc finalize nó.

Đừng quên finalize

Một prepared statement là một vùng cấp phát nhỏ bên trong SQLite. Để rò rỉ chúng sẽ ngốn bộ nhớ và, quan trọng hơn, giữ một khóa nội bộ trên cơ sở dữ liệu có thể chặn các tiến trình ghi khác. Mọi driver đều cho bạn cách dọn dẹp tự động (context manager trong Python, khối using trong C#, RAII trong C++), và bạn nên dùng chúng:

• sqlite3 của Python finalize khi cursor bị thu gom rác, nhưng gọi cursor.close() rõ ràng thì gọn hơn.
• better-sqlite3 (Node) finalize khi Statement bị thu gom rác; giữ prepared statement lâu dài vẫn ổn.
• Trong C thuần, bạn tự gọi sqlite3_finalize. Quên gọi là một bug thật sự.

Quy tắc chung: nếu bạn đã prepare thì phải có thứ gì đó finalize nó.

Khi nào bạn không cần tự làm

Bạn sẽ hiếm khi gọi trực tiếp sqlite3_prepare_v2. Các driver cấp cao biến connection.execute("SELECT ... WHERE id = ?", (42,)) thành prepare/bind/step/finalize cho bạn. Lý do nên hiểu vòng đời này là:

• Bạn sẽ nhận ra chuyện gì đang xảy ra khi gặp lỗi "statement is busy" hay "cannot operate on a finalized statement".
• Bạn sẽ biết cần cache các prepared statement tồn tại lâu khi insert trong một vòng lặp dày đặc.
• Bạn sẽ viết truy vấn tham số hóa theo bản năng, ngay cả khi nối chuỗi trông có vẻ hấp dẫn.

ORM và query builder còn đi xa hơn. Chúng dựng SQL, quản lý prepared statement và trả về kết quả có kiểu cho bạn. Bên dưới, tất cả vẫn là bốn lời gọi đó.

Tiếp theo: bind tham số

Chúng ta đã nói về placeholder một cách trừu tượng. Tiếp theo, ta sẽ xem kỹ phía bind: tham số theo vị trí và tham số có tên, cách xử lý kiểu dữ liệu, NULL, và những cái bẫy nhỏ xuất hiện khi bạn bắt đầu truyền dữ liệu thật của ứng dụng vào truy vấn.