Diễn Đàn SEO - vnseo.edu.vn - Khi bạn cần viết một thủ tục, trong đó tùy thuộc vào giá trị của các tham số đầu vào mà câu lệnh SQL cần thực hiện sẽ thay đổi, bạn cần tạo lập chuỗi lệnh SQL trong chương trình và thực thi chuỗi này. Chuỗi lệnh SQL đó được gọi là sql động. Trong nhiều trường hợp SQL Server có thể thực hiện thẳng câu lệnh cùng với tham số được cung cấp, ví dụ: SELECT * FROM dbo.KhachHang WHERE KhachHang_ID = @KhachHang_ID Tuy nhiên có những tình huống không thể thực hiện thẳng được như vậy và bạn phải dùng sql động. Ví dụ khi có một tham số vào qui định tên bảng cần được truy vấn; hoặc khi bạn có một đoạn sql code lưu trữ trong database (như một biểu thức tính toán hoặc mệnh đề WHERE…) và bạn cần ghép vào thành một lệnh SQL hoàn chỉnh để thực thi. Các cách thực thi chuỗi sql động Có hai cách là EXEC() và sp_ExecuteSql. EXEC() có cú pháp rất đơn giản: EXEC(@SqlString) trong đó @SqlString là chuỗi sql động Mã: EXEC('SELECT * FROM dbo.Tblxx x WHERE col1 = ' + @val) --hoặc DECLARE @SqlStr VARCHAR(1000) SET @SqlStr = 'SELECT * FROM dbo.Tblx xx WHERE col1 = ' + @val EXEC(@SqlStr) Đơn giản như vậy nhưng EXEC() luôn đi kèm với hai nhược điểm chính, đó là lỗi sql injection và biên dịch lại code. Sql injection là khi hacker gửi kèm các đoạn sql code phá hoại vào giá trị của tham số, và hệ thống thực hiện đoạn code đó ngoài tầm kiểm soát của bạn. Biên dịch lại code là khi hệ thống không sử dụng lại được kế hoạch thực thi đã lưu sẵn, mà phải biên dịch lại vì nó luôn nhìn thấy câu lệnh là mới thực hiện lần đầu. Mời bạn đọc bài Vì sao nên tránh viết SQL code trong ứng dụng, mục 1 và 2 để xem giải thích rõ hơn về hai vấn đề trên. Một điều tôi muốn lưu ý là Sql injection dù không khó chống nhưng vẫn là lỗ hổng số một ở rất nhiều website. Cách đây vài tháng một website của công ty tôi cũng dính lỗi này, cho dù gói phần mềm CMS mua rất đắt tiền. Rất may không mất dữ liệu, nhưng hacker đã chèn vào database vài triệu bản tin rác sau một thời gian ngắn, đến khi bộ phận quản lý mạng phát hiện ra và chặn lại. Vì hai nhược điểm trên cho nên EXEC() chỉ thích hợp với các tác vụ dùng nội bộ bên trong hệ thống, không liên quan đến ứng dụng có tương tác với bên ngoài. Sp_ExecuteSql khắc phục được hai nhược điểm trên (với điều kiện bạn viết đúng cách). Cú pháp của sp_executesql như sau: Mã: EXEC SP_EXECUTESQL @STATEMENT = @SqlStr, @params = @ParamDefinition, @Param1 = Val1, @Param2 = Val2 ... Trong đó hai tham số đầu là bắt buộc, các tham số còn lại là tùy chọn @statement: là câu lệnh bạn yêu cầu thực hiện, có kiểu dữ liệu NVARCHAR(MAX) (với SQL Server 2000 là NTEXT). Chú ý là nó chỉ chấp nhận kiểu NVARCHAR là unicode chứ không chấp nhận kiểu VARCHAR. @params: là định nghĩa các tham số dùng trong câu lệnh, cũng yêu cầu kiểu dữ liệu NVARCHAR(MAX) (hoặc NTEXT với SQL Server 2000) Các tham số còn lại dùng để gán giá trị cho các tham số đã được khai báo trong @ParamDefinition Ví dụ: Mã: DECLARE @STATEMENT NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX) SET @SqlStr = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID2 AND OrderQty > @MinQty2' SET @ParamDefinition = N'@ProductID INT, @MinQty INT' EXEC SP_EXECUTESQL @STATEMENT = @SqlStr, @params = @ParamDefinition, @ProductID2 = @ProductID, @MinQty2 = @MinQty Trong ví dụ trên, câu lệnh sql dùng hai tham số là @ProductID2 và @MinQty2, chúng được khai báo trong @ParamDefinition và được gán giá trị thông qua hai tham số tùy chọn ở cuối lệnh EXEC sp_executesql. Nếu câu lệnh sql dùng ba tham số thì cả ba đều phải được khai báo trong @ParamDefinition và kèm theo ba tham số tùy chọn để gán giá trị cho chúng. Lưu ý là @ProductID và @MinQty được truyền vào qua thủ tục, còn @ProductID2 và @MinQty2 được dùng trong nội bộ lệnh sp_executesql. Ta có thể dùng lại tên @ProductID và @MinQty vì chúng có phạm vi (scope) khác với thủ tục, nhưng như vậy là tự làm rắc rối thêm cho chính mình. Cách viết trong ví dụ trên gọi là cách viết tham số hóa cho câu lệnh. Nó hóa giải được vấn đề sql injection vì nguyên giá trị được truyền vào qua tham số sẽ được sử dụng, không có lệnh phụ nào được tạo ra để thực hiện ngoài ý muốn. Nó cũng cho phép SQL Server dùng lại kế hoạch thực thi của câu lệnh (tránh phải biên dịch lại code) vì bản thân câu lệnh vẫn như cũ chỉ có tham số thay đổi. Đây là phương pháp thực hiện sql động được khuyến cáo của Microsoft, thực tế Linq to Sql cũng dùng thủ tục này. Ở trên tôi có nhắc sp_executesql chỉ phát huy tác dụng nếu bạn viết đúng cách. Cách đúng là phải tách các tham số như ví dụ trên đây. Còn nếu bạn vẫn tạo chuỗi lệnh sql từ các tham số đầu vào rồi thực hiện nó, thì kết quả sẽ không khác gì so với EXEC(). Cách viết sai (không nên dùng): Mã: DECLARE @STATEMENT NVARCHAR(MAX) SET @SqlStr = N' SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = ' + CAST(@ProductID AS VARCHAR) + ' AND OrderQty > ' + CAST(@MinQty AS VARCHAR) EXEC SP_EXECUTESQL @SqlStr Phiên bản áp dụng: SQL Server 2000 trở lên Nguồn: sqlviet.com
