替代TSQL

Question

这个查询运行得很好，正如您所看到的，由于@SearchCriteria的varchar，我必须在@SearchCriteria和查询的其余部分之间进行拆分。如果我强制的话，语法运行得很好，但是当你查询的时候它不会返回任何东西，因为extra‘

你能帮上忙吗？

ALTER PROCEDURE [dbo].[sp_rte_GetRateList]
(
    @TenantID        INT,
    @CustomerID      BIGINT = -1,   
    @SearchCriteria  VARCHAR(64) = '', 
    @SortBy          VARCHAR(16) = '',
    @SortType        VARCHAR(16) = '',
    @Debug           BIT = 0
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(4000),
            @paramlist  nvarchar(4000)  

    IF (@SearchCriteria = '')
    BEGIN
        SELECT @sql = 'SELECT r.TenantID, r.RateID, r.RateGUID, r.RateCode, r.RateName, r.RateDescription, r.ValidityUTCDate, r.CreatedUTCTimeStamp, 
                            r.CreatedIP, r.CreatedBy, r.LastModifiedUTCTimeStamp, r.LastModifiedIP, r.LastModifiedBy, r.IsActive, 
                            c.CustomerID, c.CustomerName, rt.RateTypeID, rt.RateTypeName, s.SupplierID, s.SupplierName, r.FixedLineAmount, r.MobileAmount, r.DataAmount, r.OtherAmount, 
                            (r.FixedLineAmount + r.MobileAmount + r.DataAmount + r.OtherAmount) AS TotalAmount, 
                            r.CreatedUTCTimeSTamp,
                            STUFF((SELECT '', '' + ct.CustomerTypeName 
                                    FROM glb_CustomerTypes ct JOIN glb_CustomerCustomerTypes cct ON cct.CustomerTypeID = ct.CustomerTypeID 
                                    WHERE cct.CustomerID = C.CustomerID 
                                    GROUP BY ct.CustomerTypeName FOR XML PATH('''')), 1, 2, '''') AS CustomerTypeName 
                        FROM dbo.rte_Rates r 
                                INNER JOIN dbo.rte_RateTypes rt ON r.RateTypeID = rt.RateTypeID 
                                INNER JOIN dbo.glb_Suppliers s ON r.SupplierID = s.SupplierID
                                INNER JOIN dbo.glb_Customers c ON r.CustomerID = c.CustomerID 
                                INNER JOIN dbo.glb_Addresses a ON c.CustomerID = a.CustomerID
                                INNER JOIN dbo.glb_AddressTypes at ON a.AddressTypeID = at.AddressTypeID
                        WHERE   at.AddressTypeCode = ''GLB_ADT_PHYSCLDDRS'' AND
                                r.TenantID = @xTenantID AND 
                                rt.TenantID = @xTenantID AND 
                                s.TenantID = @xTenantID AND 
                                r.IsActive = 1 AND
                                rt.IsActive = 1 AND
                                c.IsActive = 1 AND 
                                c.CustomerID = @xCustomerID '
    END
    ELSE
    BEGIN
        SELECT @sql = 'SELECT r.TenantID, r.RateID, r.RateGUID, r.RateCode, r.RateName, r.RateDescription, r.ValidityUTCDate, r.CreatedUTCTimeStamp, 
                            r.CreatedIP, r.CreatedBy, r.LastModifiedUTCTimeStamp, r.LastModifiedIP, r.LastModifiedBy, r.IsActive, 
                            c.CustomerID, c.CustomerName, rt.RateTypeID, rt.RateTypeName, s.SupplierID, s.SupplierName, r.FixedLineAmount, r.MobileAmount, r.DataAmount, r.OtherAmount, 
                            (r.FixedLineAmount + r.MobileAmount + r.DataAmount + r.OtherAmount) AS TotalAmount, 
                            r.CreatedUTCTimeSTamp,
                            STUFF((SELECT '', '' + ct.CustomerTypeName 
                                    FROM glb_CustomerTypes ct JOIN glb_CustomerCustomerTypes cct ON cct.CustomerTypeID = ct.CustomerTypeID 
                                    WHERE cct.CustomerID = C.CustomerID 
                                    GROUP BY ct.CustomerTypeName FOR XML PATH('''')), 1, 2, '''') AS CustomerTypeName 
                        FROM dbo.rte_Rates r 
                                INNER JOIN dbo.rte_RateTypes rt ON r.RateTypeID = rt.RateTypeID 
                                INNER JOIN dbo.glb_Suppliers s ON r.SupplierID = s.SupplierID
                                INNER JOIN dbo.glb_Customers c ON r.CustomerID = c.CustomerID 
                                INNER JOIN dbo.glb_Addresses a ON c.CustomerID = a.CustomerID
                                INNER JOIN dbo.glb_AddressTypes at ON a.AddressTypeID = at.AddressTypeID
                        WHERE   at.AddressTypeCode = ''GLB_ADT_PHYSCLDDRS'' AND
                                r.TenantID = @xTenantID AND 
                                rt.TenantID = @xTenantID AND 
                                s.TenantID = @xTenantID AND 
                                r.IsActive = 1 AND
                                rt.IsActive = 1 AND
                                c.IsActive = 1 AND 
                                c.CustomerID = @xCustomerID AND 
                                (r.RateCode LIKE ''%' + @SearchCriteria + '%'' OR
                                 r.RateName LIKE ''%' + @SearchCriteria + '%'' OR
                                 rt.RateTypeName LIKE ''%' + @SearchCriteria + '%'' OR
                                 r.RateDescription LIKE ''%' + @SearchCriteria + '%'' OR
                                 s.SupplierCode LIKE ''%' + @SearchCriteria + '%'' OR
                                 s.SupplierName LIKE ''%' + @SearchCriteria + '%'' OR
                                 c.CustomerCode LIKE ''%' + @SearchCriteria + '%'' OR
                                 c.CustomerName LIKE ''%' + @SearchCriteria + '%'' OR
                                 c.CustomerDescription LIKE ''%' + @SearchCriteria + '%'' ) '                       
    END

    SELECT @sql = @sql + 'ORDER BY ' + @SortBy + ' ' + @SortType

    IF (@Debug = 1) PRINT @sql   

    SELECT @paramlist = '@xTenantID INT, @xCustomerID BIGINT'

    EXEC sp_executesql @sql, @paramlist, @TenantID, @CustomerID
END

Answer 1

你可以将@SearchCriteria中的任何引号都加两倍，但这并不能保护你免受所有形式的SQL注入--你只能通过摆脱动态SQL来做到这一点。

我不能百分之百确定首先需要 dynamic SQL来解决这个特定的问题。

Answer 2

我认为您最好将@SearchCriteria初始化为NULL：

ALTER PROCEDURE [dbo].[sp_rte_GetRateList]
(   ...
    @SearchCriteria      VARCHAR(64), --inits as NULL
    ....
)

    IF @SearchCriteria IS NOT NULL
    BEGIN

       SET @SearchCriteria = REPLACE(@SearchCriteria, '''', '''''')
       ...
    END
    ELSE
       ...

我知道为什么要这样设置动态SQL了--我注意到paramlist中没有@SearchCriteria，所以你不必定义@SearchCriteria的参数实例。当您有来自同一个表的2+列时，可以考虑全文搜索-可能更快，也不太复杂。