SQL Server：动态where子句

Question

10 浏览2023年6月12日

匿名的 2023年6月13日

0 Comments

问题：

在配方中的[n]种成分上进行Ajax建议搜索。也就是说：将配方与多种成分进行匹配。

例如：SELECT使用“flower”，“salt”进行配方将产生：“Pizza”，“Bread”，“Saltwater”等等。

表：

Ingredients [
    IngredientsID INT [PK],
    IngredientsName VARCHAR
]
Recipes [
    RecipesID INT [PK],
    RecipesName VARCHAR
]
IngredientsRecipes [
    IngredientsRecipesID INT [PK],
    IngredientsID INT,
    RecipesID INT
]

查询：

SELECT
    Recipes.RecipesID,
    Recipes.RecipesName,
    Ingredients.IngredientsID,
    Ingredients.IngredientsName
FROM
    IngredientsRecipes
    INNER JOIN Ingredients
    ON IngredientsRecipes.IngredientsID = Ingredients.IngredientsID
    INNER JOIN Recipes
    ON IngredientsRecipes.RecipesID = Recipes.RecipesID
WHERE
    Ingredients.IngredientsName IN ('salt', 'water', 'flower')

我目前正在使用ASP.NET C#构建我的查询，因为WHERE子句的动态性质。

我很抱怨我不得不在代码层构建查询，而不能使用存储过程/纯SQL，理论上存储过程/纯SQL应该更快。

你们有没有想法，我如何将所有逻辑从代码层移到纯SQL，或者至少如何优化我正在做的工作的性能？

我正在考虑使用临时表：

第一步：SELECT IngredientsID FROM Ingredients并INSERT INTO temp-table

第二步：SELECT RecipesName FROM Recipes与IngredientsRecipes和temp-table.IngredientsID连接。

0

3 答案

匿名的 · Answer 1 · 2023-08-18T05:05:34+00:00

SQL Server: 动态 where 子句

在处理输入的食材时，根据不同的处理方法，我认为当前的方法存在一些 SQL 注入的风险。

你可以将食材名称追加到连接条件中，这可能会更快。

你也可以对食谱的食材组合进行哈希处理，以便进行快速查找。

我同意。脚本注入问题也是我关注的一点。你能详细解释一下“哈希”吗？

如果用户从可能的食材列表中进行选择，可以在你的 where 语句中编程使用这些食材的 ID，这样就不存在注入问题。

例如，如果你通过将单词“盐”、“水”和“面粉”附加到 MD5 或 SHA1 哈希中，你将得到一个值（你的哈希）。在查找时，你只需要寻找匹配这个值的项目。由于只比较一个值而不是一个列表，这将更快。

这只会给他一个精确匹配。示例查询的逻辑暗示他想要包含任何食材的食谱。不过，既然你提到了，一个技巧是使用位掩码来表示你的值。

匿名的 · Answer 2 · 2023-08-22T00:01:01+00:00

问题的出现原因：该问题的出现是由于在SQL Server中使用动态where子句时，可能会导致SQL注入攻击的风险。

解决方法：为了避免SQL注入攻击，可以将where子句参数化。以下是一个示例代码的解决方法：

using System.Data;
using System.Data.SqlClient;
using System.Text;
class Foo
{
    public static void Main()
    {
        string[] parameters = { "salt", "water", "flower" };
        SqlConnection connection = new SqlConnection();
        SqlCommand command = connection.CreateCommand();
        StringBuilder where = new StringBuilder();
        
        for (int i = 0; i < parameters.Length; i++)
        {
            if (i != 0)
                where.Append(",");
                
            where.AppendFormat("{0}", i);
            
            command.Parameters.Add(new SqlParameter("Param" + i, parameters[i]));
        }
    }
}

以上代码将where子句中的参数进行了参数化处理，通过使用SqlParameter类将参数添加到SqlCommand对象的Parameters属性中，从而避免了SQL注入攻击的风险。

匿名的 · Answer 3 · 2023-06-23T00:44:59+00:00

原因：在SQL Server中，动态where子句的问题通常出现在需要根据不同条件查询数据库的情况下。如果使用的是SQL Server 2008或Oracle，可以使用表值参数来解决这个问题。但如果使用的是SQL Server 2005，则可以使用XML来模拟这种能力。如果使用的是早于2005年的版本，则需要将id连接为单个字符串，并创建一个UDF来解析它们。

解决方法1：使用表值参数（SQL Server 2008或Oracle）

解决方法2：使用XML来模拟此功能（SQL Server 2005）

解决方法3：将id连接为单个字符串，并创建UDF来解析它们（早于SQL Server 2005）

示例代码如下：

解决方法1：


CREATE TYPE dbo.IdList AS TABLE
(
    Id INT
);
GO
CREATE PROCEDURE dbo.MyProcedure
    @Ids dbo.IdList READONLY
AS
BEGIN
    SELECT *
    FROM YourTable
    WHERE Id IN (SELECT Id FROM @Ids)
END

解决方法2：


CREATE PROCEDURE dbo.MyProcedure
    @Ids XML
AS
BEGIN
    SELECT *
    FROM YourTable
    WHERE Id IN (
        SELECT Id FROM (
            SELECT T.c.value('.', 'INT') AS Id
            FROM @Ids.nodes('/Ids/Id') AS T(c)
        ) AS X
    )
END

解决方法3：


CREATE FUNCTION dbo.ParseIds
(
    @Ids VARCHAR(MAX)
)
RETURNS @Result TABLE (Id INT)
AS
BEGIN
    DECLARE @Id INT
    WHILE LEN(@Ids) > 0
    BEGIN
        SET @Id = CASE WHEN CHARINDEX(',', @Ids) > 0 THEN LEFT(@Ids, CHARINDEX(',', @Ids) - 1) ELSE @Ids END
        SET @Ids = CASE WHEN CHARINDEX(',', @Ids) > 0 THEN SUBSTRING(@Ids, CHARINDEX(',', @Ids) + 1, LEN(@Ids) - CHARINDEX(',', @Ids)) ELSE '' END
        INSERT INTO @Result (Id) VALUES (@Id)
    END
    RETURN
END
GO
CREATE PROCEDURE dbo.MyProcedure
    @Ids VARCHAR(MAX)
AS
BEGIN
    SELECT *
    FROM YourTable
    WHERE Id IN (SELECT Id FROM dbo.ParseIds(@Ids))
END