2

i am trying to query without success, what i am trying to do is that

  ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
  @filterCol NVARCHAR(20) = NULL, --<<<<
  @filterValue NVARCHAR(40) = NULL, --<<<<
  @PageNumber INT,
  @PageSize   INT
AS
BEGIN
  SET NOCOUNT ON;
        SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    WHERE       @filterCol like '%' + @filterValue + '%' --<<<< 
    ORDER BY    id
    OFFSET      @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);

    select count(1) as totalCount from [dbo].tblEmployees5m
END

i am trying to apply filter to the result, according to @filterCol and @filterValue - if they are not NULL then I want to return the results with the where clause (which don't work now).

ELSE, if there 2 values are NULL then don't apply the where clause. @filterCol will hold the column name. @filterValue will hold the column value.

how can I achieve that? there is a better way?

  • 4
    You will need to use dynamic SQL to reference a column name, you can't use a variable there. Please also read up on SQL injection before you get too far down that path. – Aaron Bertrand Sep 5 '18 at 12:34
  • 1
    @e.meir, I think you need to use dynamic sql for this.This will be a good read for you. – Biju jose Sep 5 '18 at 12:35
  • 1
    I do not understand why has this question been flagged as not belonging on our site. I think our site is the best place to ask this question... I am voting to leave open. – Mr.Brownstone Sep 6 '18 at 19:32
1

Here's the code:

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
    @filterCol NVARCHAR(20) = NULL, --<<<<
    @filterValue NVARCHAR(40) = NULL, --<<<<
    @PageNumber INT,
    @PageSize   INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    '
    IF @filterCol IS NOT NULL
    BEGIN
        SET @SQL = @SQL + 'WHERE       [' + @filterCol + '] LIKE ''%' + @filterValue + '%'''
    END

    SET @SQL = @SQL + '
    ORDER BY    id
    OFFSET      ' + CAST((@PageSize * (@PageNumber - 1)) AS VARCHAR(50)) + ' ROWS
    FETCH NEXT  ' + CAST(@PageSize AS VARCHAR(25)) + ' ROWS ONLY OPTION (RECOMPILE);'

    EXEC sp_executesql @SQL

    select count(1) as totalCount from [dbo].tblEmployees5m
END

As has been mentioned, dynamic SQL is vulnerable to SQL Injection, so take care when utilising dynamic SQL that you're sufficiently protected against these attacks.

SQL Injection Protection: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

  • so, this approach solve SQL injection ? – E.Meir Sep 7 '18 at 13:03
  • @E.Meir No, this procedure simply fixes the code problems preventing your proc from executing. SQL injection is protected against by validating and sanitising input into stored procedures, often at the application layer, to prevent malicious code from executing. Take a look at the article I linked at the bottom of the answer for further info on SQL Injection. – HandyD Sep 13 '18 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.