Hầu hết các lần, viết một chương trình bạn sẽ phải trộn các câu lệnh SQL với các giá trị được cung cấp bởi phần còn lại của chương trình:
SELECT some, fields FROM some_table WHERE id = ...
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]3 bằng những gì? Có lẽ bạn sẽ có một giá trị python mà bạn đang tìm kiếm.
cur.execute["""
INSERT INTO some_table [id, created_at, last_name]
VALUES [%s, %s, %s];
""",
[10, datetime.date[2020, 11, 18], "O'Reilly"]]
4 Đối số#
Chuyển các tham số cho câu lệnh SQL xảy ra trong các chức năng như
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]5 bằng cách sử dụng trình giữ chỗ
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]6 trong câu lệnh SQL và chuyển một chuỗi các giá trị làm đối số thứ hai của hàm. Ví dụ: cuộc gọi chức năng Python:
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]
gần tương đương với lệnh SQL:
INSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];
Lưu ý rằng các tham số sẽ không thực sự được hợp nhất với truy vấn: truy vấn và các tham số được gửi riêng đến máy chủ: xem ràng buộc phía máy chủ để biết chi tiết.Server-side binding for details.
Các đối số được đặt tên cũng được hỗ trợ bằng cách sử dụng trình giữ chỗ
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]7 trong truy vấn và chỉ định các giá trị thành ánh xạ. Sử dụng các đối số có tên cho phép chỉ định các giá trị theo bất kỳ thứ tự nào và lặp lại cùng một giá trị ở một số nơi trong truy vấn:
cur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]
Sử dụng các ký tự
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]8,
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]9,
INSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];0 trong tên đối số không được hỗ trợ.
Khi các tham số được sử dụng, để bao gồm một
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]8 theo nghĩa đen trong truy vấn, bạn có thể sử dụng chuỗi
INSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];2:
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct
Mặc dù cơ chế giống như thao tác chuỗi python thông thường, có một vài sự khác biệt tinh tế mà bạn nên quan tâm khi chuyển các tham số cho một truy vấn.
Toán tử chuỗi Python
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]
8 không được sử dụng: Phương thứccur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]
4 chấp nhận một tuple hoặc từ điển của các giá trị làm tham số thứ hai. Không bao giờ sử dụngcur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]
8 hoặcINSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];
6 để hợp nhất các giá trị thành các truy vấn:Never usecur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]
8 orINSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];
6 to merge values into queries:cur.execute["INSERT INTO numbers VALUES [%s, %s]" % [10, 20]] # WRONG cur.execute["INSERT INTO numbers VALUES [%s, %s]", [10, 20]] # correct
Đối với các biến vị trí ràng buộc, đối số thứ hai phải luôn luôn là một chuỗi, ngay cả khi nó chứa một biến duy nhất [hãy nhớ rằng Python yêu cầu dấu phẩy để tạo một bộ phần tử duy nhất]:
cur.execute["INSERT INTO foo VALUES [%s]", "bar"] # WRONG cur.execute["INSERT INTO foo VALUES [%s]", ["bar"]] # WRONG cur.execute["INSERT INTO foo VALUES [%s]", ["bar",]] # correct cur.execute["INSERT INTO foo VALUES [%s]", ["bar"]] # correct
Người giữ chỗ không được trích dẫn:
cur.execute["INSERT INTO numbers VALUES ['%s']", ["Hello",]] # WRONG cur.execute["INSERT INTO numbers VALUES [%s]", ["Hello",]] # correct
Các chất giữ chỗ của các biến phải luôn là
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]
6, ngay cả khi một trình giữ chỗ khác [chẳng hạn nhưINSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];
8 cho số nguyên hoặcINSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];
9 cho phao] có thể trông phù hợp hơn với loại. Bạn có thể tìm thấy những người giữ chỗ khác được sử dụng trong các truy vấn PSYCOPG [cur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]
0 vàcur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]
1] nhưng chúng không liên quan đến loại đối số: xem các tham số và kết quả nhị phân nếu bạn muốn đọc thêm:Binary parameters and results if you want to read more:cur.execute["INSERT INTO numbers VALUES [%d]", [10,]] # WRONG cur.execute["INSERT INTO numbers VALUES [%s]", [10,]] # correct
Chỉ các giá trị truy vấn nên được ràng buộc thông qua phương thức này: nó không nên được sử dụng để hợp nhất các tên trường hoặc trường vào truy vấn. Nếu bạn cần tạo các truy vấn SQL một cách linh hoạt [ví dụ như chọn tên bảng trong thời gian chạy], bạn có thể sử dụng các chức năng được cung cấp trong mô -đun
cur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]
2:cur.execute["INSERT INTO %s VALUES [%s]", ['numbers', 10]] # WRONG cur.execute[ # correct SQL["INSERT INTO {} VALUES [%s]"].format[Identifier['numbers']], [10,]]
Nguy hiểm: SQL Injection#
Biểu diễn SQL của nhiều loại dữ liệu thường khác với biểu diễn chuỗi python của chúng. Ví dụ điển hình là với các trích dẫn đơn trong chuỗi: Trong các trích dẫn đơn SQL được sử dụng làm trình phân cách theo nghĩa đen, do đó, các trích dẫn xuất hiện bên trong chuỗi phải được thoát ra, trong khi trong các trích dẫn đơn Python có thể được để lại .
Do sự khác biệt, đôi khi tinh tế, giữa các biểu diễn các loại dữ liệu, cách tiếp cận ngây thơ đối với thành phần chuỗi truy vấn, chẳng hạn như sử dụng kết nối chuỗi Python, là một công thức cho các vấn đề khủng khiếp:
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]0
Nếu các biến chứa dữ liệu để gửi đến cơ sở dữ liệu đến từ một nguồn không đáng tin cơ sở dữ liệu. Hình thức tấn công này được gọi là tiêm SQL và được biết đến là một trong những hình thức tấn công phổ biến nhất trên các hệ thống cơ sở dữ liệu. Trước khi tiếp tục, xin vui lòng in trang này dưới dạng bản ghi nhớ và treo nó lên bàn của bạn.
PSYCOPG có thể tự động chuyển đổi các đối tượng Python thành các giá trị SQL: Sử dụng tính năng này, mã của bạn sẽ mạnh mẽ và đáng tin cậy hơn. Chúng ta phải nhấn mạnh điểm này:automatically convert Python objects to SQL values: using this feature your code will be more robust and reliable. We must stress this point:
Cảnh báo
Don lồng hợp nhất các giá trị hợp nhất cho một truy vấn: tin tặc từ nước ngoài sẽ đột nhập vào máy tính của bạn và đánh cắp không chỉ các đĩa của bạn, mà cả đĩa CD của bạn, chỉ khiến bạn có ba hồ sơ đáng xấu hổ nhất mà bạn từng mua. Trên băng cassette.
Nếu bạn sử dụng nhà điều hành
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]
8 để hợp nhất các giá trị cho một truy vấn, Con nghệ sĩ sẽ quyến rũ mèo của bạn, người sẽ chạy trốn lấy thẻ tín dụng của bạn và kính râm của bạn với họ.Nếu bạn sử dụng
INSERT INTO some_table [id, created_at, last_name] VALUES [10, '2020-11-18', 'O''Reilly'];
6 để hợp nhất giá trị văn bản thành một chuỗi, kẻ xấu ở Balaclava sẽ tìm đường đến tủ lạnh của bạn, uống tất cả bia của bạn và để chỗ ngồi vệ sinh của bạn lên và giấy vệ sinh của bạn theo hướng sai.Bạn không muốn hợp nhất các giá trị theo cách thủ công với một truy vấn: sử dụng các phương thức được cung cấp thay thế.use the provided methods instead.
Cách chính xác để truyền các biến trong lệnh SQL là sử dụng đối số thứ hai của phương thức
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]5:
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]1
Ghi chú
Trình kiểm tra mã tĩnh Python chưa hoàn toàn ở đó, nhưng trong tương lai, có thể kiểm tra mã của bạn để sử dụng không đúng các biểu thức chuỗi trong các truy vấn. Xem kiểm tra các chuỗi theo nghĩa đen trong các truy vấn để biết chi tiết.Checking literal strings in queries for details.
Thông số nhị phân và kết quả#
PostgreSQL có hai cách khác nhau để truyền dữ liệu giữa máy khách và máy chủ:
cur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]6, luôn luôn có sẵn và
cur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]7, có sẵn hầu hết thời gian nhưng không phải lúc nào cũng vậy. Thông thường định dạng nhị phân hiệu quả hơn để sử dụng.
PSYCOPG có thể hỗ trợ cả hai định dạng cho từng loại dữ liệu. Bất cứ khi nào một giá trị được chuyển cho một truy vấn bằng cách sử dụng trình giữ chỗ
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]6 bình thường, định dạng tốt nhất có sẵn được chọn [thường, nhưng không phải lúc nào cũng, định dạng nhị phân được chọn làm lựa chọn tốt nhất].
Nếu bạn có lý do để chọn rõ ràng định dạng nhị phân hoặc định dạng văn bản cho một giá trị bạn có thể sử dụng tương ứng là trình giữ chỗ
cur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]0 hoặc trình giữ chỗ
cur.execute[""" INSERT INTO some_table [id, created_at, updated_at, last_name] VALUES [%[id]s, %[created]s, %[created]s, %[name]s]; """, {'id': 10, 'name': "O'Reilly", 'created': datetime.date[2020, 11, 18]}]1 thay vì bình thường
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]6.
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]4 sẽ thất bại nếu
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct3 cho đúng kiểu dữ liệu và định dạng không có sẵn.
Hai định dạng tương tự, văn bản hoặc nhị phân, được PostgreSQL sử dụng để trả lại dữ liệu từ truy vấn cho máy khách. Không giống như với các tham số, trong đó bạn có thể chọn định dạng giá trị theo giá trị, tất cả các cột được trả về bởi một truy vấn sẽ có cùng định dạng. Mỗi loại được trả về bởi truy vấn phải có cấu hình
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct4, nếu không dữ liệu sẽ được trả về dưới dạng
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct5 [cho kết quả văn bản] hoặc bộ đệm [cho kết quả nhị phân].
Ghi chú
Bảng PG_TYPE xác định định dạng nào được hỗ trợ cho từng loại dữ liệu PostgreSQL. Đầu vào/đầu ra văn bản được quản lý bởi các hàm được khai báo trong các trường
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct6 và
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct7 [luôn luôn có mặt], đầu vào/đầu ra nhị phân được quản lý bởi
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct8 và
cur.execute["SELECT [%s % 2] = 0 AS even", [10,]] # WRONG cur.execute["SELECT [%s %% 2] = 0 AS even", [10,]] # correct9 [là tùy chọn].
Bởi vì không phải mọi loại PostgreSQL đều hỗ trợ đầu ra nhị phân, theo mặc định, dữ liệu sẽ được trả về ở định dạng văn bản. Để trả về dữ liệu ở định dạng nhị phân, bạn có thể tạo con trỏ bằng cách sử dụng
cur.execute["INSERT INTO numbers VALUES [%s, %s]" % [10, 20]] # WRONG cur.execute["INSERT INTO numbers VALUES [%s, %s]", [10, 20]] # correct0
cur.execute["INSERT INTO numbers VALUES [%s, %s]" % [10, 20]] # WRONG cur.execute["INSERT INTO numbers VALUES [%s, %s]", [10, 20]] # correct1 hoặc thực hiện truy vấn bằng cách sử dụng
cur.execute["INSERT INTO numbers VALUES [%s, %s]" % [10, 20]] # WRONG cur.execute["INSERT INTO numbers VALUES [%s, %s]", [10, 20]] # correct2
cur.execute["INSERT INTO numbers VALUES [%s, %s]" % [10, 20]] # WRONG cur.execute["INSERT INTO numbers VALUES [%s, %s]", [10, 20]] # correct1. Một trường hợp yêu cầu kết quả nhị phân là một người chiến thắng rõ ràng là khi bạn có dữ liệu nhị phân lớn trong cơ sở dữ liệu, chẳng hạn như hình ảnh:
cur.execute[""" INSERT INTO some_table [id, created_at, last_name] VALUES [%s, %s, %s]; """, [10, datetime.date[2020, 11, 18], "O'Reilly"]]2