Catch an SQL injection attempt on delete

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Delete.RealCases;

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

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

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Services;
using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Delete.RealCases;

/// <summary>
/// Catching an SQL injection attempt on delete, in four scenarios. A table or match column that carries SQL is refused
/// with ARX1017 SuspectedInjection before the database. SQL in the value to delete by, the classic
/// <c>' OR '1'='1</c> that would empty a table when concatenated, is a parameter: it deletes nothing.
/// </summary>
public sealed class RealCase5_CatchAnInjectionAttempt : ISample
{
    public string Name => "delete-real-5-catch-an-injection-attempt";

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

    public async Task RunAsync(SampleContext dbContext)
    {
        var accounts = new List<ArxEntity>();

        foreach (var number in new[] { "ACC-I301", "ACC-I302", "ACC-I303" })
        {
            var account = new ArxEntity("account");
            account["name"] = "Injection: keep me";
            account["account_number"] = number;
            accounts.Add(account);
        }

        var ids = await dbContext.Service.CreateMultipleAsync(accounts);

        await Scenario1_ATableNameFromTheRequestAsync(dbContext, ids[0]);

        await Scenario2_SqlInTheValueToDeleteByAsync(dbContext);

        await Scenario3_TheMatchColumnFromTheRequestAsync(dbContext);

        await Scenario4_DeleteMultipleWithABadTableAsync(dbContext, ids);
    }

    private static void SecurityLog(ArxValidationException error) => Show($"SECURITY: {error.Message}");

    /// <summary>1. <c>DELETE /api/{type}/{id}</c> with the type used as the table: the SQL in it is refused before the database.</summary>
    private static async Task Scenario1_ATableNameFromTheRequestAsync(SampleContext dbContext, ArxRecordId id)
    {
        Step("1. A table name from the request carries SQL");

        var type = "account WHERE 1=1; --";

        try
        {
            await dbContext.Service.DeleteAsync(type, id);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            SecurityLog(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number IN ('ACC-I301', 'ACC-I302', 'ACC-I303');
         * -- Expect: 3: nothing was deleted.
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. The classic: "delete the account with this number", and the user types <c>ACC-I301' OR '1'='1</c>.
    /// Concatenated into SQL it would delete every account; as a parameter it is an account number nobody has.
    /// </summary>
    private static async Task Scenario2_SqlInTheValueToDeleteByAsync(SampleContext dbContext)
    {
        Step("2. SQL in the value to delete by deletes nothing");

        var target = new ArxEntity("account");
        target["account_number"] = "ACC-I301' OR '1'='1";

        int deleted = await dbContext.Service.DeleteAsync(target, new ArxDeleteOptions { Match = ArxMatch.Key("account_number") });

        Show($"{deleted} rows deleted");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number IN ('ACC-I301', 'ACC-I302', 'ACC-I303');
         * -- Expect: 3: the text was compared as a value, so it matched no account.
         * ===========================================================
         */
    }

    /// <summary>3. The column to delete by comes from the request: the SQL in it is refused; only allow columns from a fixed list.</summary>
    private static async Task Scenario3_TheMatchColumnFromTheRequestAsync(SampleContext dbContext)
    {
        Step("3. The column to delete by, from the request, carries SQL");

        var by = "name = name OR account_number";

        var target = new ArxEntity("account");
        target[by] = "x";

        try
        {
            await dbContext.Service.DeleteAsync(target, new ArxDeleteOptions { Match = ArxMatch.Columns(by) });
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            SecurityLog(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE name = 'Injection: keep me';
         * -- Expect: 3.
         * ===========================================================
         */
    }

    /// <summary>4. DeleteMultiple of the three ids with a table name carrying SQL: refused before the database, all three kept.</summary>
    private static async Task Scenario4_DeleteMultipleWithABadTableAsync(SampleContext dbContext, IReadOnlyList<ArxRecordId> ids)
    {
        Step("4. DeleteMultiple with a table name carrying SQL");

        try
        {
            await dbContext.Service.DeleteMultipleAsync("account\"; TRUNCATE account; --", ids.ToList());
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            SecurityLog(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE name = 'Injection: keep me';
         * -- Expect: 3: every account is still there.
         * ===========================================================
         */
    }
}

  > 1. A table name from the request carries SQL
      SECURITY: ARX1017 SuspectedInjection: Entity name 'account WHERE 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.
  > 2. SQL in the value to delete by deletes nothing
      0 rows deleted
  > 3. The column to delete by, from the request, carries SQL
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'name = name OR account_number' 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.
  > 4. DeleteMultiple with a table name carrying SQL
      SECURITY: ARX1017 SuspectedInjection: Entity name 'account"; TRUNCATE 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.