Быстрый поиск определенного столбца для определенных ключевых слов с помощью хранимой процедуры

Question

Я написал хранимую процедуру, которая получает:

• "SearchKeys" - ключи для поиска, разделенные "," "key1, key2"
• "ToSearch" - таблицы для поиска в разделенных "," с колонками после ":" разделенных "." "Table1:column1.column2,table2:column1.column2"

В конце процедуры возвращается таблица с именем таблицы и идентификатором строки, в которой был найден ключ.

вот код:

--Search keys in tables
CREATE PROCEDURE [dbo].[Search_All]
(
    @SearchKeys nvarchar(50), --Keys to search separated by ','
    @ToSearch varchar(200) --Tables to search in separated by ',' with colums after ':' separated by '.'
)
AS
BEGIN
    --create table with found values
    CREATE TABLE #Results (TargetId int, DBName varchar(20))

    --Split SearchKeys to Keys
    WHILE LEN(@SearchKeys) > 0
     BEGIN
        DECLARE @Key NVARCHAR(25)
        IF CHARINDEX(',',@SearchKeys) > 0
            SET  @Key = SUBSTRING(@SearchKeys,0,CHARINDEX(',',@SearchKeys))
        ELSE
            BEGIN
            SET  @Key = @SearchKeys
            SET @SearchKeys = ''
        END
        --Split ToSearch to Tables
        WHILE LEN(@ToSearch) > 0
         BEGIN
            DECLARE @TableAndColums VARCHAR(200)
            IF CHARINDEX(',',@ToSearch) > 0
                SET  @TableAndColums = SUBSTRING(@ToSearch,0,CHARINDEX(',',@ToSearch))
            ELSE
                BEGIN
                SET  @TableAndColums = @ToSearch
                SET @ToSearch = ''
                END
            SET @ToSearch = REPLACE(@ToSearch,@TableAndColums + ',' , '')
            --Split @TableAndColums to Table and Colums
            --Select Table
            DECLARE @Table VARCHAR(25)
            SET  @Table = SUBSTRING(@TableAndColums,0,CHARINDEX(':',@TableAndColums))
            SET @TableAndColums = REPLACE(@TableAndColums,@Table + ':' , '')
            --Split to Colums
            WHILE LEN(@TableAndColums) > 0
             BEGIN
                DECLARE @Column VARCHAR(25)
                IF CHARINDEX('.',@TableAndColums) > 0
                    SET  @Column = SUBSTRING(@TableAndColums,0,CHARINDEX('.',@TableAndColums))
                ELSE
                    BEGIN
                    SET  @Column = @TableAndColums
                    SET @TableAndColums = ''
                    END

                    BEGIN
                --insert result in to #Results table
                INSERT INTO #Results
                EXEC
                    (
                        'SELECT ' + @Table + '.Id AS ''TargetId'', '''+@Table+''' AS ''DBName''
                        FROM ' + @Table +
                        ' WHERE  ' + @Column + ' LIKE N''%' + @Key + '%'''
                    )
                END

             SET @TableAndColums = REPLACE(@TableAndColums,@Column + '.' , '')
            END
         END
     SET @SearchKeys = REPLACE(@SearchKeys,@Key + ',' , '')
    END
    --return found values
    SELECT DISTINCT TargetId , DBname FROM #Results
END

По какой-то причине он ищет только первый ключ, игнорируя все остальные ключи. Я не могу выяснить, почему это происходит. Пожалуйста помоги!

Answer 1

Самое первое, о чем я здесь предупрежу, это то, что ваша процедура широко открыта для инъекционной атаки. Инъекционная атака сама по себе является широкой темой. Если вам интересно, я предлагаю прочитать эту статью. Если вам абсолютно необходим интерфейс этого типа (то есть вы не можете использовать статический тип SQL или что-то вроде Entity Framework для выполнения запросов за вас), вы должны ДОЛЖНЫ убедиться, что все строки выполняются во время выполнения (например, @column, @table, @key) параметризованы или заключены в скобки. Эта процедура, как написано, также завершится ошибкой, если введенная таблица не содержит столбец ID или если введенный столбец не существует.

http://www.sommarskog.se/dynamic_sql.html

Что касается разделения строк, я бы посмотрел статью ниже. Хотя нет способа избавиться от необходимости циклически проходить по каждой таблице, поместив все строки поиска в таблицу с помощью функции разбиения строк, подобной тем, которые упомянуты в этой статье, вы можете искать все условия поиска в одной таблице одновременно. Что-то вроде этого:

select *
from #SearchConditions a
inner join dbo.TargetTable b
    on b.Name like '%' + a.SearchKey + '%'

http://www.sqlservercentral.com/articles/Tally+Table/72993/