[csharp] sql injection detection not working?

[indy-singh]

We are evaluating GHAS for our app sec pipeline and we can't seem to get it to flag for sql injection.

public void SubscribeTo(int systemKeyId, ThirdPartyType thirdParty, string userReference)
{

#pragma warning disable SYSLIB0021
#pragma warning disable SCS0010
// this is detected
  System.Security.Cryptography.SymmetricAlgorithm serviceProvider = new System.Security.Cryptography.DESCryptoServiceProvider(); 
#pragma warning restore SCS0010
#pragma warning restore SYSLIB0021

#pragma warning disable CS0618 // Type or member is obsolete
// none of these detected
  var adapterA = new SqlCommand("SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='" + userReference + "' ORDER BY PRICE");
  var adapterB = new SqlCommand("SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='" + userReference + "' ORDER BY PRICE", null);
  var adapterC = new SqlCommand("SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='" + userReference + "' ORDER BY PRICE", null, null);
#pragma warning restore CS0618 // Type or member is obsolete

  using (var session = _pgDatabase.OpenSession())
  using (var transaction = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
  {
// this is not detected
    session.CreateSQLQuery(@$"insert into thirdpartymonitor.subscription(user_reference, thirdparty_id, system_id) VALUES ('{userReference}', {thirdParty}, {systemKeyId}) ON CONFLICT DO NOTHING;")
      .ExecuteUpdate();

    transaction.Commit();
  }
}

I would expect the SqlCommand to be detected as per

codeql/csharp/ql/lib/ext/System.Data.SqlClient.model.yml

Line 6 in fe18e0e

- ["System.Data.SqlClient", "SqlCommand", False, "SqlCommand", "(System.String)", "", "Argument[0]", "sql-injection", "manual"]

But I'm not sure if CreateSQLQuery is detected as I don't see a sink for nhibernate.CreateSQLQuery?

I've attached my workflow file too.

codeql.yml

Cheers,
Indy

[hvitved]

Hi

The parameter userReference is not actually user controllable, so this is why we do not flag it. See https://github.com/github/codeql/tree/main/csharp/ql/test/query-tests/Security%20Features/CWE-089 for some test examples.

[indy-singh]

Hi @hvitved,

Yeah that's what I suspected was happening, but this is an internal BE app and the incoming requests are all user controllable.

Can I instruct CodeQL to taint thing everything under a namespace (e.g. DataMining.Api.*)? So that method call is populated from this api request:-

using ProtoBuf;

namespace DataMining.Api.ThirdPartyMonitor.Types.Subscribe;

[ProtoContract(IsGroup = true)]
public sealed class SubscribeRequest : Codeweavers.Communication.IApiRequest
{
    [ProtoMember(1)]
    public Codeweavers.Common.Api.Types.User User { get; set; }

    [ProtoMember(3)]
    public Codeweavers.Common.Core.Types.ThirdPartyType ThirdParty { get; set; }

    [ProtoMember(4)]
    public string UserReference { get; set; }
}

Cheers,
Indy

[indy-singh]

Kinda slowly piecing this together; by following some documentation.

So far I've managed to download the codeql database from the branch that has the test sql injection in.

I guess I need to find out how to query the namespace and then taint it somehow?

Cheers,
Indy

[indy-singh]

Not making much progress with the api namespaces yet, I've got as far as this:-

import csharp

from Class c
where c.getFile().getAbsolutePath().matches("%/src/DataMining/app/Api%")
select c.getNamespace()

In the mean, I tried another PoC:-

builder.UseEndpoints(x => x.MapGet("services/application.svc/thread-pool/{authenticationToken}", (string authenticationToken) =>
{
  System.Security.Cryptography.SymmetricAlgorithm serviceProvider = new System.Security.Cryptography.DESCryptoServiceProvider();

  var adapterA = new SqlCommand("SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='" + authenticationToken + "' ORDER BY PRICE");
  var adapterB = new SqlCommand("SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='" + authenticationToken + "' ORDER BY PRICE", null);
  var adapterC = new SqlCommand("SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='" + authenticationToken + "' ORDER BY PRICE", null, null);

  using (var session = PostgresDatabase.Instance.OpenSession())
  using (var transaction = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
  {
    session.CreateSQLQuery(@$"insert into thirdpartymonitor.subscription(user_reference, thirdparty_id, system_id) VALUES ('{authenticationToken}', 1, 1) ON CONFLICT DO NOTHING;")
      .ExecuteUpdate();

    transaction.Commit();
  }

  return application.ThreadPool();
}));

None of those are detected?