Một câu lệnh đã chuẩn bị sẵn là một tính năng được sử dụng để thực thi lặp đi lặp lại các câu lệnh SQL giống nhau [hoặc tương tự] với hiệu quả cao
Các báo cáo đã chuẩn bị về cơ bản hoạt động như thế này
- Chuẩn bị. Mẫu câu lệnh SQL được tạo và gửi đến cơ sở dữ liệu. Một số giá trị không được chỉ định, được gọi là tham số [được gắn nhãn "?"]. Thí dụ. CHÈN GIÁ TRỊ VÀO MyGuests[?, ?, ?]
- Cơ sở dữ liệu phân tích cú pháp, biên dịch và thực hiện tối ưu hóa truy vấn trên mẫu câu lệnh SQL và lưu trữ kết quả mà không cần thực thi nó
- Hành hình. Sau đó, ứng dụng sẽ liên kết các giá trị với tham số và cơ sở dữ liệu thực thi câu lệnh. Ứng dụng có thể thực thi câu lệnh bao nhiêu lần tùy thích với các giá trị khác nhau
So với việc thực thi trực tiếp các câu lệnh SQL, các câu lệnh đã chuẩn bị sẵn có ba ưu điểm chính
- Các câu lệnh đã chuẩn bị giảm thời gian phân tích cú pháp vì việc chuẩn bị cho truy vấn chỉ được thực hiện một lần [mặc dù câu lệnh được thực hiện nhiều lần]
- Tham số giới hạn giảm thiểu băng thông đến máy chủ vì bạn chỉ cần gửi tham số mỗi lần chứ không phải toàn bộ truy vấn
- Các câu lệnh đã chuẩn bị rất hữu ích đối với việc tiêm SQL, bởi vì các giá trị tham số, được truyền sau này bằng một giao thức khác, không cần phải thoát một cách chính xác. Nếu mẫu câu lệnh ban đầu không được lấy từ đầu vào bên ngoài, thì SQL injection không thể xảy ra
Báo cáo đã chuẩn bị trong MySQLi
Ví dụ sau sử dụng các câu lệnh đã chuẩn bị và các tham số ràng buộc trong MySQLi
Ví dụ [MySQLi với câu lệnh đã chuẩn bị]
$servername = "máy chủ cục bộ";
$username = "tên người dùng";
$password = "mật khẩu";
$dbname = "myDB";
// Tạo kết nối
$conn = new mysqli[$servername, $username, $password, $dbname];
// Kiểm tra kết nối
nếu [$conn->connect_error] {
die["Kết nối thất bại. ". $conn->connect_error];
}
// chuẩn bị và ràng buộc
$stmt = $conn->prepare["CHÈN VÀO MyGuests [tên, họ, email] GIÁ TRỊ [?, ?, ?]"];
$stmt->bind_param["sss", $firstname, $lastname, $email];
// thiết lập tham số và thực hiện
$firstname = "John";
$lastname = "Doe";
$email = "john@ví dụ. com";
$stmt->execute[];
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@ví dụ. com";
$stmt->execute[];
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example. com";
$stmt->execute[];
echo "Tạo bản ghi mới thành công";
$stmt->đóng[];
$conn->đóng[];
?>
Các dòng mã để giải thích từ ví dụ trên
"CHÈN VÀO MyGuest [tên, họ, email] GIÁ TRỊ [?, ?, ?]"
Trong SQL của chúng tôi, chúng tôi chèn một dấu chấm hỏi [?] nơi chúng tôi muốn thay thế bằng một giá trị số nguyên, chuỗi, kép hoặc blob
Sau đó, hãy xem hàm bind_param[]
$stmt->bind_param["sss", $firstname, $lastname, $email];
Hàm này liên kết các tham số với truy vấn SQL và cho cơ sở dữ liệu biết các tham số đó là gì. Đối số "sss" liệt kê các loại dữ liệu mà các tham số. Ký tự s nói với mysql rằng tham số là một chuỗi
Đối số có thể là một trong bốn loại
- tôi - số nguyên
- d - gấp đôi
- s - chuỗi
- b - BLOB
Chúng ta phải có một trong số này cho mỗi tham số
Bằng cách cho mysql biết loại dữ liệu mong đợi, chúng tôi giảm thiểu rủi ro khi tiêm SQL
Ghi chú. Nếu chúng tôi muốn chèn bất kỳ dữ liệu nào từ các nguồn bên ngoài [chẳng hạn như đầu vào của người dùng], thì điều rất quan trọng là dữ liệu đó phải được làm sạch và xác thực
Đôi khi sẽ thuận tiện hơn khi sử dụng một đối tượng PreparedStatement để gửi các câu lệnh SQL tới cơ sở dữ liệu. Loại câu lệnh đặc biệt này bắt nguồn từ lớp tổng quát hơn,
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
0, mà bạn đã biếtNếu bạn muốn thực hiện một đối tượng
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
0 nhiều lần, việc sử dụng đối tượng PreparedStatement để thay thế sẽ giảm thời gian thực hiệnĐặc điểm chính của đối tượng PreparedStatement là, không giống như đối tượng
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
0, nó được cung cấp một câu lệnh SQL khi được tạo. Ưu điểm của điều này là trong hầu hết các trường hợp, câu lệnh SQL này được gửi ngay đến DBMS, nơi nó được biên dịch. Kết quả là, đối tượng PreparedStatement không chỉ chứa một câu lệnh SQL mà còn chứa một câu lệnh SQL đã được biên dịch trước. Điều này có nghĩa là khi PreparedStatement được thực thi, DBMS chỉ có thể chạy câu lệnh SQL PreparedStatement mà không cần phải biên dịch nó trướcMặc dù bạn có thể sử dụng các đối tượng PreparedStatement cho các câu lệnh SQL không có tham số, nhưng bạn có thể sử dụng chúng thường xuyên nhất cho các câu lệnh SQL có tham số. Ưu điểm của việc sử dụng các câu lệnh SQL nhận tham số là bạn có thể sử dụng cùng một câu lệnh và cung cấp cho nó các giá trị khác nhau mỗi khi bạn thực thi nó. Ví dụ về điều này là trong các phần sau
Tuy nhiên, ưu điểm quan trọng nhất của các câu lệnh được chuẩn bị sẵn là chúng giúp ngăn chặn các cuộc tấn công SQL injection. SQL injection là một kỹ thuật khai thác độc hại các ứng dụng sử dụng dữ liệu do khách hàng cung cấp trong các câu lệnh SQL. Những kẻ tấn công lừa công cụ SQL thực thi các lệnh ngoài ý muốn bằng cách cung cấp đầu vào chuỗi được chế tạo đặc biệt, do đó có được quyền truy cập trái phép vào cơ sở dữ liệu để xem hoặc thao tác dữ liệu bị hạn chế. Tất cả các kỹ thuật SQL injection đều khai thác một lỗ hổng duy nhất trong ứng dụng. Các ký tự chuỗi ký tự được xác thực hoặc không được xác thực không chính xác được nối vào một câu lệnh SQL được tạo động và được giải thích dưới dạng mã bởi công cụ SQL. Các câu lệnh đã chuẩn bị luôn coi dữ liệu do máy khách cung cấp là nội dung của một tham số và không bao giờ là một phần của câu lệnh SQL. Xem phần trong Tài liệu tham khảo ngôn ngữ PL/SQL Cơ sở dữ liệu, một phần của tài liệu Cơ sở dữ liệu Oracle, để biết thêm thông tin
Phương pháp sau đây,
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
9, lưu số cân cà phê đã bán trong tuần hiện tại vào cột updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];0 cho mỗi loại cà phê và cập nhật tổng số cân cà phê đã bán trong cột
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];1 cho mỗi loại cà phê
public void updateCoffeeSales[HashMap salesForWeek] throws SQLException {
String updateString =
"update COFFEES set SALES = ? where COF_NAME = ?";
String updateStatement =
"update COFFEES set TOTAL = TOTAL + ? where COF_NAME = ?";
try [PreparedStatement updateSales = con.prepareStatement[updateString];
PreparedStatement updateTotal = con.prepareStatement[updateStatement]]
{
con.setAutoCommit[false];
for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
}
} catch [SQLException e] {
JDBCTutorialUtilities.printSQLException[e];
if [con != null] {
try {
System.err.print["Transaction is being rolled back"];
con.rollback[];
} catch [SQLException excep] {
JDBCTutorialUtilities.printSQLException[excep];
}
}
}
}
Sau đây tạo một đối tượng PreparedStatement nhận hai tham số đầu vào
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
Bạn phải cung cấp các giá trị thay cho chỗ dành sẵn cho dấu chấm hỏi [nếu có] trước khi bạn có thể thực thi một đối tượng PreparedStatement. Thực hiện việc này bằng cách gọi một trong các phương thức setter được định nghĩa trong lớp PreparedStatement. Các câu sau đây cung cấp hai chỗ dành cho dấu chấm hỏi trong PreparedStatement có tên
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];
Đối số đầu tiên cho mỗi phương thức setter này chỉ định trình giữ chỗ dấu chấm hỏi. Trong ví dụ này,
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];7 chỉ định trình giữ chỗ đầu tiên và
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];8 chỉ định trình giữ chỗ thứ hai
Sau khi một tham số đã được thiết lập với một giá trị, nó sẽ giữ lại giá trị đó cho đến khi nó được đặt lại thành một giá trị khác hoặc phương thức
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];9 được gọi. Sử dụng đối tượng
PreparedStatement updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6, đoạn mã sau minh họa việc sử dụng lại một câu lệnh đã chuẩn bị sau khi đặt lại giá trị của một trong các tham số của nó và giữ nguyên giá trị còn lại
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];
Sử dụng vòng lặp để đặt giá trị
Bạn thường có thể viết mã dễ dàng hơn bằng cách sử dụng vòng lặp
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];2 hoặc vòng lặp
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];3 để đặt giá trị cho tham số đầu vào
Phương thức
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
9 sử dụng vòng lặp for-each để lặp lại giá trị đặt trong các đối tượng PreparedStatement updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6 và
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];7
for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
Phương thức
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
9 nhận một đối số, // changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];9. Mỗi phần tử trong đối số
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];9 chứa tên của một loại cà phê và số pound loại cà phê đó được bán trong tuần hiện tại. Vòng lặp for-each lặp qua từng phần tử của đối số
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];9 và đặt các dấu chấm hỏi thích hợp thay thế trong
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6 và
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];7
Như với các đối tượng
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
0, để thực thi một đối tượng PreparedStatement, hãy gọi một câu lệnh thực thi. for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
6 nếu truy vấn chỉ trả về một for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
7 [chẳng hạn như câu lệnh SQL for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
8], for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
9 nếu truy vấn không trả về for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
7 [chẳng hạn như câu lệnh SQL updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
1] hoặc updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
2 nếu truy vấn có thể trả về nhiều hơn một đối tượng for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
7. Cả hai đối tượng PreparedStatement trong updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
5 đều chứa các câu lệnh SQL updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
1, vì vậy cả hai đều được thực thi bằng cách gọi for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
9updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
Không có đối số nào được cung cấp cho
for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
9 khi chúng được sử dụng để thực thi updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6 và
con.setAutoCommit[false];0;
Ghi chú. Khi bắt đầu
String updateString =
"update COFFEES " + "set SALES = ? where COF_NAME = ?";
// ...
PreparedStatement updateSales = con.prepareStatement[updateString];
9, chế độ tự động cam kết được đặt thành falsecon.setAutoCommit[false];
Do đó, không có câu lệnh SQL nào được cam kết cho đến khi phương thức
con.setAutoCommit[false];3 được gọi. Để biết thêm thông tin về chế độ tự động cam kết, hãy xem Giao dịch
Giá trị trả về cho phương thức execUpdate
Trong khi
for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
6 trả về một đối tượng for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
7 chứa kết quả của truy vấn được gửi tới DBMS, thì giá trị trả về cho for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
9 là một giá trị con.setAutoCommit[false];7 cho biết có bao nhiêu hàng của một bảng đã được cập nhật. Chẳng hạn, đoạn mã sau hiển thị giá trị trả về của
for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
9 được gán cho biến con.setAutoCommit[false];9
updateSales.setInt[1, 50]; updateSales.setString[2, "Espresso"]; int n = updateSales.executeUpdate[]; // n = 1 because one row had a change in it
Bảng
updateSales.setInt[1, 50]; updateSales.setString[2, "Espresso"]; int n = updateSales.executeUpdate[]; // n = 1 because one row had a change in it0 được cập nhật; . Bản cập nhật đó ảnh hưởng đến một hàng trong bảng, vì vậy
con.setAutoCommit[false];9 bằng 1
Khi phương pháp
for [Map.Entry e : salesForWeek.entrySet[]] {
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
// ...
}
9 được sử dụng để thực thi câu lệnh DDL [ngôn ngữ định nghĩa dữ liệu], chẳng hạn như trong việc tạo bảng, nó trả về giá trị 0 của con.setAutoCommit[false];7. Do đó, trong đoạn mã sau, thực thi câu lệnh DDL được sử dụng để tạo bảng
updateSales.setInt[1, 50]; updateSales.setString[2, "Espresso"]; int n = updateSales.executeUpdate[]; // n = 1 because one row had a change in it0,
con.setAutoCommit[false];9 được gán giá trị là 0