Catch an SQL injection attempt on Execute

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Execute.RealCases;

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

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

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Execute.RealCases;

/// <summary>
/// Catching an SQL injection attempt on Execute, in three scenarios. A routine name or a parameter name that
/// carries SQL is refused with ARX1017 SuspectedInjection before the database. SQL in a value is a parameter: it's stored
/// as text and runs nothing. Execute never takes SQL text.
/// </summary>
public sealed class RealCase8_CatchAnInjectionAttempt : ISample
{
    public string Name => "execute-real-8-catch-an-injection-attempt";

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

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Injection: keep me";
        account["account_number"] = "ACC-I801";
        var accountId = await dbContext.Service.CreateAsync(account);

        await Scenario1_ARoutineNameFromTheRequestAsync(dbContext);

        await Scenario2_AParameterNameFromTheRequestAsync(dbContext, accountId);

        await Scenario3_SqlInAValueAsync(dbContext, accountId);
    }

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

    /// <summary>1. <c>POST /api/run/{report}</c> with the report used as the procedure name.</summary>
    private static async Task Scenario1_ARoutineNameFromTheRequestAsync(SampleContext dbContext)
    {
        Step("1. A routine name from the request carries SQL");

        try
        {
            await dbContext.Service.ExecuteAsync(new ArxProcedureExpression("sp_account_report(1); DROP TABLE account; --"));
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
        {
            SecurityLog(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number = 'ACC-I801';
         * -- Expect: 1: the table is there.
         * ===========================================================
         */
    }

    /// <summary>2. A form whose field names become parameter names.</summary>
    private static async Task Scenario2_AParameterNameFromTheRequestAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. A parameter name from the request carries SQL");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name => 'x'); DELETE FROM account; --", "Pwned");

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

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

    /// <summary>3. SQL typed into a value: it's the account's new name, nothing more.</summary>
    private static async Task Scenario3_SqlInAValueAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. SQL in a value is a value");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name", "x'); DROP TABLE account; --");

        await dbContext.Service.ExecuteAsync(procedure);
        Show("renamed, literally");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name
         * FROM account
         * WHERE account_number = 'ACC-I801';
         * -- Expect: x'); DROP TABLE account; --
         * ===========================================================
         */
    }
}

  > 1. A routine name from the request carries SQL
      SECURITY: ARX1017 SuspectedInjection: Routine name 'sp_account_report(1); 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.
  > 2. A parameter name from the request carries SQL
      SECURITY: ARX1017 SuspectedInjection: Parameter name 'p_name => 'x'); 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.
  > 3. SQL in a value is a value
      renamed, literally