В чем опасность динамического SQL и можно ли их избежать?

См. Этот отвечать.

Также это:

Имею ли я иммунитет к SQL-инъекциям, если использую хранимые процедуры?

Как избежать внедрения SQL в запросе SQL с оператором Like с использованием параметров?

Могу ли я защитить себя от внедрения SQL-кода, исключив одиночные кавычки и заключив вводимые пользователем данные в одинарные кавычки?

Кстати, я бы не стал использовать EXEC; скорее я бы использовал sp_executesql. См. Эту превосходную статью Проклятие и благословение динамического SQL, чтобы узнать причину и другую информацию по использованию динамического sql.

Вот оптимизированная версия вышеприведенного запроса, в которой не используется динамический SQL ...

Declare @selectedFeature as varchar(MAX),
@searchStr as varchar(100),
@pageCount as int,
@startIndex as int,
@searchId as int,
@venueName as varchar(100),
@range int,
@latitude varchar(100),
@longitude varchar(100),
@showAll int,
@OrderBy varchar(50),
@SearchOrder varchar(10)

Set @startIndex = 1
Set @pageCount = 50



Set @searchStr = 'e'
Set @searchId = 4
Set @OrderBy = 'Address1'
Set @showAll = 1
--Select dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude)


DECLARE @endIndex int
SET  @endIndex = @startIndex + @pageCount -1
;

WITH LogEntries as (
SELECT 
    Row_Number() 
        OVER (ORDER BY 
            CASE @OrderBy
               WHEN 'Distance' THEN Cast(dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude) as varchar(10))
               WHEN 'Name' THEN Name
               WHEN 'Address1' THEN Address1
               WHEN 'RecordId' THEN Cast(RecordId as varchar(10))
               WHEN 'EliteStatus' THEN Cast(EliteStatus as varchar(10))
            END) AS RowNumber,
RecordId,EliteStatus,Name,Description,
Address,TotalReviews,AverageFacilityRating,AverageServiceRating,Address1,Address2,Address3,Address4,Address5,Address6,PhoneNumber,
visitCount,referalCount,requestCount,imgUrl,Latitude,Longitude,
Convert(decimal(10,2),dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude)) as distance
FROM VenueAllData 
where Enabled=1 and EliteStatus <> 3
And 
    (
        (Address5 like @searchStr + '%' And @searchId = 1) OR
        (Address4 like @searchStr + '%' And @searchId = 2) OR
        (Address6 like @searchStr + '%' And @searchId = 3) OR
        (
            (
                @searchId = 4 And 
                    (Name like '%' + @venueName + '%' OR Address like '%'+ @searchStr+'%')
            )
        )
    )
And
    ID in (
        Select VenueID 
        From CachedVenueAttributes 
        --Extra Where Clause for the processing of VenueAttributes using @selectedFeature
    )
And
    (   
        (@showAll = 1) Or
        (@showAll <> 1 and dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude) <= convert(varchar,@range )) 
    )
)

SELECT * FROM  LogEntries 
WHERE RowNumber between @startIndex and @endIndex 
ORDER BY CASE @OrderBy
               WHEN 'Distance' THEN Cast(Distance as varchar(10))
               WHEN 'Name' THEN Name
               WHEN 'Address1' THEN Address1
               WHEN 'RecordId' THEN Cast(RecordId as varchar(10))
               WHEN 'EliteStatus' THEN Cast(EliteStatus as varchar(10))
            END

Единственное, что я не исправил, - это выбор из CachedVenueAttributes, который, кажется, создает оператор where в цикле. Думаю, я мог бы поместить это в функцию с табличным значением и провести рефакторинг изолированно от остальной процедуры.

Мне нравится динамический SQL для поиска.

Там, где я использовал это в прошлом, я использовал подготовленные операторы .Net с любой сгенерированной пользователем строкой, передаваемой в качестве параметра, НЕ включенного как текст в SQL.

Чтобы работать с существующим решением, вы можете сделать несколько вещей для снижения риска.

1. Белый список ввода, проверьте ввод, чтобы он мог содержать только a-zA-Z0-9 \ w (буквенные числа и пробелы) (плохо, если вам нужно поддерживать символы Unicode)
2. Выполните любой динамический sql как пользователь с ограничениями. Установите владельцем хранимой процедуры пользователя, который имеет доступ только для чтения к соответствующим таблицам. запретить запись во все таблицы и т. д. Кроме того, при вызове этого хранимого процесса вам может потребоваться сделать это с пользователем с аналогичными ограничениями на то, что они могут делать, поскольку он показывает, что MS-SQL выполняет динамический sql в сохраненном процессе как вызывающий пользователь, а не владелец хранимого процесса.

Я понял, что это действительно старый пост, но когда делал что-то вроде:

    AND
    (   
        (@showAll = 1) 
        OR (@showAll <> 1 
            AND dbo.GeocodeDistanceMiles(Latitude,Longitude,@latitude,@longitude) <= convert(varchar,@range)) 
    )

... OPTION(RECOMPILE) обычно помогает выбрать более лаконичный план, если он не будет выполняться тысячу раз в секунду или что-то в этом роде.