Catch an SQL injection attempt on a search

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new RealCase9_CatchAnInjectionAttempt().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// Catching an SQL injection attempt on a search, in four scenarios. The sort field and the filter field of a search
/// screen come from the request: SQL in them is ARX1017 SuspectedInjection, refused before the database. A badly
/// formed name is ARX1003, a well-formed misspelling ARX5002. SQL in a search value, the classic <c>' OR '1'='1</c>, is a parameter: it matches nothing.
/// </summary>
public sealed class RealCase9_CatchAnInjectionAttempt : ISample
{
    public string Name => "retrieve-multiple-real-9-catch-an-injection-attempt";

    public string Title => "Catch an SQL injection attempt on a search";

    public async Task RunAsync(SampleContext dbContext)
    {
        foreach (var number in new[] { "ACC-R6A1", "ACC-R6A2" })
        {
            var account = new ArxEntity("account");
            account["name"] = "Injection search " + number;
            account["account_number"] = number;
            await dbContext.Service.CreateAsync(account);
        }

        await Scenario1_ASortFieldFromTheRequestAsync(dbContext);

        await Scenario2_AFilterFieldFromTheRequestAsync(dbContext);

        await Scenario3_SqlInASearchValueAsync(dbContext);

        await Scenario4_ATypoIsNotAnAttackAsync(dbContext);
    }

    private static void Report(ArxValidationException error)
        => Show(error.Code == ArxErrorCode.SuspectedInjection ? $"SECURITY: {error.Message}" : $"bug, not an attack: {error.Message}");

    /// <summary>1. <c>?sort=name; DROP TABLE account; --</c> used as the order column.</summary>
    private static async Task Scenario1_ASortFieldFromTheRequestAsync(SampleContext dbContext)
    {
        Step("1. A sort field from the request carries SQL");

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumn("name");
        query.AddOrder("name; DROP TABLE account; --", ArxOrderType.Ascending);

        try
        {
            await dbContext.Service.RetrieveMultipleAsync(query);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            Report(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT CASE WHEN OBJECT_ID('dbo.account') IS NOT NULL THEN 1 ELSE 0 END AS account_exists;
         * -- Expect: 1.
         * ===========================================================
         */
    }

    /// <summary>2. <c>?field=name) OR (1=1</c> used as the condition's column.</summary>
    private static async Task Scenario2_AFilterFieldFromTheRequestAsync(SampleContext dbContext)
    {
        Step("2. A filter field from the request carries SQL");

        var query = new ArxQueryExpression("account");
        query.Criteria.AddCondition("name) OR (1=1", ArxConditionOperator.Equal, "x");

        try
        {
            await dbContext.Service.RetrieveMultipleAsync(query);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            Report(error);
        }

        // The fix: the fields a search may filter or sort by, from a fixed list.
        var searchable = new HashSet<string> { "name", "account_number", "status" };
        Show(searchable.Contains("name) OR (1=1") ? "would search" : "the allow-list refused the field");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number LIKE 'ACC-R6A%';
         * -- Expect: 2: the accounts are there; the refused search read none of them.
         * ===========================================================
         */
    }

    /// <summary>3. The classic in a value: concatenated it would return every account; as a parameter it is a name nobody has.</summary>
    private static async Task Scenario3_SqlInASearchValueAsync(SampleContext dbContext)
    {
        Step("3. SQL in the search value matches nothing");

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumn("name");
        query.Criteria.AddCondition("name", ArxConditionOperator.Equal, "x' OR '1'='1");

        var rows = await dbContext.Service.RetrieveMultipleAsync(query);
        Show($"{rows.Entities.Count} accounts found");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE name = 'x'' OR ''1''=''1';
         * -- Expect: 0.
         * ===========================================================
         */
    }

    /// <summary>4. A badly formed field name ("account number", with a space) is ARX1003: a bug to fix, not an attack. A well-formed misspelling reaches the database and is ARX5002.</summary>
    private static async Task Scenario4_ATypoIsNotAnAttackAsync(SampleContext dbContext)
    {
        Step("4. A badly formed name is ARX1003, not ARX1017");

        var query = new ArxQueryExpression("account");
        query.AddOrder("account number", ArxOrderType.Ascending);

        try
        {
            await dbContext.Service.RetrieveMultipleAsync(query);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidIdentifier)
        {
            Report(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM information_schema.columns
         * WHERE table_name = 'account' AND column_name = 'account_number';
         * -- Expect: 1: the column is account_number, with an underscore.
         * ===========================================================
         */
    }
}

  > 1. A sort field from the request carries SQL
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'name; DROP TABLE account; --' looks like an SQL injection attempt (contains ';'). ARX refused it before sending anything to the database. Names must come from your code or a fixed list, never from user input.
  > 2. A filter field from the request carries SQL
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'name) OR (1=1' looks like an SQL injection attempt (contains '('). ARX refused it before sending anything to the database. Names must come from your code or a fixed list, never from user input.
      the allow-list refused the field
  > 3. SQL in the search value matches nothing
      0 accounts found
  > 4. A badly formed name is ARX1003, not ARX1017
      bug, not an attack: ARX1003 InvalidIdentifier: Attribute name 'account number' is not a valid identifier. Use ASCII letters, digits and underscores, starting with a letter or underscore, at most 128 characters. A column named otherwise is used through an alias declared in the schema catalog: .Alias(table, attribute, databaseName).