Is there a way to identify dynamic sql that may be vulnerable to sql injection?

  • Is there a way to identify if there is any dynamic sql in use in a database that may be vulnerable to sql injection?

  • Anything that concatenates user input into a string and executes the resulting string is vulnerable. That includes user input that got stored in the database and then used to build a string.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes but we support a number of databases and dont have time to check every query so would like someone way to check the databases for vulnerabilities

  • The easiest (and safest) is to assume that all dynamic SQL is vulnerable, unless proven otherwise.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • A starting point may be to query stored procedures for the existence of sp_executeSQL.

    ...

  • HappyGeek - Wednesday, January 3, 2018 6:30 AM

    A starting point may be to query stored procedures for the existence of sp_executeSQL.

    A lot of people, however, tend to use EXEC(@SQL) which'll be missed.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Flat out, if you're not parameterizing the queries in some method, through stored procedures, through the code, or through parameters defined in sp_executesql, you are almost absolutely at risk of SQL Injection attacks. Period. Full stop. Doesn't matter if it's one query or one million. You're at risk if you're not using methods that ensure only appropriate data can be sent into queries in your system. The way to ensure that is using parameters. That's it.

    Now, none of this says you MUST use stored procedures (I hate that argument). However, just generating dynamic T-SQL, using pretty much any method, can be vulnerable unless that T-SQL is parameterized. You can see in the code examples that I link to, you're not limited to stored procs. However, you have to write the code correctly, or your business is vulnerable.

    To quote my kids, it's current year. No one should be writing code any longer that isn't dealing appropriately with this issue.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • juniorDBA13 - Wednesday, January 3, 2018 6:04 AM

    Yes but we support a number of databases and dont have time to check every query so would like someone way to check the databases for vulnerabilities

    A little tough love coming up here... If you don't take the time to check every instance of dynamic SQL for inject-ability, then plan on spending some time explaining how your company suffered a successful attack.  You CAN'T afford to not take the time.  Tell the company to stop making excuses and do it NOW!

    Then implement a rigorous process that prevents unreviewed code from being deployed even to your staging environments.  Again, no excuses... just do it!

    I'll also tell you that you need to do the same thing for your front end code.  At the very least, hire a 3rd party to do penetration testing of your public facing applications and by "public facing", I mean any app outside of IT (we even test the non-public facing stuff).

    This is something you don't want to screw with or let get balled up in stupid politics by managers that don't know any better.  If they think it's expensive to do all of this, wait until they find out the true cost of a successful attack on your systems.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • juniorDBA13 - Wednesday, January 3, 2018 6:04 AM

    Yes but we support a number of databases and dont have time to check every query so would like someone way to check the databases for vulnerabilities

    I should point out that the hacking attack that took Sony down a few years ago started with SQL injection and ended with a complete compromise of their entire network.
    The Equifax data breach - SQL injection
    etc, etc, etc, http://codecurmudgeon.com/wp/sql-injection-hall-of-shame/

    Maybe ask the company's risk officers whether it's worth the time not to join that list.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thom A - Wednesday, January 3, 2018 6:33 AM

    HappyGeek - Wednesday, January 3, 2018 6:30 AM

    A starting point may be to query stored procedures for the existence of sp_executeSQL.

    A lot of people, however, tend to use EXEC(@SQL) which'll be missed.

    Tom you are of course correct, it did occur to me, I hoped the OP would have picked up on that too, it was offered purely as a starting point.

    ...

  • I understand all of this. 

    My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem

  • juniorDBA13 - Wednesday, January 3, 2018 7:05 AM

    I understand all of this. 

    My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem

    Are those only select queries or are there also Insert/Update/Delete queries? The reason for asking is that you can use the sys.dm_exec_describe_first_result_set function to check if there is a result set if only select queries are allowed. I did post this on one of the forums a while back.
    😎

  • juniorDBA13 - Wednesday, January 3, 2018 7:05 AM

    I understand all of this. 

    My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem

    You'll need to check any code that uses dynamic SQL, like the others said; that could be an SP on your SQL server, a trigger, etc, and even your application code.

    I suppose a better question, from us might be, do you know how to determine that a piece of SQL could suffer injection? For example, consider the following 2 (fake) SPs that are intended to do the same thing; do you understand why one would suffer from injection and the other would not?

    CREATE PROC AllTableData1 @TableName varchar(8000), @CustomerName varchar(100) = NULL AS
     
      DECLARE @SQL nvarchar(MAX) = 'SELECT * FROM ' + @TableName +
                                    CASE WHEN @CustomerName IS NOT NULL THEN ' WHERE CustomerName = ''' + @CustomerName +'''' ELSE '' END + ';';

      EXEC (@SQL);
    GO

    CREATE PROC AllTableData2 @TableName varchar(8000), @CustomerName varchar(100) = NULL AS
     
      DECLARE @SQL nvarchar(MAX)

      SELECT @SQL = N'
      SELECT *
      FROM ' + QUOTENAME([name])
      FROM sys.Tables
      WHERE [name] = @TableName;

      IF @CustomerName IS NULL BEGIN
       SET @SQL = @SQL + N';';
       EXEC sp_executesql @SQL;
      END ELSE BEGIN
       SET @SQL = @SQL + N'
      WHERE CustomerName = @dCustomerName;';
       DECLARE @Param nvarchar(MAX) = N'@dCustomerName varchar(100)';
       EXEC sp_executesql @SQL, @param, @dCustomerName = @CustomerName;
      END
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • juniorDBA13 - Wednesday, January 3, 2018 7:05 AM

    I understand all of this. 

    My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem

    Information_schema.routines view can be queried to give you definitions for procedures and functions but not UDF's. Look this up if you are not sure.

    ...

  • juniorDBA13 - Wednesday, January 3, 2018 7:05 AM

    My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem

    Check any dynamic SQL, in the DBs or in the application. You have to start with the assumption that any dynamic SQL is vulnerable and work from there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply