MySQL · 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:
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 (ArxMySqlDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)
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");Defined once in this sample.
private static Task<GuardResult<int>> PatchAccountsAsync(ArxMySqlDbService 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
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 (ArxMySqlDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)
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}");Defined once in this sample.
private static Task<GuardResult<int>> PatchAccountsAsync(ArxMySqlDbService 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
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 (ArxMySqlDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)
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) });
}Defined once in this sample.
private static readonly HashSet<string> AllowedFields = new(StringComparer.Ordinal) { "name", "website", "employee_count" };
private static Task<GuardResult<int>> PatchAccountsAsync(ArxMySqlDbService 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
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 (ArxMySqlDbService), guard (SecurityGuard), ids (IReadOnlyList<ArxRecordId>)
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
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
- .NET SDK: .NET SDK 10.0.100 or later 10.0.x (target
net10.0). - Create the project:
Terminal dotnet new console -n ArxSample -f net10.0 cd ArxSample - Install the packages:
Terminal dotnet add package ARX.SDK.MySQL --version 1.0.0-rc.1 - Database: a MySQL 8.4 (tested with 8.4.13) database with the sample schema.
- Configuration: set
ARX_SAMPLES_MYSQLto its connection string. Nothing is hard-coded. - Source code: replace
Program.csand add the 6 other files below, with the file names shown. - Run it:
Terminal dotnet run - Expected output: below.
The files
Runs the sample. Generated for this page; it reads the connection string from the environment.
using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Update.AdvancedCases;
// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_MYSQL")
?? throw new InvalidOperationException("Set the ARX_SAMPLES_MYSQL environment variable to your MySQL connection string.");
await new Advanced3_GuardAgainstAttacks().RunAsync(new SampleContext(connectionString));
The sample, as it is in the repository.
using ARX.SDK.Services;
using static ArxSamples.MySQL.SampleOutput;
namespace ArxSamples.MySQL.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).
* ===========================================================
*/
}
}
What every sample is given: the connection string (and the ISample interface the samples implement).
namespace ArxSamples.MySQL;
/// <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 -- <name></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 MySqlConnector 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; }
}
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.MySQL;
/// <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}");
}
The application's ARX service and its schema catalog, created once and shared by the samples.
using ARX.SDK.MySQL.Services;
using ARX.SDK.Services;
namespace ArxSamples.MySQL;
/// <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 ArxMySqlDbService? _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 MySqlConnector pool and
/// closes it, unless you pass your own connection or transaction.
/// </summary>
public ArxMySqlDbService Service => _service ??= new ArxMySqlDbService(ConnectionString, Catalog);
}
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.MySQL;
/// <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 or Guid.
/// (A fixed list of text values is a String set, whether the column is an ENUM or a VARCHAR that a CHECK guards.)</item>
/// <item><b>Multiplicity</b>: Single (one code), or several codes stored as bit flags (Flags), a delimited string
/// (Delimited) or a JSON array (Json). MySQL has no array columns.</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 ArxMySqlDbService(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 (AUTO_INCREMENT): 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 Guid id the application gives (CHAR(36)): MySQL can only hand back generated integer ids.
.Table("device", ArxIdColumn.Supplied<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 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 VARCHAR(10).
.Add("AccountStatus", ArxSelectionSetKind.String, ("ACTIVE", "Active"), ("ON_HOLD", "On hold"), ("CLOSED", "Closed"))
// Multiple choice, stored as delimited text ("ENTERPRISE,PUBLIC"). RESELLER is retired: rows that have it still
// read back, but new writes can't use it. Column: account.segments VARCHAR(200).
.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.Delimited))
// Single-character codes. Column: contact.preferred_channel CHAR(1).
.Add("ContactChannel", ArxSelectionSetKind.Character, ('E', "Email"), ('P', "Phone"), ('S', "Text message"))
// A fixed list of text values, in a MySQL ENUM column, which allows only its labels.
// Column: data_type_showcase.mood ENUM('happy', 'neutral', 'sad').
.Add(new ArxSelectionSetDefinition("Mood", ArxSelectionSetKind.String, new[]
{
new ArxSelectionSetOption("happy", "Happy", 0),
new ArxSelectionSetOption("neutral", "Neutral", 1),
new ArxSelectionSetOption("sad", "Sad", 2)
}));
// Loaded from JSON: LeadSource (contact.lead_source VARCHAR(50)) 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);
}
A security check around ARX calls: suspected injection logged as a security event, strikes, blocking.
using ARX.SDK;
namespace ArxSamples.MySQL;
/// <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.
> 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