Catch an SQL injection attempt on update

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Update.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 RealCase4_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.Update.RealCases;

/// <summary>
/// Catching an SQL injection attempt on update, in four scenarios. A name that carries SQL (a field to change, the
/// column to find rows by) is refused with ARX1017 SuspectedInjection before the database. SQL in a value, the classic
/// <c>' OR '1'='1</c>, is a parameter: it matches nothing instead of every row.
/// </summary>
public sealed class RealCase4_CatchAnInjectionAttempt : ISample
{
    public string Name => "update-real-4-catch-an-injection-attempt";

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

    public async Task RunAsync(SampleContext dbContext)
    {
        var first = new ArxEntity("account");
        first["name"] = "Injection: first";
        first["account_number"] = "ACC-I201";
        first["employee_count"] = 10;

        var second = new ArxEntity("account");
        second["name"] = "Injection: second";
        second["account_number"] = "ACC-I202";
        second["employee_count"] = 10;

        var ids = await dbContext.Service.CreateMultipleAsync(new List<ArxEntity> { first, second });

        await Scenario1_AFieldNameFromTheEditFormAsync(dbContext, ids[0]);

        await Scenario2_TheFindByColumnFromTheRequestAsync(dbContext);

        await Scenario3_SqlInTheValueToFindByAsync(dbContext);

        await Scenario4_OneBadEntityInAnUpdateMultipleAsync(dbContext, ids);
    }

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

    /// <summary>1. The edit form's field names are copied into the change, and one of them carries SQL: the update is refused, the account is unchanged.</summary>
    private static async Task Scenario1_AFieldNameFromTheEditFormAsync(SampleContext dbContext, ArxRecordId id)
    {
        Step("1. A field name from the edit form carries SQL");

        var posted = new Dictionary<string, object>
        {
            ["employee_count"] = 11,
            ["name\" = 'pwned', \"employee_count"] = 0,
        };

        var change = new ArxEntity("account");
        change.RecordId = id;

        foreach (var field in posted)
        {
            change[field.Key] = field.Value;
        }

        try
        {
            await dbContext.Service.UpdateAsync(change);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            SecurityLog(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, employee_count
         * FROM account
         * WHERE account_number = 'ACC-I201';
         * -- Expect: 1 row: Injection: first, 10 (not even employee_count changed).
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. The column to find rows by comes from the request (<c>?by=…</c>). ARX refuses the SQL in it; the fix is a fixed
    /// list of the columns a request may find rows by.
    /// </summary>
    private static async Task Scenario2_TheFindByColumnFromTheRequestAsync(SampleContext dbContext)
    {
        Step("2. The column to find rows by, from the request, carries SQL");

        var by = "account_number = account_number OR 1=1 --";

        var change = new ArxEntity("account");
        change[by] = "anything";
        change["employee_count"] = 0;

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

        // The fix: only the columns you allow.
        var allowed = new HashSet<string> { "account_number" };
        Show(allowed.Contains(by) ? $"would find rows by {by}" : "the allow-list refused the column to find rows by");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE employee_count = 0;
         * -- Expect: 0: no account was changed.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. The classic: the account number to find comes from the user as <c>ACC-I202' OR '1'='1</c>. Concatenated into
    /// SQL it would change every account; as a parameter it is just an account number that doesn't exist (ARX2002).
    /// </summary>
    private static async Task Scenario3_SqlInTheValueToFindByAsync(SampleContext dbContext)
    {
        Step("3. SQL in the value to find by matches nothing");

        var change = new ArxEntity("account");
        change["account_number"] = "ACC-I202' OR '1'='1";
        change["employee_count"] = 999;

        try
        {
            await dbContext.Service.UpdateAsync(change, new ArxUpdateOptions { Match = ArxMatch.Key("account_number") });
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.RecordNotFound)
        {
            Show($"no account has that number: {error.Code}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number IN ('ACC-I201', 'ACC-I202') AND employee_count = 999;
         * -- Expect: 0: the text was compared as a value, so it matched no account (concatenated, it would have changed both).
         * ===========================================================
         */
    }

    /// <summary>4. UpdateMultiple with one entity carrying SQL in a name: the whole call is refused, the other account is unchanged too.</summary>
    private static async Task Scenario4_OneBadEntityInAnUpdateMultipleAsync(SampleContext dbContext, IReadOnlyList<ArxRecordId> ids)
    {
        Step("4. One bad entity refuses the whole UpdateMultiple");

        var good = new ArxEntity("account");
        good.RecordId = ids[0];
        good["employee_count"] = 50;

        var bad = new ArxEntity("account");
        bad.RecordId = ids[1];
        bad["employee_count; DELETE FROM account; --"] = 50;

        try
        {
            await dbContext.Service.UpdateMultipleAsync(new List<ArxEntity> { good, bad });
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            SecurityLog(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, employee_count
         * FROM account
         * WHERE account_number IN ('ACC-I201', 'ACC-I202')
         * ORDER BY account_number;
         * -- Expect: 2 rows, both with employee_count 10: the good change wasn't stored either.
         * ===========================================================
         */
    }
}

  > 1. A field name from the edit form carries SQL
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'name" = 'pwned', "employee_count' 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. The column to find rows by, from the request, carries SQL
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'account_number = account_number 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 column to find rows by
  > 3. SQL in the value to find by matches nothing
      no account has that number: RecordNotFound
  > 4. One bad entity refuses the whole UpdateMultiple
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'employee_count; DELETE FROM 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.