dpenton

Không phải năng động Sql

Giả sử bạn cần loại trừ hoặc đưa các tham số vào một câu lệnh sql. Hãy lấy ví dụ này:

TẠO THỦ TỤC dbo.myProc
@pkA int
, @ var varchar (10)
, @ pkC thập phân (12, 2)

NHƯ

LỰA CHỌN
t.colA, t.colB, t.colC
TỪ
dbo.myTable t
Ở ĐÂU
t.pkA = @pkA
VÀ t.pkB = @ pkB
VÀ t.pkC = @ pkC
Thủ tục này mong đợi ba tham số, cũng như câu lệnh sql. Vì vậy, chúng tôi muốn bỏ qua các tham số dựa trên được thiết lập để NULL. Có nhiều hướng khác nhau để làm điều đó:

Sử dụng sql động trong ngôn ngữ khách hàng của bạn
Sử dụng sql động trong thủ tục lưu trữ của bạn
Sử dụng logic điều kiện trong truy vấn của bạn với các kết hợp khác nhau của OR, AND hoặc COALESCE / ISNULL trong truy vấn của bạn
Sử dụng cấu trúc sql động trong một thủ tục lưu trữ
Hãy để tôi đưa ra một ví dụ của mỗi:

Xây dựng các sql trong một chuỗi như thế này:
string sql = “SELECT cola FROM tblA”;
nếu (prmB! = null && prmB.Length! = 0) sql + = “WHERE colB = ‘” + prmB.Replace (“‘”, “” “) +” ‘;

Bạn phải đối mặt với đảm bảo rằng logic của bạn trong truy vấn cuối cùng là chính xác. Điều này là dễ dàng với một tham số, nhưng khi bạn kết thúc có nhiều hơn một trong mệnh đề WHERE (hoặc các khoản khác) thì logic có thể có được khá phức tạp. Chúng tôi cũng cần phải giải quyết SQL Injection. Tất nhiên, điều này dễ dàng giải quyết bằng cách sử dụng các tham số trên các đối tượng lệnh (như sử dụng một SqlParameter với một đối tượng SqlCommand).

Điều này tương tự như ở trên nhưng bạn đang làm logic trong thủ tục lưu trữ. Bạn vẫn cần phải tranh đua với sql tiêm trong trường hợp này.
Logic điều kiện trong truy vấn của bạn có nghĩa là sử dụng OR và VÀ khi cần:
CHỌN cola FROM tblA
WHERE (colB = @colB OR @colB IS NULL)

— HOẶC LÀ —

CHỌN cola FROM tblA
WHERE colB = COALESCE (@colB, colB)

Điều này bị logic nhánh trong truy vấn của bạn. Nó sẽ trở nên khó khăn hơn cho SQL Server để thực hiện một kế hoạch thực hiện thực tế cho loại truy vấn này, đặc biệt là khi có nhiều điều kiện hơn.

Sử dụng cấu trúc sql động trong thủ tục của bạn:
TẠO THỦ TỤC dbo.myProc
@pkA int
, @ var varchar (10)
, @ pkC thập phân (12, 2)

NHƯ
SET NOCOUNT ON

DECLARE @sql nvarchar (4000)

SET @sql = N ‘
LỰA CHỌN
t.colA, t.colB, t.colC
TỪ
dbo.myTable t
Ở ĐÂU
1 = 1
@pkAREP
@ pkBREP
@ pkCREP ‘

NẾU @pkA KHÔNG NULL
SET @sql = REPLACE (@sql, ‘@pkAREP’, ‘AND a.pkA = @pkA’)

Khác
SET @sql = REPLACE (@sql, ‘@pkAREP’, ”)

NẾU @pkB KHÔNG NULL
SET @sql = REPLACE (@sql, ‘@pkBREP’, ‘AND a.pkB = @pkB’)

Khác
SET @sql = REPLACE (@sql, ‘@pkBREP’, ”)

NẾU @pkC KHÔNG NULL
SET @sql = REPLACE (@sql, ‘@pkCREP’, ‘AND a.pkC = @pkC’)

Khác
SET @sql = REPLACE (@sql, ‘@pkCREP’, ”)

 

EXECUTE “dbo”. “Sp_executesql”
@ sqq
, N ‘@ pkA int, @pkB varchar (10), @pkC thập phân (12, 0)’
, @ pkA = @pkA
, @ pkB = @pkB
, @ pkC = @pkC

RETURN 0
Tôi muốn thảo luận lựa chọn # 4 ở trên. Tôi tin rằng nó để làm một số điều cho nhà phát triển:

Ngăn chặn SQL injection bởi vì bạn đang sử dụng các truy vấn tham số
Caching truy vấn kế hoạch bởi vì bạn đang sử dụng sp_executesql
Kiểm soát dữ liệu đến thủ tục
Ngăn chặn logic phân nhánh trong mệnh đề WHERE của bạn trong truy vấn
Tôi thực sự sẽ làm nhiều hơn một chút với ‘1 = 1’ phần, nhưng cho rõ ràng trong bài này tôi không. Ngoài ra, trong các truy vấn của tôi tôi thường có giá trị luôn luôn được yêu cầu nên ‘1 = 1’ là không cần thiết. Tất nhiên, như là nhà phát triển bạn cần phải đảm bảo rằng bạn không vô tình đặt bất kỳ điểm sql tiêm trong phần năng động của sql của bạn. Nhưng điều này sẽ nhận được đánh bắt trong một đánh giá mã, phải không? 😉

Please give your comments!

Your email address is confidential.

Recent Posts

Recent Comments

    Archives

    Categories

    Meta