Is this stored procedure safe from SQL injection?

Question

This stored proc executes sql with parameters using sp_executesql.
Is it safe from SQL injection?

create procedure ExecutePeopleFilter 
    (@lastNameFilter varchar(20), 
    @companyNameFilter varchar(20), 
    @ageFilter int, 
    @dateFilter datetime)
as
begin
    declare @sql varchar(4000)
    declare @params varchar(1000)
    declare @whereClause varchar(1000)

    set @whereClause = ''

    if ISNULL(@lastNameFilter,'') <> ''
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        if (LEN(@lastNameFilter) < 20) set @lastNameFilter += '%'
        set @whereClause += 'LastName like @lastName '
    end

    if ISNULL(@companyNameFilter,'') <> ''
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        if (LEN(@companyNameFilter) < 20) set @companyNameFilter += '%'
        set @whereClause += 'CompanyName like @companyName '
    end

    if @ageFilter is not null
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '     
        set @whereClause += 'Age = @age '
    end

    if @dateFilter is not null
    begin
        if (LEN(@whereClause) <> 0) set @whereClause += ' and '
        set @whereClause += 'StartDate = @startDate '
    end


    set @sql = 'select FirstName, LastName, CompanyName, Age, StartDate 
        from People'
    if (LEN(@whereClause) <> 0) set @sql += ' where ' + @whereClause

    set @params = '@lastName varchar(20), 
        @companyName varchar(20), 
        @age int, 
        @startDate datetime'

    execute sp_executesql @sql, @params, 
        @lastName = @lastNameFilter, 
        @companyName = @companyNameFilter, 
        @age = @ageFilter, 
        @startDate = @dateFilter
end

Answer 1

To a large extent.

The key to preventing SQL injection is to correctly process the parameters using the "approved" mechanism and avoid string concatenation.

Your code does not create a string with parameters: they are separated and cleared through sp_executesql.

If you did this, this is another matter ... as other answers show

Answer 2

? , , . , MS Enterprise DAAB (.NET), , .

Answer 3

SQL WHERE... :

WHERE ((@x IS NULL) OR (@x = ...)) AND ...

, ..

:

CREATE PROCEDURE ExecutePeopleFilter
  (
   @lastNameFilter varchar(20),
   @companyNameFilter varchar(20),
   @ageFilter int,
   @dateFilter datetime
  )
AS 
  BEGIN
    SELECT FirstName, LastName, CompanyName, Age, StartDate
      FROM People
      WHERE (
             (ISNULL(@lastNameFilter, '') = '')
             OR (LastName LIKE @lastNameFilter+'%')
            )
        AND (
             (ISNULL(@companyNameFilter, '') = '')
             OR (LastName LIKE @companyNameFilter+'%')
            )
        AND (
             (@ageFilter IS NULL)
             OR (Age = @ageFilter)
            )
        AND (
             (@dateFilter IS NULL)
             OR (StartDate = @dateFilter)
            ) ;
  END

Answer 4

, , , SQL-, , SQL- ( ). (, @startDate ).

Answer 5

, concat sp_executesql , SQL- .

, , LINQ - , , .

, varchar (20) , , , .

Answer 6

. , . , , , .

. , . sp_executesql, , .

---

, , , :

create procedure ExecutePeopleFilter 
    (@lastNameFilter varchar(20) = NULL, 
    @companyNameFilter varchar(20) = NULL, 
    @ageFilter int = NULL, 
    @dateFilter datetime = NULL)
as
begin

    if (LEN(@lastNameFilter) < 20) set @lastNameFilter += '%'
    if (LEN(@companyNameFilter) < 20) set @companyNameFilter += '%'

    SELECT FirstName, LastName, CompanyName, Age, StartDate 
    FROM People
    WHERE  LastName    LIKE COALESCE(@lastNameFilter, LastName) 
       AND CompanyName LIKE COALESCE(@companyNameFilter, CompanyName)
       AND Age          =   COALESCE(@ageFilter, Age)
       AND StartDate    =   COALESCE(@dateFilter, StartDate)

end

sql , WHERE "OR".

Answer 7

... "".

, , , temp , ? , , .

CREATE TABLE #People
   (personid int)
INSERT INTO #People SELECT personid FROM people
IF NOT @lastNameParam IS NULL
   DELETE FROM #People WHERE personid NOT IN (SELECT personid FROM people WHERE lastname LIKE @lastNameParam + '%')
-- And so on...