在存储过程中使用IN()参数传递多个值-使用MySql数据库

ecbunoof  于 2023-03-17  发布在  Mysql
关注(0)|答案(3)|浏览(279)

例如:-

Select * from test table where some_words IN("some1","some2")

那么在这个查询中,我该如何在存储过程中编写它,其中IN()参数中传递的值是用户传递的值。
在存储过程中-

Create definer Test(In user_input)
Begin
Select* from table where some_words IN(user_input);
End

这里user_input只有一个值,我希望它可以接受逗号分隔的多个值,我该怎么做呢?

ac1kyiln

ac1kyiln1#

可以按如下方式使用预准备语句:
此存储过程将逗号分隔列表作为参数。

CREATE PROCEDURE Test(IN user_input varchar(30))
BEGIN

  SET @sql = CONCAT('select * from mytable where some_words IN ( ', user_input, ' )');
  PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
end

要呼叫SP,请使用:

CALL Test('"some1","some2"');

Demo here

umuewwlo

umuewwlo2#

如果您使用的是SQL Server,我们可以尝试使用adventure数据库,内联表值函数对于优化非常重要。
https://www.sqlshack.com/sql-server-inline-table-valued-function/
对于这一点,如果输入值列表很小,我们可以使用动态T-SQL。

-- simple select
select * from dbo.FactInternetSales
where ProductKey in (336, 346, 311)

这将返回一个包含350行的结果集。

-- multi-line stored procedure
create procedure test2 @list VARCHAR(512)
as
declare @stmt nvarchar(1024);
set @stmt = 'select * from dbo.FactInternetSales where ProductKey in (' + @list + ')';
execute sp_executesql @stmt;

这个存储过程将in子句的逗号分隔列表作为参数。这可以通过检查@list是否为空以及更改动态T-SQL来增强。我将让您完成这些操作。
下面是一个调用存储过程的示例。

-- get rows via sp call
exec test2 @list = N'336, 346, 311'

两个调用的输出相同(select与exec)。

q9rjltbz

q9rjltbz3#

预准备语句的问题在于它们会导致SQL注入。此代码包含以下部分:创建表,插入数据,检查有效列表并返回给定列表的数据。

--
--  1 - create sample table
--

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255)
);

--
--  2 - load sample data
--

-- https://en.wikipedia.org/wiki/List_of_residences_of_presidents_of_the_United_States

-- Insert sample data - President 1
Insert Into Persons values(1, 'Washington', 'George', 'Mount Vernon, Mount Vernon, Virginia');

-- Insert sample data - President 2
Insert Into Persons values(2, 'Adams', 'John', 'Peacefield, Quincy, Massachusetts');

-- Insert sample data - President 3
Insert Into Persons values(3, 'Jefferson', 'Thomas', 'Monticello, Charlottesville, Virginia');

--
--  3 - stored procedure to return presidents by id list
--

DELIMITER $$
CREATE PROCEDURE sp_Valid_List(IN Ids varchar(128), OUT Valid INT)
BEGIN

    -- Declare Variables
    DECLARE Acode INT DEFAULT 0;    
    DECLARE Cnt INT DEFAULT 0;
    
    -- Set Variables
    SET Cnt = 1;
    SET Valid = 1;

   -- Following chars are valid (0-9 and ,)
    WHILE Cnt < LENGTH(Ids)
    DO   
        SET Acode = ASCII(SUBSTRING(Ids, Cnt, 1));
        IF NOT( (Acode >= 48 AND Acode <= 57) OR (Acode = 44) ) THEN
            SET Valid = 0;
        END IF;
        SET Cnt = Cnt + 1;   
    END WHILE;
    
END$$

--
--  4 - stored procedure to return presidents by id list
--

DELIMITER $$
CREATE PROCEDURE sp_GetPresidentById (IN Ids varchar(128))
BEGIN

    -- Variable
    DECLARE RetVal INT DEFAULT 0;
    
    -- Valid list?
    CALL sp_Valid_List(Ids, RetVal);
    
    -- Return results if truee
    IF (RetVal = 1) THEN
        SELECT P.* 
        FROM Persons as P 
        WHERE FIND_IN_SET(P.PersonID, Ids) > 0;
    ELSE
        SELECT '' FROM dual;
    END IF;
    
END$$
DELIMITER ;

有三个对存储过程的调用:sql注入、无效列表和有效列表。三个列表中有两个没有返回结果,因为我们的验证函数告诉程序不要执行正确的代码。请注意,我们使用的是FIND_IN_SET()函数,而不是IN()。后者无法正常工作。PersonId发生了隐式强制转换。

-- call sp (SQL Injection)
CALL sp_GetPresidentById('1); DROP TABLE');

-- call sp (bad data)
CALL sp_GetPresidentById ('1 x 2');

-- call sp (good data)
CALL sp_GetPresidentById ('2,3');

上一个查询的输出示例。

相关问题