Sql Động

Vũ Huy Tâm

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

Pages: 1 2


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

Tags: , , , , ,

14 Comments
Posted on 11/8/2010 | Categories: SQL Server Programming, Sql động

Các bài viết tương tự

Comments
  • chip07 (07/07/2011 11:57 pm)

    Bác cho em hỏi chút, cách viết này:

    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

    và thế này:

    DECLARE @STATEMENT NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
    SET @SqlStr = N’SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID AND OrderQty > @MinQty’
    EXEC SP_EXECUTESQL @SqlStr, N’@ProductID INT, @MinQty INT’, @ProductID, @MinQty

    có gì khác không ạ?

    • ChienVH (30/08/2011 12:10 am)

      @chip07: Cách viết của bạn hoàn toàn đúng nhé :) . Cách viết trên của tác giả có vẻ tường minh hơn. Nhưng cách của bạn được sử dụng nhiều hơn nhé.

  • ChienVH (30/08/2011 12:07 am)

    Hi,
    Thanks bạn, bài viết rất hay. Mình có bổ sung một chút khi sử dụng dynamic SQL như sau:

    Sử dụng dynamic sql thì length của chuỗi dynamic rất dài (vài nghìn kí tự là chuyện quá bình thường). Rất nhiều hệ thống db của khách hàng họ không cho sử dụng kiểu NVARCHAR(MAX) vì một vài lý do nào đó ( theo như mình biết thì khi sử dụng kiểu nvarchar(MAX), chúng ta sẽ không thể tạo được index cho column đó để tăng performance cho query được –> điều này là tối kị đối với hệ thống db quá lớn)

    ==> Chúng ta chỉ sử dụng được nvarchar tối đa là 4000 kí tự. Do đó khi tạo script có nhiều giới hạn.

    • Duy (29/05/2015 10:11 pm)

      Cám ơn bạn đã chia sẻ nhưng mình xin được hỏi kỹ hơn về phần EXEC, chỉ nên EXEC một chuỗi 4000 ký tự nhằm cải thiện tốc độ thôi hay còn ý nghĩa gì khác nữa không ?

  • John (05/01/2012 4:20 am)
    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 INTEXEC SP_EXECUTESQL
    @STATEMENT = @SqlStr,
    @params = @ParamDefinition,
    @ProductID2 = @ProductID,
    @MinQty2 = @MinQty

    Xin hỏi cách thực thi và truyền tham số như thế nào bạn!
    Mình có 1 function search có 4 tham số truyền vào, trong đó 1 tham số bắt buột, 3 tham số còn lại không bắt buộc, tức là 3 tham số này có cũng được, không có cũng được. Nếu user truyền vào 3 tham số này, thì sẽ + thêm điều kiện vào câu lệnh, còn ngược lại thì bỏ qua, chỉ quan tâm đến thông số bắt buộc (bắt buộc phải có tham số truyền vào).

    Vậy hỏi bạn kiểu này viết như thế nào nhé!
    Thanks

  • Teo (28/01/2013 10:46 am)
     @MinQty’
    EXEC SP_EXECUTESQL @SqlStr, N’@ProductID INT, @MinQty INT’, @ProductID, @MinQty
  • Gkhtdl (14/04/2015 11:57 pm)

    Anh ơi cho em hỏi
    Ở trên a có giới thiệu “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ì.” Vậy trong thủ tục có chứa đoạn sql động và thủ tục có biến thì làm sao trong sql động có thể dùng đc các biến này anh

    • Vũ Huy Tâm (15/04/2015 3:09 pm)

      Bạn đọc kỹ lại đoạn code gọi thủ tục SP_EXECUTESQL, có bày cách truyền tham số (hoặc biến) từ thủ tục vào đoạn lệnh SQL động để SP_EXECUTESQL thực hiện:

      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 đoạn lệnh trên, @ProductID và @MinQty là các tham số hoặc biến từ thủ tục

  • nhan (22/05/2015 12:09 am)

    cho em hỏi nếu mình dùng phương thức exec(@xyz) trong câu stored procedure thì có bị dính lỗi sql ịnection không

  • Tuan (12/07/2015 4:05 am)

    cho e hỏi ??
    vd cho csdl :
    ngay makh soluong
    01/01/2005 k1 10
    01/01/2015 k2 20
    01/01/2015 k1 30
    02/01/2015 k3 50
    làm sao để lấy makh động như bảng kq sau :
    ngay k1 k2 k3 … tong
    01/01/2015 30 20 – – 50
    02/01/2015 – – 50 – 50

  • Hải (24/12/2015 12:09 am)

    1.Tôi thường chỉ thấy một câu select chứa tên cột là tường minh :

    VD: select ID,Name from tableA where ID=’xyz’

    2.Bây giờ tôi muốn rằng : thay vì là tên cột tường minh,có thể thay tên cột là tham số hay không ? (Để có thể gọi từ C# chẳng hạn…)
    VD:select @para ,Name from tableA where @para=’xyz’

    Liệu có được ko ? Hoặc có cách nào khác /hoặc kết hợp với C# có cách nào không ?
    Rất mong các a/c chỉ bảo cho .Xin cám ơn !

  • hoc (23/06/2016 9:39 am)

    Bạn nào chỉ dùm mình cách lấy điểm max của 2 lần thi 1 và 2 với.

  •  (20/10/2016 4:36 am)

    Chào anh,

    Nếu
    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

    Với @SqlStr vượt 4000 ký tự, thì mình có thể thực hiện @STATEMENT = @SqlStr1 + @SqlStr2 được không?

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>