Values and names that come from users or files

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Create.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 RealCase6_SaveUserInput().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

namespace ArxSamples.MSSQL.Operations.Create.RealCases;

/// <summary>
/// Saving what users typed and what files contain, in three scenarios: a public "contact us" form full of SQL-looking
/// text, a CSV header used directly as attribute names, and the better way, mapping the file's columns. Values are always
/// sent as parameters, so any text is stored exactly as typed. Names are validated and quoted, so a name can't change the SQL.
/// </summary>
public sealed class RealCase6_SaveUserInput : ISample
{
    public string Name => "create-real-6-save-user-input";

    public string Title => "Values and names that come from users or files";

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

        await Scenario2_ACsvHeaderUsedAsAttributeNamesAsync(dbContext);

        await Scenario3_MapTheFilesColumnsAsync(dbContext);
    }

    /// <summary>1. A form submission full of SQL-looking text is stored exactly as typed.</summary>
    private static async Task Scenario1_AFormFullOfSqlLookingTextAsync(SampleContext dbContext)
    {
        Step("1. A form submission full of SQL-looking text is stored exactly as typed");

        var form = new ContactForm("Robert'); DROP TABLE contact;--", "O'Brien", "Ünïcödé, emoji 🚀, quotes \" ' and a backslash \\");

        var contact = new ArxEntity("contact");
        contact["first_name"] = form.FirstName;
        contact["last_name"] = form.LastName;
        contact["notes"] = form.Message;
        var id = await dbContext.Service.CreateAsync(contact);

        var stored = await dbContext.Direct.RowAsync("contact", id.AsInt64());
        Show($"first_name {stored["first_name"]}");
        Show($"last_name  {stored["last_name"]}");
        Show($"notes      {stored["notes"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, notes
         * FROM contact
         * WHERE last_name = 'O''Brien';
         * -- Expect: 1 row, stored exactly as typed: Robert'); DROP TABLE contact;-- and the Unicode note.
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. Column names from a file header: only ASCII letters, digits and underscores (at most 63 characters) are accepted,
    /// and the check happens before any SQL is built. A header with quotes in it is SQL, not a typo, so it is refused as
    /// ARX1017 SuspectedInjection (RealCase10_CatchAnInjectionAttempt shows how to handle it).
    /// </summary>
    private static async Task Scenario2_ACsvHeaderUsedAsAttributeNamesAsync(SampleContext dbContext)
    {
        Step("2. A CSV header used directly as attribute names");

        var header = new[] { "first_name", "last_name", "email\" text, \"x" };
        var values = new[] { "Ivy", "Chen", "[email protected]" };

        var imported = new ArxEntity("contact");

        for (var i = 0; i < header.Length; i++)
        {
            imported[header[i]] = values[i];
        }

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT column_name
         * FROM information_schema.columns
         * WHERE table_name = 'contact' AND column_name LIKE 'email%';
         * -- Expect: 1 row, email: the header with quotes was refused before any SQL was built.
         * ===========================================================
         */
    }

    /// <summary>3. The better way: map the file's columns to the attributes you expect.</summary>
    private static async Task Scenario3_MapTheFilesColumnsAsync(SampleContext dbContext)
    {
        Step("3. Better: map the file's columns to the attributes you expect");

        var csvRow = new Dictionary<string, string> { ["First Name"] = "Ivy", ["Last Name"] = "Chen", ["E-mail"] = "[email protected]", ["Fax"] = "ignored" };

        var mapped = new ArxEntity("contact");
        mapped["first_name"] = csvRow["First Name"];
        mapped["last_name"] = csvRow["Last Name"];
        mapped["email"] = csvRow["E-mail"];
        var id = await dbContext.Service.CreateAsync(mapped);

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, email
         * FROM contact
         * WHERE email = '[email protected]';
         * -- Expect: 1 row, Ivy Chen.
         * ===========================================================
         */
    }

    private sealed record ContactForm(string FirstName, string LastName, string Message);
}

  > 1. A form submission full of SQL-looking text is stored exactly as typed
      first_name Robert'); DROP TABLE contact;--
      last_name  O'Brien
      notes      Ünïcödé, emoji 🚀, quotes " ' and a backslash \
  > 2. A CSV header used directly as attribute names
      caught ArxValidationException: ARX1017 SuspectedInjection: Attribute name 'email" 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.
  > 3. Better: map the file's columns to the attributes you expect
      stored contact 2