PostgreSQL · Update

A security check around updates: batches, allowed fields, false positives

A security check around updates (SecurityGuard, in the project root), in four scenarios: an API batch of changes passes through; an attack hidden in one item refuses the whole batch and is logged; a list of allowed fields stops unknown names before ARX; and a false positive from your own configuration is reported as a bug, not an attack, so nobody is blocked.

Set up for this page

Besides the sample itself, it uses SampleContext.cs (the dbContext every sample is given); SampleOutput.cs (Step and Show, which print); SampleContext.Application.cs (dbContext.Service, the application's ARX service); SampleCatalog.cs (the tables' ids, keys, lookups and display names); SecurityGuard.cs (the security log and strikes of the attack samples), in the files below.

Before scenario 1 the sample runs this, so the service and the rows the scenarios use are there:

Set up
var guard = new SecurityGuard(blockAfter: 3);
var accounts = new List<ArxEntity>();
foreach (var number in new[] { "ACC-G401", "ACC-G402", "ACC-G403" })
{
    var account = new ArxEntity("account");
    account["name"] = $"Guarded {number}";
    account["account_number"] = number;
    account["employee_count"] = 10;
    accounts.Add(account);
}
var ids = await service.CreateMultipleAsync(accounts);

1 A batch of changes passes through

A batch of two changes goes through the guard untouched.

Already in scope: service (ArxPgDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)

C#
var result = await PatchAccountsAsync(service, guard, "ana", new[]
{
    (ids[0], new Dictionary<string, object> { ["employee_count"] = 11 }),
    (ids[1], new Dictionary<string, object> { ["employee_count"] = 12 }),
});

Console.WriteLine($"ana: {result.Outcome}, {result.Value} accounts changed");
Used above

Defined once in this sample.

private static Task<GuardResult<int>> PatchAccountsAsync(ArxPgDbService service, SecurityGuard guard, string user, IReadOnlyList<(ArxRecordId Id, Dictionary<string, object> Fields)> items)
{
    var changes = new List<ArxEntity>();

    foreach (var (id, fields) in items)
    {
        var change = new ArxEntity("account");
        change.RecordId = id;

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

        changes.Add(change);
    }

    return guard.RunAsync(user, "patch accounts", NameSource.UserInput, () => service.UpdateMultipleAsync(changes));
}

Check it in the database

SQL

Run it in any SQL tool connected to the database, after the code has run.

SELECT account_number, employee_count
FROM account
WHERE account_number IN ('ACC-G401', 'ACC-G402')
ORDER BY account_number;

Expect: 2 rows: ACC-G401 11, ACC-G402 12.

2 An attack in one item refuses the whole batch

One item of three carries SQL in a field name: ARX refuses the whole UpdateMultiple, the guard logs it.

Already in scope: service (ArxPgDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)

C#
var result = await PatchAccountsAsync(service, guard, "mallory", new[]
{
    (ids[0], new Dictionary<string, object> { ["employee_count"] = 0 }),
    (ids[1], new Dictionary<string, object> { ["employee_count\" = 0, \"name"] = "pwned" }),
    (ids[2], new Dictionary<string, object> { ["employee_count"] = 0 }),
});

Console.WriteLine($"mallory sees: \"{result.ForTheUser}\"; log: {guard.Events[^1].Kind}");
Used above

Defined once in this sample.

private static Task<GuardResult<int>> PatchAccountsAsync(ArxPgDbService service, SecurityGuard guard, string user, IReadOnlyList<(ArxRecordId Id, Dictionary<string, object> Fields)> items)
{
    var changes = new List<ArxEntity>();

    foreach (var (id, fields) in items)
    {
        var change = new ArxEntity("account");
        change.RecordId = id;

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

        changes.Add(change);
    }

    return guard.RunAsync(user, "patch accounts", NameSource.UserInput, () => service.UpdateMultipleAsync(changes));
}

Check it in the database

SQL

Run it in any SQL tool connected to the database, after the code has run.

SELECT count(*)
FROM account
WHERE account_number IN ('ACC-G401', 'ACC-G402', 'ACC-G403') AND employee_count = 0;

Expect: 0: not even the two harmless items were stored.

3 Allowed fields stop unknown names before ARX

Defence in depth: the endpoint checks the field names against its allowed list before calling ARX. An unknown field is a client error (a 400), not a security event, and ARX never sees it.

Already in scope: service (ArxPgDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)

C#
var posted = new Dictionary<string, object> { ["credit_limit"] = 1_000_000m, ["employee_count"] = 13 };
var unknown = posted.Keys.Where(x => !AllowedFields.Contains(x)).ToList();

if (unknown.Count > 0)
{
    Console.WriteLine($"400 Bad Request: fields not allowed: {string.Join(", ", unknown)}; security log still has {guard.Events.Count} entries");
}
else
{
    await PatchAccountsAsync(service, guard, "ana", new[] { (ids[2], posted) });
}
Used above

Defined once in this sample.

private static readonly HashSet<string> AllowedFields = new(StringComparer.Ordinal) { "name", "website", "employee_count" };

private static Task<GuardResult<int>> PatchAccountsAsync(ArxPgDbService service, SecurityGuard guard, string user, IReadOnlyList<(ArxRecordId Id, Dictionary<string, object> Fields)> items)
{
    var changes = new List<ArxEntity>();

    foreach (var (id, fields) in items)
    {
        var change = new ArxEntity("account");
        change.RecordId = id;

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

        changes.Add(change);
    }

    return guard.RunAsync(user, "patch accounts", NameSource.UserInput, () => service.UpdateMultipleAsync(changes));
}

Check it in the database

SQL

Run it in any SQL tool connected to the database, after the code has run.

SELECT credit_limit, employee_count
FROM account
WHERE account_number = 'ACC-G403';

Expect: 1 row: credit_limit NULL, employee_count 10 (the request was refused before ARX).

4 A false positive from your own configuration

A false positive from your own configuration: a nightly job reads the column to reset from appsettings, and someone pasted it with a trailing ";". ARX refuses it as SQL (ARX1017), but the names came from code (Code), so the guard logs a bug, blocks nobody, and the job's owner fixes the setting.

Already in scope: service (ArxPgDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)

C#
var columnFromSettings = "website;";

foreach (var column in new[] { columnFromSettings, columnFromSettings.TrimEnd(';') })
{
    var reset = new ArxEntity("account");
    reset.RecordId = ids[2];
    reset[column] = "https://example.com/guarded";

    var result = await guard.RunAsync("nightly-job", "reset website", NameSource.Code, () => service.UpdateAsync(reset));

    Console.WriteLine($"'{column}': {result.Outcome}" + (result.Outcome == GuardOutcome.Refused ? $", logged as a {guard.Events[^1].Kind}; job blocked: {guard.IsBlocked("nightly-job")}" : ""));
}

Check it in the database

SQL

Run it in any SQL tool connected to the database, after the code has run.

SELECT website
FROM account
WHERE account_number = 'ACC-G403';

Expect: 1 row: https://example.com/guarded (written once the setting was fixed).

Run the complete sample

The code above comes from one program, built in a fresh project and run against the database. To run it yourself, here is every file and step.

The program, its files and how to run it

Run it locally

  1. .NET SDK: .NET SDK 10.0.100 or later 10.0.x (target net10.0).
  2. Create the project:
    Terminal
    dotnet new console -n ArxSample -f net10.0
    cd ArxSample
  3. Install the packages:
    Terminal
    dotnet add package ARX.SDK.PostgreSql --version 1.0.0-rc.1
  4. Database: a PostgreSQL 16 (tested with 16.15) database with the sample schema.
  5. Configuration: set ARX_SAMPLES_POSTGRESQL to its connection string. Nothing is hard-coded.
  6. Source code: replace Program.cs and add the 6 other files below, with the file names shown.
  7. Run it:
    Terminal
    dotnet run
  8. Expected output: below.

The files

Program.cs

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Update.AdvancedCases;

// 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 Advanced3_GuardAgainstAttacks().RunAsync(new SampleContext(connectionString));
Advanced3_GuardAgainstAttacks.cs

The sample, as it is in the repository.

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

namespace ArxSamples.PostgreSql.Operations.Update.AdvancedCases;

/// <summary>
/// A security check around updates (<see cref="SecurityGuard"/>, in the project root), in four scenarios: an API batch
/// of changes passes through; an attack hidden in one item refuses the whole batch and is logged; a list of allowed
/// fields stops unknown names before ARX; and a false positive from your own configuration is reported as a bug, not
/// an attack, so nobody is blocked.
/// </summary>
public sealed class Advanced3_GuardAgainstAttacks : ISample
{
    public string Name => "update-advanced-3-guard-against-attacks";

    public string Title => "A security check around updates: batches, allowed fields, false positives";

    /// <summary>The fields an API client may change. Checking them first is defence in depth: ARX would refuse SQL in a name anyway.</summary>
    private static readonly HashSet<string> AllowedFields = new(StringComparer.Ordinal) { "name", "website", "employee_count" };

    public async Task RunAsync(SampleContext dbContext)
    {
        var guard = new SecurityGuard(blockAfter: 3);

        var accounts = new List<ArxEntity>();

        foreach (var number in new[] { "ACC-G401", "ACC-G402", "ACC-G403" })
        {
            var account = new ArxEntity("account");
            account["name"] = $"Guarded {number}";
            account["account_number"] = number;
            account["employee_count"] = 10;
            accounts.Add(account);
        }

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

        await Scenario1_ABatchOfChangesPassesThroughAsync(dbContext, guard, ids);

        await Scenario2_AnAttackInOneItemRefusesTheBatchAsync(dbContext, guard, ids);

        await Scenario3_AllowedFieldsStopUnknownNamesFirstAsync(dbContext, guard, ids);

        await Scenario4_AFalsePositiveFromYourOwnConfigurationAsync(dbContext, guard, ids);
    }

    /// <summary>The "PATCH /api/accounts" endpoint: each item is an id and the fields to change, as the client sent them.</summary>
    private static Task<GuardResult<int>> PatchAccountsAsync(SampleContext dbContext, SecurityGuard guard, string user, IReadOnlyList<(ArxRecordId Id, Dictionary<string, object> Fields)> items)
    {
        var changes = new List<ArxEntity>();

        foreach (var (id, fields) in items)
        {
            var change = new ArxEntity("account");
            change.RecordId = id;

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

            changes.Add(change);
        }

        return guard.RunAsync(user, "patch accounts", NameSource.UserInput, () => dbContext.Service.UpdateMultipleAsync(changes));
    }

    /// <summary>1. A batch of two changes goes through the guard untouched.</summary>
    private static async Task Scenario1_ABatchOfChangesPassesThroughAsync(SampleContext dbContext, SecurityGuard guard, IReadOnlyList<ArxRecordId> ids)
    {
        Step("1. A batch of changes passes through");

        var result = await PatchAccountsAsync(dbContext, guard, "ana", new[]
        {
            (ids[0], new Dictionary<string, object> { ["employee_count"] = 11 }),
            (ids[1], new Dictionary<string, object> { ["employee_count"] = 12 }),
        });

        Show($"ana: {result.Outcome}, {result.Value} accounts changed");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, employee_count
         * FROM account
         * WHERE account_number IN ('ACC-G401', 'ACC-G402')
         * ORDER BY account_number;
         * -- Expect: 2 rows: ACC-G401 11, ACC-G402 12.
         * ===========================================================
         */
    }

    /// <summary>2. One item of three carries SQL in a field name: ARX refuses the whole UpdateMultiple, the guard logs it.</summary>
    private static async Task Scenario2_AnAttackInOneItemRefusesTheBatchAsync(SampleContext dbContext, SecurityGuard guard, IReadOnlyList<ArxRecordId> ids)
    {
        Step("2. An attack in one item refuses the whole batch");

        var result = await PatchAccountsAsync(dbContext, guard, "mallory", new[]
        {
            (ids[0], new Dictionary<string, object> { ["employee_count"] = 0 }),
            (ids[1], new Dictionary<string, object> { ["employee_count\" = 0, \"name"] = "pwned" }),
            (ids[2], new Dictionary<string, object> { ["employee_count"] = 0 }),
        });

        Show($"mallory sees: \"{result.ForTheUser}\"; log: {guard.Events[^1].Kind}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number IN ('ACC-G401', 'ACC-G402', 'ACC-G403') AND employee_count = 0;
         * -- Expect: 0: not even the two harmless items were stored.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. Defence in depth: the endpoint checks the field names against its allowed list before calling ARX. An unknown
    /// field is a client error (a 400), not a security event, and ARX never sees it.
    /// </summary>
    private static async Task Scenario3_AllowedFieldsStopUnknownNamesFirstAsync(SampleContext dbContext, SecurityGuard guard, IReadOnlyList<ArxRecordId> ids)
    {
        Step("3. Allowed fields stop unknown names before ARX");

        var posted = new Dictionary<string, object> { ["credit_limit"] = 1_000_000m, ["employee_count"] = 13 };
        var unknown = posted.Keys.Where(x => !AllowedFields.Contains(x)).ToList();

        if (unknown.Count > 0)
        {
            Show($"400 Bad Request: fields not allowed: {string.Join(", ", unknown)}; security log still has {guard.Events.Count} entries");
        }
        else
        {
            await PatchAccountsAsync(dbContext, guard, "ana", new[] { (ids[2], posted) });
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT credit_limit, employee_count
         * FROM account
         * WHERE account_number = 'ACC-G403';
         * -- Expect: 1 row: credit_limit NULL, employee_count 10 (the request was refused before ARX).
         * ===========================================================
         */
    }

    /// <summary>
    /// 4. A false positive from your own configuration: a nightly job reads the column to reset from appsettings, and
    /// someone pasted it with a trailing ";". ARX refuses it as SQL (ARX1017), but the names came from code
    /// (<see cref="NameSource.Code"/>), so the guard logs a bug, blocks nobody, and the job's owner fixes the setting.
    /// </summary>
    private static async Task Scenario4_AFalsePositiveFromYourOwnConfigurationAsync(SampleContext dbContext, SecurityGuard guard, IReadOnlyList<ArxRecordId> ids)
    {
        Step("4. A false positive from your own configuration");

        var columnFromSettings = "website;";

        foreach (var column in new[] { columnFromSettings, columnFromSettings.TrimEnd(';') })
        {
            var reset = new ArxEntity("account");
            reset.RecordId = ids[2];
            reset[column] = "https://example.com/guarded";

            var result = await guard.RunAsync("nightly-job", "reset website", NameSource.Code, () => dbContext.Service.UpdateAsync(reset));

            Show($"'{column}': {result.Outcome}" + (result.Outcome == GuardOutcome.Refused ? $", logged as a {guard.Events[^1].Kind}; job blocked: {guard.IsBlocked("nightly-job")}" : ""));
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT website
         * FROM account
         * WHERE account_number = 'ACC-G403';
         * -- Expect: 1 row: https://example.com/guarded (written once the setting was fixed).
         * ===========================================================
         */
    }
}
SampleContext.cs

What every sample is given: the connection string (and the ISample interface the samples implement).

namespace ArxSamples.PostgreSql;

/// <summary>One documentation sample: a scenario a real application has, shown end to end.</summary>
public interface ISample
{
    /// <summary>The name to run it alone: <c>dotnet run -- &lt;name&gt;</c>.</summary>
    string Name { get; }

    /// <summary>One line on what it shows.</summary>
    string Title { get; }

    Task RunAsync(SampleContext dbContext);
}

/// <summary>
/// What a sample is given. Every sample gets the connection string; the application's service and catalog
/// (SampleContext.Application.cs) and the plain Npgsql read-back helper (SampleContext.Direct.cs) live in their own
/// files, so a sample needs only the files it uses.
/// </summary>
public sealed partial class SampleContext
{
    public SampleContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }
}
SampleOutput.cs

How the samples print: Step for each scenario, Show for what it did, ShowError for an error caught on purpose.

using ARX.SDK;

namespace ArxSamples.PostgreSql;

/// <summary>How the samples print: a numbered step for each scenario, then what it did.</summary>
public static class SampleOutput
{
    /// <summary>The start of a scenario: <c>Step("1. By the id Create returned")</c>.</summary>
    public static void Step(string text) => Console.WriteLine($"  > {text}");

    /// <summary>What the scenario did.</summary>
    public static void Show(string text) => Console.WriteLine($"      {text}");

    /// <summary>An ARX error the scenario caught on purpose.</summary>
    public static void ShowError(ArxException exception)
        => Console.WriteLine($"      caught {exception.GetType().Name}: {exception.Message}");
}
SampleContext.Application.cs

The application's ARX service and its schema catalog, created once and shared by the samples.

using ARX.SDK.PostgreSql.Services;
using ARX.SDK.Services;

namespace ArxSamples.PostgreSql;

/// <summary>The application's ARX service and its schema catalog, shared by the real and advanced samples.</summary>
public sealed partial class SampleContext
{
    private ArxSchemaCatalog? _catalog;
    private ArxPgDbService? _service;

    /// <summary>The application's schema catalog: table ids, keys and selection sets (SampleCatalog.cs).</summary>
    public ArxSchemaCatalog Catalog => _catalog ??= SampleCatalog.Build();

    /// <summary>
    /// One service for the whole application. It holds no connection: every call opens one from the Npgsql pool and
    /// closes it, unless you pass your own connection or transaction.
    /// </summary>
    public ArxPgDbService Service => _service ??= new ArxPgDbService(ConnectionString, Catalog);
}
SampleCatalog.cs

The application's schema catalog: every table's id column and keys, and its selection sets.

using ARX.SDK.Services;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace ArxSamples.PostgreSql;

/// <summary>An account's industry. Stored in account.industry (smallint) as the enum's number.</summary>
public enum Industry : short
{
    Retail = 1,
    Manufacturing = 2,
    Healthcare = 3,
    Finance = 4,
    Technology = 5
}

/// <summary>
/// The application's schema catalog: every table's id column and keys, and every picklist (selection set),
/// defined once.
/// </summary>
/// <remarks>
/// <para>
/// <b>Ids.</b> Every table the application writes is declared with its one id column: its name, its .NET type, and
/// whether the database generates it (<c>ArxIdColumn.Generated</c>: identity, sequence, default) or the caller supplies
/// it (<c>ArxIdColumn.Supplied</c>: a natural id, a client-generated Guid). A table with no id column, such as a log or a
/// table identified by several columns together, is declared <c>ArxIdColumn.NoIdColumn</c>. ARX never reads ids from
/// the database: the developer knows the schema, and an undeclared table is refused (ARX1016).
/// </para>
/// <para>
/// <b>Keys.</b> Optional business keys: one or more columns whose values identify a record, such as an account number,
/// or a region and a SKU together. Create doesn't use them (the database's unique constraints do the enforcing); Update
/// and Delete will be able to find rows by them. <c>ValidateSchemaAsync</c> checks ids and keys against the database
/// when you ask it to.
/// </para>
/// <para>
/// <b>Selection sets.</b> /// A selection set (picklist, option set) is a column that only accepts a fixed list of codes, each with a display
/// text: an account's status (ACTIVE, ON_HOLD, CLOSED), a contact's preferred channel (E, P, S). The catalog tells ARX,
/// for each set:
/// </para>
/// <list type="bullet">
/// <item><b>Name</b>: what an <see cref="ArxSelectionSetValue"/> refers to, for example <c>new ArxSelectionSetValue("AccountStatus", "ON_HOLD")</c>.</item>
/// <item><b>Kind</b>: what the codes are, matching the column: Integer, String, Character, Boolean, Decimal, Guid, or a
/// PostgreSQL enum type (DatabaseEnum).</item>
/// <item><b>Multiplicity</b>: Single (one code), or several codes stored as an array (Array), bit flags (Flags), a
/// delimited string (Delimited) or a JSON array (Json).</item>
/// <item><b>Options</b>: each code with its display text and order. A retired option (<c>IsActive: false</c>) stays
/// readable in old rows, but can't be written any more.</item>
/// </list>
/// <para>
/// Build the catalog once at startup and give it to the service: <c>new ArxPgDbService(connectionString, SampleCatalog.Build())</c>.
/// ARX checks every <see cref="ArxSelectionSetValue"/> against it in memory, before any SQL is sent, and stores the code
/// itself in the column: no lookup table, no extra query. A plain value (a string or number that isn't an
/// ArxSelectionSetValue) isn't checked.
/// </para>
/// <para>
/// The catalog is plain code, so the options can come from wherever you keep them: written out here, taken from a .NET
/// enum (<c>AddEnum</c>), loaded from JSON (<see cref="SetsJson"/> and <see cref="FromJson"/> below), or loaded at
/// startup from your own option table and passed to <c>new ArxSelectionSetDefinition(name, kind, options)</c>. ARX
/// never reads option tables or files itself.
/// </para>
/// </remarks>
public static class SampleCatalog
{
    /// <summary>
    /// Selection sets kept as JSON, as an application might keep them in a file, a settings store or a database column.
    /// Here the JSON is in the class itself; in an application, read it from wherever you keep it
    /// (<c>File.ReadAllText("selection-sets.json")</c>, configuration, an HTTP call) and pass it to <see cref="FromJson"/>.
    /// "multiplicity" and "active" are optional (Single and true).
    /// </summary>
    public const string SetsJson = """
        [
          {
            "name": "LeadSource",
            "kind": "String",
            "options": [
              { "value": "WEB",       "text": "Website",    "order": 0 },
              { "value": "REFERRAL",  "text": "Referral",   "order": 1 },
              { "value": "EVENT",     "text": "Trade show", "order": 2 },
              { "value": "COLD_CALL", "text": "Cold call",  "order": 3, "active": false }
            ]
          },
          {
            "name": "ContactRating",
            "kind": "Integer",
            "multiplicity": "Single",
            "options": [
              { "value": 1, "text": "Poor" },
              { "value": 2, "text": "Fair" },
              { "value": 3, "text": "Good" },
              { "value": 4, "text": "Very good" },
              { "value": 5, "text": "Excellent" }
            ]
          }
        ]
        """;

    public static ArxSchemaCatalog Build()
    {
        var builder = ArxSchemaCatalog.Create()

            // ---------- tables: id column and keys ----------
            // Generated bigint ids (bigserial): the database fills them in. The keys are the unique columns that also
            // identify a record.
            .Table("account", ArxIdColumn.Generated<long>("id")).Key("account", "account_number")
            .Table("contact", ArxIdColumn.Generated<long>("id")).Key("contact", "email")

            // Retrieve: a contact's account_id reads back as a reference to its account, named by the account's
            // name; a contact is named by its first and last names.
            .Lookup("contact", "account_id", "account")
            .DisplayName("account", "name")
            .DisplayName("contact", new[] { "first_name", "last_name" }, " ")

            .Table("product_category", ArxIdColumn.Generated<long>("id")).Key("product_category", "name")
            .Table("product", ArxIdColumn.Generated<long>("id")).Key("product", "sku")
            .Table("sales_order", ArxIdColumn.Generated<long>("id")).Key("sales_order", "order_number")
            .Table("order_line", ArxIdColumn.Generated<long>("id"))
            .Table("data_type_showcase", ArxIdColumn.Generated<long>("id"))

            // A natural id: the caller gives the ISO country code.
            .Table("country", ArxIdColumn.Supplied<string>("code"))

            // A generated uuid id (DEFAULT gen_random_uuid()).
            .Table("device", ArxIdColumn.Generated<Guid>("id")).Key("device", "serial_number")

            // No id column: a price is identified by its region and SKU together, a compound key.
            .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")

            // No primary key at all: the same action can be logged twice.
            .Table("activity_log", ArxIdColumn.NoIdColumn)

            // A partitioned table without an id: a visit is a fact, never updated (Retrieve Advanced8).
            .Table("web_visit", ArxIdColumn.NoIdColumn)

            // ---------- selection sets ----------

            // From a .NET enum: the set is named after the enum ("Industry"), the codes are its numbers (Retail = 1, …),
            // the display texts are its member names. Column: account.industry smallint.
            .AddEnum<Industry>()

            // Written out: String codes with display texts, single choice. Column: account.status text.
            .Add("AccountStatus", ArxSelectionSetKind.String, ("ACTIVE", "Active"), ("ON_HOLD", "On hold"), ("CLOSED", "Closed"))

            // Multiple choice, stored as an array of codes. RESELLER is retired: rows that have it still read back,
            // but new writes can't use it. Column: account.segments text[].
            .Add(new ArxSelectionSetDefinition("CustomerSegment", ArxSelectionSetKind.String, new[]
            {
                new ArxSelectionSetOption("SMB", "Small business", 0),
                new ArxSelectionSetOption("ENTERPRISE", "Enterprise", 1),
                new ArxSelectionSetOption("PUBLIC", "Public sector", 2),
                new ArxSelectionSetOption("RESELLER", "Reseller", 3, IsActive: false)
            }, ArxSelectionSetMultiplicity.Array))

            // Single-character codes. Column: contact.preferred_channel char(1).
            .Add("ContactChannel", ArxSelectionSetKind.Character, ('E', "Email"), ('P', "Phone"), ('S', "Text message"))

            // The labels of a PostgreSQL enum type: the value is written with a cast to that type.
            // Column: data_type_showcase.mood showcase_mood.
            .Add(new ArxSelectionSetDefinition("Mood", ArxSelectionSetKind.DatabaseEnum, new[]
            {
                new ArxSelectionSetOption("happy", "Happy", 0),
                new ArxSelectionSetOption("neutral", "Neutral", 1),
                new ArxSelectionSetOption("sad", "Sad", 2)
            }, databaseTypeName: "showcase_mood"));

        // Loaded from JSON: LeadSource (contact.lead_source text) and ContactRating (contact.rating smallint).
        foreach (var set in FromJson(SetsJson))
        {
            builder.Add(set);
        }

        return builder.Build();
    }

    /// <summary>Reads selection set definitions from JSON (the shape of <see cref="SetsJson"/>).</summary>
    public static IEnumerable<ArxSelectionSetDefinition> FromJson(string json)
    {
        var options = new JsonSerializerOptions
        {
            PropertyNameCaseInsensitive = true,
            Converters = { new JsonStringEnumConverter() }   // "String", "Integer", "Array", … as names
        };

        var sets = JsonSerializer.Deserialize<List<JsonSet>>(json, options)
            ?? throw new InvalidOperationException("The selection set JSON is empty.");

        foreach (var set in sets)
        {
            yield return new ArxSelectionSetDefinition(
                set.Name,
                set.Kind,
                set.Options.Select(x => new ArxSelectionSetOption(Code(x.Value, set.Kind), x.Text, x.Order, x.Active)),
                set.Multiplicity,
                set.DatabaseTypeName);
        }
    }

    /// <summary>A JSON value as a code of the set's kind: JSON has no char, Guid or 64-bit integer of its own.</summary>
    private static object Code(JsonElement value, ArxSelectionSetKind kind) => kind switch
    {
        ArxSelectionSetKind.Integer => value.GetInt64(),
        ArxSelectionSetKind.Decimal => value.GetDecimal(),
        ArxSelectionSetKind.Boolean => value.GetBoolean(),
        ArxSelectionSetKind.Guid => value.GetGuid(),
        ArxSelectionSetKind.Character => value.GetString()![0],
        _ => value.GetString()!   // String and DatabaseEnum
    };

    private sealed record JsonSet(
        string Name,
        ArxSelectionSetKind Kind,
        List<JsonOption> Options,
        ArxSelectionSetMultiplicity Multiplicity = ArxSelectionSetMultiplicity.Single,
        string? DatabaseTypeName = null);

    private sealed record JsonOption(JsonElement Value, string Text, int Order = 0, bool Active = true);
}
SecurityGuard.cs

A security check around ARX calls: suspected injection logged as a security event, strikes, blocking.

using ARX.SDK;

namespace ArxSamples.PostgreSql;

/// <summary>Where the names (tables, attributes, match columns) in a call come from. It decides what an ARX1017 means.</summary>
public enum NameSource
{
    /// <summary>A name built from what a user sent (a form field, a file header, a query string): ARX1017 is a suspected attack.</summary>
    UserInput,

    /// <summary>A name from your code or configuration: ARX1017 is a bug in your code, not an attack. Nobody is blocked.</summary>
    Code,
}

/// <summary>What the guard did with a call.</summary>
public enum GuardOutcome
{
    /// <summary>The call ran.</summary>
    Done,

    /// <summary>ARX refused the call with ARX1017; the event is logged and the user got a plain message.</summary>
    Refused,

    /// <summary>The user was already blocked: the call wasn't attempted.</summary>
    Blocked,
}

/// <summary>One entry of the security log: a suspected attack, or a bug when the names came from code.</summary>
public sealed record SecurityEvent(DateTimeOffset At, string User, string Action, string Kind, string Detail);

/// <summary>The outcome, the call's result when it ran, and the message to show the user (never the detection details).</summary>
public sealed record GuardResult<T>(GuardOutcome Outcome, T? Value, string ForTheUser);

/// <summary>
/// A security check around ARX calls: the pattern to copy into an application, kept small so it can be read in one
/// go. It lets every ARX error through except <c>ARX1017 SuspectedInjection</c>, which it turns into:
/// <list type="bullet">
/// <item>an entry in the security log (<see cref="Events"/>), with the user, the action and ARX's message (the name is
/// escaped and cut to 64 characters by ARX, so it is safe to log);</item>
/// <item>a plain message for the user that doesn't say what was detected;</item>
/// <item>a strike against the user. After <c>blockAfter</c> strikes the user is blocked and their calls aren't attempted.
/// One strike never blocks anyone, so a single false positive only costs one refused request;</item>
/// <item>no strike when the names came from your own code (<see cref="NameSource.Code"/>): that's a bug to fix, logged
/// as one.</item>
/// </list>
/// A false positive (a harmless name such as a file header <c>E-mail (work)</c> flagged as SQL) is handled by triage:
/// read the event, fix the mapping that let the name through, and <see cref="Unblock"/> the user if needed.
/// In a real application the log goes to your SIEM or security monitoring and the strikes live in a shared store
/// (Redis, the database), not in memory.
/// </summary>
public sealed class SecurityGuard
{
    private const string ATTACK = "suspected attack";
    private const string BUG = "bug";

    private readonly object _gate = new();
    private readonly int _blockAfter;
    private readonly Dictionary<string, int> _strikes = new(StringComparer.Ordinal);
    private readonly List<SecurityEvent> _events = new();

    public SecurityGuard(int blockAfter = 3)
    {
        _blockAfter = blockAfter;
    }

    /// <summary>The security log, oldest first.</summary>
    public IReadOnlyList<SecurityEvent> Events
    {
        get
        {
            lock (_gate)
            {
                return _events.ToArray();
            }
        }
    }

    public bool IsBlocked(string user)
    {
        lock (_gate)
        {
            return _strikes.TryGetValue(user, out var strikes) && strikes >= _blockAfter;
        }
    }

    /// <summary>After triage found the events were a false positive: clears the user's strikes (the log is kept).</summary>
    public void Unblock(string user)
    {
        lock (_gate)
        {
            _strikes.Remove(user);
        }
    }

    /// <summary>Runs <paramref name="call"/> for <paramref name="user"/> unless they are blocked, and handles ARX1017 as described above.</summary>
    public async Task<GuardResult<T>> RunAsync<T>(string user, string action, NameSource names, Func<Task<T>> call)
    {
        if (IsBlocked(user))
        {
            return new GuardResult<T>(GuardOutcome.Blocked, default, "Your access is suspended. Contact support.");
        }

        try
        {
            return new GuardResult<T>(GuardOutcome.Done, await call(), "Done.");
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            lock (_gate)
            {
                if (names == NameSource.Code)
                {
                    _events.Add(new SecurityEvent(DateTimeOffset.UtcNow, user, action, BUG, error.Message));
                    return new GuardResult<T>(GuardOutcome.Refused, default, "Something went wrong on our side. It has been reported.");
                }

                _strikes[user] = _strikes.GetValueOrDefault(user) + 1;
                _events.Add(new SecurityEvent(DateTimeOffset.UtcNow, user, action, ATTACK, error.Message));
            }

            return new GuardResult<T>(GuardOutcome.Refused, default, "The request couldn't be processed.");
        }
    }
}

Expected output

What dotnet run printed when the sample was verified, on a freshly created sample schema. Ids differ when the tables already hold rows.

Output
  > 1. A batch of changes passes through
      ana: Done, 2 accounts changed
  > 2. An attack in one item refuses the whole batch
      mallory sees: "The request couldn't be processed."; log: suspected attack
  > 3. Allowed fields stop unknown names before ARX
      400 Bad Request: fields not allowed: credit_limit; security log still has 1 entries
  > 4. A false positive from your own configuration
      'website;': Refused, logged as a bug; job blocked: False
      'website': Done