Sql Động

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

EXEC('SELECT * FROM dbo.Tblxxx WHERE col1 = ' + @val)
--hoặc
DECLARE @SqlStr VARCHAR(1000)
SET @SqlStr = 'SELECT * FROM dbo.Tblxxx 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:

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ụ:

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):

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

Một số đặc tính của sql động

Dù bạn dùng phương pháp thực hiện nào, sql động cũng có những đặc tính mà bạn cần nắm được khi sử dụng
Ngữ cảnh mới: chuỗi sql động khi thực thi sẽ tạo ra một ngữ cảnh mới, khác với ngữ cảnh mà thủ tục chứa nó đang thực thi. Điều này dẫn đến:

• Các biến được khai báo trong thủ tục sẽ không truy cập được bên trong đoạn sql động; thậm chí bạn có thể khai báo biến bên trong sql động trùng tên với một biến trong trủ tục mà không bị phàn nàn gì.
• Bảng tạm được tạo trong thủ tục có thể dùng trong đoạn sql động. Tuy nhiên bảng tạm tạo bên trong đoạn sql động chỉ tồn tại bên trong đó, khi đoạn sql động kết thúc thực thi thì bảng cũng bị xóa.
• Bên trong sql động bạn có thể dùng lệnh USE để đổi sang database khác và các lệnh SQL trong đoạn đó sẽ thực hiện trên database mới; nhưng khi kết thúc đoạn sql động thì ngữ cảnh lại trở về database lúc đầu khi thủ tục bắt đầu thực thi.
• sql động có kế hoạch thực thi riêng không nằm trong kế hoạch thực thi của thủ tục. Khi đoạn sql động cần biên dịch lại, nó có thể có kế hoạch thực thi mới nhưng kế hoạch thực thi của thủ tục (các phần khác) vẫn được giữ nguyên.

Quyền: Sql động đòi hỏi user thực hiện thủ tục chứa nó có đủ quyền trên các bảng được dùng trong đoạn đó. Ví dụ đoạn sql động thực hiện SELECT từ một bảng và UPDATE vào một bảng khác, thì user cần phải có quyền read trên bảng thứ nhất và write trên bảng thứ hai. Trong khi đó nếu thủ tục không chứa sql động thì user chỉ cần duy nhất quyền execute thủ tục. Điều này gây khó khăn khi bạn muốn hướng tất cả các truy nhập vào database thông qua thủ tục và gỡ bỏ hết các quyền trên bảng. Với SQL Server 2005 trở lên, một cách khắc phục là bạn dùng mệnh đề WITH EXECUTE AS ‘username’ khi viết thủ tục (đưa vào ngay sau tên thủ tục), để giả danh một username có đủ quyền cần thiết khi thực thi thủ tục.

Phân giải tên đối tượng: SQL Server luôn phân giải (resolve) tên các đối tượng trong database (như tên bảng, tên cột) khi biên dịch câu lệnh, vì thế tên đối tượng không thể được truyền qua tham số với sql động. Ví dụ, bạn muốn truyền qua tham số tên bảng cần truy vấn, đoạn code sau sẽ báo lỗi:

DECLARE @STATEMENT NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM @Tenbang2 WHERE ProductID = @ProductID2'
SET @ParamDefinition = N'@Tenbang2 NVARRCHAR(100),@ProductID2 int'
EXEC SP_EXECUTESQL
@STATEMENT = @SQL,
@params = @ParamDefinition,
@Tenbang2 = @Tenbang,
@ProductID2 = @ProductID

Bạn cần viết như sau (nhớ luôn luôn dùng hàm QUOTENAME với tên đối tượng để ngăn chặn sql injection):

DECLARE @STATEMENT NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@Tenbang) + ' WHERE ProductID = @ProductID2'
SET @ParamDefinition = N'@ProductID2 int'
EXEC SP_EXECUTESQL
@STATEMENT = @SQL,
@params = @ParamDefinition,
@ProductID2 = @ProductID

Sự phụ thuộc: Sql động cũng cản trở việc theo dõi sự phụ thuộc (dependency) giữa các đối tượng. View sys.sql_dependencies (phiên bản 2005 trở lên) duy trì các thông tin như bảng Tblxyz được dùng bởi những thủ tục nào… Nếu thủ tục có một đoạn sql động truy nhập bảng này thì thông tin đó không xuất hiện trong sys.sql_dependencies. Một cách bạn có thể làm là truy vấn view INFORMATION_SCHEMA.ROUTINES

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Tblxyz%'

Tóm lại sql động rất cần thiết để mở rộng khả năng thực thi của SQL Server, tuy nhiên nó cũng kèm theo một số nhược điểm. Nói chung khi viết code trên SQL Server bạn nên cố gắng dùng sql tĩnh, và chỉ dùng sql động khi không có giải pháp thay thế (có lẽ Tìm Kiếm Với Nhiều Tham Số là một trường hợp hiếm hoi sql động phát huy được ưu thế so với sql tĩnh).

Phiên bản áp dụng: SQL Server 2000 trở lên

tham khao tai day

Sql Động 2016-02-27T08:29:00+07:00 Rating: 4.5 Diposkan Oleh: http://pdunoteit.blogspot.com/