Catch an SQL injection attempt on create

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Create.RealCases;

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

The sample, as it is in the repository.

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

namespace ArxSamples.MySQL.Operations.Create.RealCases;

/// <summary>
/// Catching an SQL injection attempt on create, in four scenarios. ARX refuses a name (table or attribute) that carries
/// SQL with its own code, ARX1017 SuspectedInjection, before anything is sent to the database, so you can log it as a
/// security event instead of a bug. Values are never refused: they are parameters, and SQL in a value is just text.
/// </summary>
public sealed class RealCase8_CatchAnInjectionAttempt : ISample
{
    public string Name => "create-real-8-catch-an-injection-attempt";

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

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_AFieldNameFromAFormAsync(dbContext);

        await Scenario2_ATableNameFromTheRequestAsync(dbContext);

        await Scenario3_OneBadRowInAnImportAsync(dbContext);

        await Scenario4_SqlInAValueIsJustTextAsync(dbContext);
    }

    /// <summary>What an application does with the two codes: an attack goes to the security log, a typo is a bug to fix.</summary>
    private static void Report(ArxValidationException error)
    {
        if (error.Code == ArxErrorCode.SuspectedInjection)
        {
            Show($"SECURITY: {error.Message}");
        }
        else
        {
            Show($"bug, not an attack: {error.Message}");
        }
    }

    /// <summary>
    /// 1. A form whose field names are copied straight into attributes (the mistake), and an attacker who adds a field
    /// named with SQL. The whole create is refused with ARX1017; a plain typo in a field name is ARX1003.
    /// </summary>
    private static async Task Scenario1_AFieldNameFromAFormAsync(SampleContext dbContext)
    {
        Step("1. A field name from a form carries SQL");

        var posted = new Dictionary<string, object>
        {
            ["first_name"] = "Mallory",
            ["last_name"] = "Injection-C1",
            ["notes\") VALUES ('x'); DROP TABLE contact; --"] = "x",
        };

        var typo = new Dictionary<string, object>
        {
            ["first_name"] = "Ted",
            ["last name"] = "Typo-C1",
        };

        foreach (var form in new[] { posted, typo })
        {
            var contact = new ArxEntity("contact");

            foreach (var field in form)
            {
                contact[field.Key] = field.Value;
            }

            try
            {
                await dbContext.Service.CreateAsync(contact);
            }
            catch (ArxValidationException error) when (error.Code is ArxErrorCode.SuspectedInjection or ArxErrorCode.InvalidIdentifier)
            {
                Report(error);
            }
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM contact
         * WHERE last_name = 'Injection-C1' OR first_name = 'Ted';
         * -- Expect: 0: neither contact was created, and the contact table is still there.
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. A table name taken from the request (<c>?type=…</c>). ARX refuses the SQL in it; the fix is to map the request's
    /// word to a table from a fixed list, so user input never becomes a name.
    /// </summary>
    private static async Task Scenario2_ATableNameFromTheRequestAsync(SampleContext dbContext)
    {
        Step("2. A table name from the request carries SQL");

        var requested = "contact; DROP TABLE account; --";

        var fromTheRequest = new ArxEntity(requested);
        fromTheRequest["first_name"] = "Mallory";
        fromTheRequest["last_name"] = "Injection-C2";

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

        // The fix: a fixed list. Anything else is refused by the application, before ARX.
        string? table = requested switch
        {
            "person" => "contact",
            "company" => "account",
            _ => null,
        };

        Show(table == null ? $"the allow-list refused type '{requested}'" : $"would create in {table}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'account') AS account_exists,
         *        (SELECT count(*) FROM contact WHERE last_name = 'Injection-C2') AS created;
         * -- Expect: account_exists 1, created 0.
         * ===========================================================
         */
    }

    /// <summary>3. An import of three contacts where one row's column header carries SQL: the whole call is refused, nothing is written.</summary>
    private static async Task Scenario3_OneBadRowInAnImportAsync(SampleContext dbContext)
    {
        Step("3. One bad row in an import refuses the whole CreateMultiple");

        var first = new ArxEntity("contact");
        first["first_name"] = "Ann";
        first["last_name"] = "Import-C3";

        var bad = new ArxEntity("contact");
        bad["first_name"] = "Bob";
        bad["last_name\" text, \"x"] = "Import-C3";

        var third = new ArxEntity("contact");
        third["first_name"] = "Cy";
        third["last_name"] = "Import-C3";

        try
        {
            await dbContext.Service.CreateMultipleAsync(new List<ArxEntity> { first, bad, third });
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            Report(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM contact
         * WHERE last_name = 'Import-C3';
         * -- Expect: 0: Ann and Cy weren't created either.
         * ===========================================================
         */
    }

    /// <summary>4. SQL in a value isn't an attack on ARX: values are parameters, so it's stored exactly as typed and nothing is refused.</summary>
    private static async Task Scenario4_SqlInAValueIsJustTextAsync(SampleContext dbContext)
    {
        Step("4. SQL in a value is stored as plain text");

        var contact = new ArxEntity("contact");
        contact["first_name"] = "Robert'); DROP TABLE contact; --";
        contact["last_name"] = "Value-C4";
        var id = await dbContext.Service.CreateAsync(contact);

        Show($"created contact {id}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name
         * FROM contact
         * WHERE last_name = 'Value-C4';
         * -- Expect: 1 row: Robert'); DROP TABLE contact; -- (stored as typed; the contact table is still there).
         * ===========================================================
         */
    }
}

  > 1. A field name from a form carries SQL
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'notes") VALUES ('x'); DROP TABLE contact; --' 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.
      bug, not an attack: ARX1003 InvalidIdentifier: Attribute name 'last name' is not a valid identifier. Use ASCII letters, digits and underscores, starting with a letter or underscore, at most 64 characters. A column named otherwise is used through an alias declared in the schema catalog: .Alias(table, attribute, databaseName).
  > 2. A table name from the request carries SQL
      SECURITY: ARX1017 SuspectedInjection: Entity name 'contact; 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.
      the allow-list refused type 'contact; DROP TABLE account; --'
  > 3. One bad row in an import refuses the whole CreateMultiple
      SECURITY: ARX1017 SuspectedInjection: Attribute name 'last_name" text, "x' 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. SQL in a value is stored as plain text
      created contact 1