sql - 使用存储过程访问数据

Question

“最佳实践”之一是通过存储过程访问数据。我明白为什么这种情况很好。
我的动机是拆分数据库和应用程序逻辑(表可以更改，如果存储过程的行为相同)，SQL注入(inject)防御(用户不能执行“select * from some_tables”，他们只能调用存储过程)，以及安全性(在存储过程中可以是安全的“任何东西”，用户不能选择/插入/更新/删除数据，这不适合他们)。

我不知道如何使用动态过滤器访问数据。

我正在使用 MSSQL 2005。

如果我有 table :

CREATE TABLE tblProduct (
   ProductID uniqueidentifier -- PK
   , IDProductType uniqueidentifier -- FK to another table
   , ProductName nvarchar(255) -- name of product
   , ProductCode nvarchar(50) -- code of product for quick search
   , Weight decimal(18,4)
   , Volume decimal(18,4)
)

那么我应该创建 4 个存储过程(创建/读取/更新/删除)。

“创建”的存储过程很简单。

CREATE PROC Insert_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN
   INSERT INTO tblProduct ( ProductID, IDProductType, ... etc .. ) VALUES ( @ProductID, @IDProductType, ... etc ... )
END

“删除”的存储过程也很简单。

CREATE PROC Delete_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN
    DELETE tblProduct WHERE ProductID = @ProductID AND IDProductType = @IDProductType AND ... etc ...
END

“更新”的存储过程与“删除”类似，但我不确定这是正确的方法，如何去做。我认为更新所有列效率不高。

CREATE PROC Update_Product( @ProductID uniqueidentifier, @Original_ProductID uniqueidentifier, @IDProductType uniqueidentifier, @Original_IDProductType uniqueidentifier, ... etc ... ) AS BEGIN
   UPDATE tblProduct SET ProductID = @ProductID, IDProductType = @IDProductType, ... etc ...
      WHERE ProductID = @Original_ProductID AND IDProductType = @Original_IDProductType AND ... etc ...
END

最后一个“读取”的存储过程对我来说有点神秘。如何通过复杂条件的过滤器值？我有几点建议:

使用 XML 参数传递 where 条件:

CREATE PROC Read_Product ( @WhereCondition XML ) AS BEGIN
    DECLARE @SELECT nvarchar(4000)
    SET @SELECT = 'SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume FROM tblProduct'

    DECLARE @WHERE nvarchar(4000)
    SET @WHERE = dbo.CreateSqlWherecondition( @WhereCondition ) --dbo.CreateSqlWherecondition is some function which returns text with WHERE condition from passed XML

    DECLARE @LEN_SELECT int
    SET @LEN_SELECT = LEN( @SELECT )
    DECLARE @LEN_WHERE int
    SET @LEN_WHERE = LEN( @WHERE )
    DECLARE @LEN_TOTAL int
    SET @LEN_TOTAL = @LEN_SELECT + @LEN_WHERE
    IF @LEN_TOTAL > 4000 BEGIN
        -- RAISE SOME CONCRETE ERROR, BECAUSE DYNAMIC SQL ACCEPTS MAX 4000 chars
    END

    DECLARE @SQL nvarchar(4000)
    SET @SQL = @SELECT + @WHERE

    EXEC sp_execsql @SQL
END

但是，我认为一个查询的“4000”个字符的限制是丑陋的。

下一个建议是为每一列使用过滤表。将过滤器值插入过滤器表，然后调用具有过滤器 ID 的存储过程:

CREATE TABLE tblFilter (
   PKID uniqueidentifier -- PK
   , IDFilter uniqueidentifier -- identification of filter
   , FilterType tinyint -- 0 = ignore, 1 = equals, 2 = not equals, 3 = greater than, etc ...
   , BitValue bit , TinyIntValue tinyint , SmallIntValue smallint, IntValue int
   , BigIntValue bigint, DecimalValue decimal(19,4), NVarCharValue nvarchar(4000)
   , GuidValue uniqueidentifier, etc ... )

CREATE TABLE Read_Product ( @Filter_ProductID uniqueidentifier, @Filter_IDProductType uniqueidentifier, @Filter_ProductName uniqueidentifier, ... etc ... ) AS BEGIN
   SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume
   FROM tblProduct
   WHERE ( @Filter_ProductID IS NULL
            OR ( ( ProductID IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_ProductID AND FilterType = 1 ) AND NOT ( ProductID IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_ProductID AND FilterType = 2 ) )
      AND ( @Filter_IDProductType IS NULL
            OR ( ( IDProductType IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_IDProductType AND FilterType = 1 ) AND NOT ( IDProductType IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_IDProductType AND FilterType = 2 ) )
      AND ( @Filter_ProductName IS NULL OR ( ... etc ... ) ) 
END

但我认为这个建议有点复杂。

是否有一些“最佳实践”来执行这种类型的存储过程？

Answer 1

对于读取数据，您不需要存储过程来确保安全或分离出逻辑，您可以使用 View 。

只授予 View 上的选择。

您可以限制显示的记录、更改字段名称、将许多表连接成一个逻辑“表”等。