Stored procedure to implement a search filter











up vote
2
down vote

favorite












I've implemented a stored procedure for a search process SQL Server 2008. I'm not sure if everything I created is correct. Also I'm wondering if this code has any leaks or vulnerability on SQL Injection. Here is my stored procedure:



USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[Search_Dictionary] Script Date: 08/09/2018 19:17:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Search_Dictionary]
@FilterBy int = NULL,
@Name varchar(50) = NULL,
@Code char(2) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT RecID, Status, Code, Name
FROM Dictionary
WHERE
(@FilterBy = 1 AND Name LIKE '%'+@Name+'%')
OR
(@FilterBy = 2 AND Code = @Code)
OR
(@FilterBy = 3 AND @Name IS NULL AND @Code IS NULL);
END


Here is example on how I call this procedure:



EXEC Search_Dictionary @FilterBy = 1, @Name = "Grant", @Code = NULL;


I just want to prevent, if for example Filter By is 2 that should search query by Code column returns any result if user pass word Grant. In that case should return 0 records.



Also if anyone have any suggestions on how to improve the code please let me know.










share|improve this question




























    up vote
    2
    down vote

    favorite












    I've implemented a stored procedure for a search process SQL Server 2008. I'm not sure if everything I created is correct. Also I'm wondering if this code has any leaks or vulnerability on SQL Injection. Here is my stored procedure:



    USE [TestDB]
    GO
    /****** Object: StoredProcedure [dbo].[Search_Dictionary] Script Date: 08/09/2018 19:17:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[Search_Dictionary]
    @FilterBy int = NULL,
    @Name varchar(50) = NULL,
    @Code char(2) = NULL
    AS
    BEGIN
    SET NOCOUNT ON;

    SELECT RecID, Status, Code, Name
    FROM Dictionary
    WHERE
    (@FilterBy = 1 AND Name LIKE '%'+@Name+'%')
    OR
    (@FilterBy = 2 AND Code = @Code)
    OR
    (@FilterBy = 3 AND @Name IS NULL AND @Code IS NULL);
    END


    Here is example on how I call this procedure:



    EXEC Search_Dictionary @FilterBy = 1, @Name = "Grant", @Code = NULL;


    I just want to prevent, if for example Filter By is 2 that should search query by Code column returns any result if user pass word Grant. In that case should return 0 records.



    Also if anyone have any suggestions on how to improve the code please let me know.










    share|improve this question


























      up vote
      2
      down vote

      favorite









      up vote
      2
      down vote

      favorite











      I've implemented a stored procedure for a search process SQL Server 2008. I'm not sure if everything I created is correct. Also I'm wondering if this code has any leaks or vulnerability on SQL Injection. Here is my stored procedure:



      USE [TestDB]
      GO
      /****** Object: StoredProcedure [dbo].[Search_Dictionary] Script Date: 08/09/2018 19:17:48 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      ALTER PROCEDURE [dbo].[Search_Dictionary]
      @FilterBy int = NULL,
      @Name varchar(50) = NULL,
      @Code char(2) = NULL
      AS
      BEGIN
      SET NOCOUNT ON;

      SELECT RecID, Status, Code, Name
      FROM Dictionary
      WHERE
      (@FilterBy = 1 AND Name LIKE '%'+@Name+'%')
      OR
      (@FilterBy = 2 AND Code = @Code)
      OR
      (@FilterBy = 3 AND @Name IS NULL AND @Code IS NULL);
      END


      Here is example on how I call this procedure:



      EXEC Search_Dictionary @FilterBy = 1, @Name = "Grant", @Code = NULL;


      I just want to prevent, if for example Filter By is 2 that should search query by Code column returns any result if user pass word Grant. In that case should return 0 records.



      Also if anyone have any suggestions on how to improve the code please let me know.










      share|improve this question















      I've implemented a stored procedure for a search process SQL Server 2008. I'm not sure if everything I created is correct. Also I'm wondering if this code has any leaks or vulnerability on SQL Injection. Here is my stored procedure:



      USE [TestDB]
      GO
      /****** Object: StoredProcedure [dbo].[Search_Dictionary] Script Date: 08/09/2018 19:17:48 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      ALTER PROCEDURE [dbo].[Search_Dictionary]
      @FilterBy int = NULL,
      @Name varchar(50) = NULL,
      @Code char(2) = NULL
      AS
      BEGIN
      SET NOCOUNT ON;

      SELECT RecID, Status, Code, Name
      FROM Dictionary
      WHERE
      (@FilterBy = 1 AND Name LIKE '%'+@Name+'%')
      OR
      (@FilterBy = 2 AND Code = @Code)
      OR
      (@FilterBy = 3 AND @Name IS NULL AND @Code IS NULL);
      END


      Here is example on how I call this procedure:



      EXEC Search_Dictionary @FilterBy = 1, @Name = "Grant", @Code = NULL;


      I just want to prevent, if for example Filter By is 2 that should search query by Code column returns any result if user pass word Grant. In that case should return 0 records.



      Also if anyone have any suggestions on how to improve the code please let me know.







      performance sql sql-server stored-procedure






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Aug 10 at 3:04









      Stephen Rauch

      3,75051530




      3,75051530










      asked Aug 9 at 23:29









      Milos

      111




      111






















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          0
          down vote













          Vulnerabilities



          There's no way this stored procedure can be used for SQL injection. The only way a stored procedure can allow SQL injection is if it allows an unsanitized user-entered string to be used to build a dynamic query using sp_executesql (see docs). You're all safe on the vulnerabilities side.



          Functionality



          The intended functionality with regards to the FilterBys is not very clear in your posts so I'll try to explain how I think they'll behave:



          Your procedure has three "modes" depending on what number is entered in to FilterBy.




          1. For FilterBy = 1, the procedure would return any rows that have the @Name parameter inside the Name column. It ignores whatever is entered in to @Code in this instance. Note, if @Name is an empty string, all records will be returned. Is that something you want?*

          2. For FilterBy = 2, the procedure returns any rows where the @Code parameter matches the Code column entirely. In this instance, if @Code is null or empty, no records are returned. Whatever is passed in as @Name is ignored – whether it's null or not.

          3. For FilterBy = 3, the procedure returns all rows, but only if @Name and @Code are null. If either of these parameters not null, no records are returned.




          * If you don't want an empty string to return all rows, change the line to something like:



          (@FilterBy = 1 AND Name LIKE '%'+@Name+'%' AND @Name <> '')



          Performance



          Performance of this query largely depends on the size of the Dictionary table (obviously), and indexes. If you can afford the disk space and slower write speed, you could add an index to Dictionary with the Code column as the first indexed field. Make sure you include RecID, Status, and Name columns somewhere in that index. This would increase performance when using FilterBy = 2.



          You could try adding another index with the Name field first, however this wouldn't do much for performance since you're using wildcards on the left side of the parameter when searching the Name field. Here's a quick little explanation of why with a nice analogy.






          share|improve this answer




























            up vote
            0
            down vote













            A user could cause a dos attack by passing in complex like filters, you might want to sanitize the @name parameter. https://www.owasp.org/index.php/Testing_for_SQL_Wildcard_Attacks_(OWASP-DS-001)






            share|improve this answer





















              Your Answer





              StackExchange.ifUsing("editor", function () {
              return StackExchange.using("mathjaxEditing", function () {
              StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
              StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
              });
              });
              }, "mathjax-editing");

              StackExchange.ifUsing("editor", function () {
              StackExchange.using("externalEditor", function () {
              StackExchange.using("snippets", function () {
              StackExchange.snippets.init();
              });
              });
              }, "code-snippets");

              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "196"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              convertImagesToLinks: false,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














               

              draft saved


              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f201338%2fstored-procedure-to-implement-a-search-filter%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes








              up vote
              0
              down vote













              Vulnerabilities



              There's no way this stored procedure can be used for SQL injection. The only way a stored procedure can allow SQL injection is if it allows an unsanitized user-entered string to be used to build a dynamic query using sp_executesql (see docs). You're all safe on the vulnerabilities side.



              Functionality



              The intended functionality with regards to the FilterBys is not very clear in your posts so I'll try to explain how I think they'll behave:



              Your procedure has three "modes" depending on what number is entered in to FilterBy.




              1. For FilterBy = 1, the procedure would return any rows that have the @Name parameter inside the Name column. It ignores whatever is entered in to @Code in this instance. Note, if @Name is an empty string, all records will be returned. Is that something you want?*

              2. For FilterBy = 2, the procedure returns any rows where the @Code parameter matches the Code column entirely. In this instance, if @Code is null or empty, no records are returned. Whatever is passed in as @Name is ignored – whether it's null or not.

              3. For FilterBy = 3, the procedure returns all rows, but only if @Name and @Code are null. If either of these parameters not null, no records are returned.




              * If you don't want an empty string to return all rows, change the line to something like:



              (@FilterBy = 1 AND Name LIKE '%'+@Name+'%' AND @Name <> '')



              Performance



              Performance of this query largely depends on the size of the Dictionary table (obviously), and indexes. If you can afford the disk space and slower write speed, you could add an index to Dictionary with the Code column as the first indexed field. Make sure you include RecID, Status, and Name columns somewhere in that index. This would increase performance when using FilterBy = 2.



              You could try adding another index with the Name field first, however this wouldn't do much for performance since you're using wildcards on the left side of the parameter when searching the Name field. Here's a quick little explanation of why with a nice analogy.






              share|improve this answer

























                up vote
                0
                down vote













                Vulnerabilities



                There's no way this stored procedure can be used for SQL injection. The only way a stored procedure can allow SQL injection is if it allows an unsanitized user-entered string to be used to build a dynamic query using sp_executesql (see docs). You're all safe on the vulnerabilities side.



                Functionality



                The intended functionality with regards to the FilterBys is not very clear in your posts so I'll try to explain how I think they'll behave:



                Your procedure has three "modes" depending on what number is entered in to FilterBy.




                1. For FilterBy = 1, the procedure would return any rows that have the @Name parameter inside the Name column. It ignores whatever is entered in to @Code in this instance. Note, if @Name is an empty string, all records will be returned. Is that something you want?*

                2. For FilterBy = 2, the procedure returns any rows where the @Code parameter matches the Code column entirely. In this instance, if @Code is null or empty, no records are returned. Whatever is passed in as @Name is ignored – whether it's null or not.

                3. For FilterBy = 3, the procedure returns all rows, but only if @Name and @Code are null. If either of these parameters not null, no records are returned.




                * If you don't want an empty string to return all rows, change the line to something like:



                (@FilterBy = 1 AND Name LIKE '%'+@Name+'%' AND @Name <> '')



                Performance



                Performance of this query largely depends on the size of the Dictionary table (obviously), and indexes. If you can afford the disk space and slower write speed, you could add an index to Dictionary with the Code column as the first indexed field. Make sure you include RecID, Status, and Name columns somewhere in that index. This would increase performance when using FilterBy = 2.



                You could try adding another index with the Name field first, however this wouldn't do much for performance since you're using wildcards on the left side of the parameter when searching the Name field. Here's a quick little explanation of why with a nice analogy.






                share|improve this answer























                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  Vulnerabilities



                  There's no way this stored procedure can be used for SQL injection. The only way a stored procedure can allow SQL injection is if it allows an unsanitized user-entered string to be used to build a dynamic query using sp_executesql (see docs). You're all safe on the vulnerabilities side.



                  Functionality



                  The intended functionality with regards to the FilterBys is not very clear in your posts so I'll try to explain how I think they'll behave:



                  Your procedure has three "modes" depending on what number is entered in to FilterBy.




                  1. For FilterBy = 1, the procedure would return any rows that have the @Name parameter inside the Name column. It ignores whatever is entered in to @Code in this instance. Note, if @Name is an empty string, all records will be returned. Is that something you want?*

                  2. For FilterBy = 2, the procedure returns any rows where the @Code parameter matches the Code column entirely. In this instance, if @Code is null or empty, no records are returned. Whatever is passed in as @Name is ignored – whether it's null or not.

                  3. For FilterBy = 3, the procedure returns all rows, but only if @Name and @Code are null. If either of these parameters not null, no records are returned.




                  * If you don't want an empty string to return all rows, change the line to something like:



                  (@FilterBy = 1 AND Name LIKE '%'+@Name+'%' AND @Name <> '')



                  Performance



                  Performance of this query largely depends on the size of the Dictionary table (obviously), and indexes. If you can afford the disk space and slower write speed, you could add an index to Dictionary with the Code column as the first indexed field. Make sure you include RecID, Status, and Name columns somewhere in that index. This would increase performance when using FilterBy = 2.



                  You could try adding another index with the Name field first, however this wouldn't do much for performance since you're using wildcards on the left side of the parameter when searching the Name field. Here's a quick little explanation of why with a nice analogy.






                  share|improve this answer












                  Vulnerabilities



                  There's no way this stored procedure can be used for SQL injection. The only way a stored procedure can allow SQL injection is if it allows an unsanitized user-entered string to be used to build a dynamic query using sp_executesql (see docs). You're all safe on the vulnerabilities side.



                  Functionality



                  The intended functionality with regards to the FilterBys is not very clear in your posts so I'll try to explain how I think they'll behave:



                  Your procedure has three "modes" depending on what number is entered in to FilterBy.




                  1. For FilterBy = 1, the procedure would return any rows that have the @Name parameter inside the Name column. It ignores whatever is entered in to @Code in this instance. Note, if @Name is an empty string, all records will be returned. Is that something you want?*

                  2. For FilterBy = 2, the procedure returns any rows where the @Code parameter matches the Code column entirely. In this instance, if @Code is null or empty, no records are returned. Whatever is passed in as @Name is ignored – whether it's null or not.

                  3. For FilterBy = 3, the procedure returns all rows, but only if @Name and @Code are null. If either of these parameters not null, no records are returned.




                  * If you don't want an empty string to return all rows, change the line to something like:



                  (@FilterBy = 1 AND Name LIKE '%'+@Name+'%' AND @Name <> '')



                  Performance



                  Performance of this query largely depends on the size of the Dictionary table (obviously), and indexes. If you can afford the disk space and slower write speed, you could add an index to Dictionary with the Code column as the first indexed field. Make sure you include RecID, Status, and Name columns somewhere in that index. This would increase performance when using FilterBy = 2.



                  You could try adding another index with the Name field first, however this wouldn't do much for performance since you're using wildcards on the left side of the parameter when searching the Name field. Here's a quick little explanation of why with a nice analogy.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Sep 17 at 14:09









                  Kris Lawton

                  1509




                  1509
























                      up vote
                      0
                      down vote













                      A user could cause a dos attack by passing in complex like filters, you might want to sanitize the @name parameter. https://www.owasp.org/index.php/Testing_for_SQL_Wildcard_Attacks_(OWASP-DS-001)






                      share|improve this answer

























                        up vote
                        0
                        down vote













                        A user could cause a dos attack by passing in complex like filters, you might want to sanitize the @name parameter. https://www.owasp.org/index.php/Testing_for_SQL_Wildcard_Attacks_(OWASP-DS-001)






                        share|improve this answer























                          up vote
                          0
                          down vote










                          up vote
                          0
                          down vote









                          A user could cause a dos attack by passing in complex like filters, you might want to sanitize the @name parameter. https://www.owasp.org/index.php/Testing_for_SQL_Wildcard_Attacks_(OWASP-DS-001)






                          share|improve this answer












                          A user could cause a dos attack by passing in complex like filters, you might want to sanitize the @name parameter. https://www.owasp.org/index.php/Testing_for_SQL_Wildcard_Attacks_(OWASP-DS-001)







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Oct 17 at 15:05









                          C.M.

                          972




                          972






























                               

                              draft saved


                              draft discarded



















































                               


                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f201338%2fstored-procedure-to-implement-a-search-filter%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Ellipse (mathématiques)

                              Quarter-circle Tiles

                              Mont Emei