Configuring Scalar Prep Query for Tag-Based Columns

Replacing the column-name placeholder with a tag value does not select that SQL column: a prepared-query placeholder represents a data value, not an identifier such as a column name. Keep the row number parameterized, and compose the column identifier only after validating it against an allowed list.

Stop binding the column name as a value

The tempting quick fix is to keep SELECT ? and pass the language tag as the first argument. The database treats that placeholder as a value expression, so the result can be the tag text itself rather than the contents of the named column. Prepared parameters protect and transmit values; they do not rewrite the SQL statement’s structure.

Another poor fix is to concatenate both tag values into the SQL string. It may appear to work, but it removes parameter binding for the row number and can produce invalid SQL or unsafe queries when a tag contains unexpected text. Do not replace every placeholder with string formatting.

Separate the identifier from the row value

The query has two different kinds of inputs:

SQL engines parse identifiers such as table and column names as part of the statement structure. A bound parameter is supplied as a value after that structure has been defined; it cannot stand in for a column name. This is why the first placeholder in SELECT ? does not dynamically choose a column.

Validate the tag before composing SQL

Use a fixed mapping from accepted language tag values to actual column names. The allowed names must match the schema of Last_stop_errors. Reject or fall back on any tag value that is not an exact member of that mapping; do not assume that a tag is safe merely because it is normally set by the client.

# Replace these example mapping entries with actual column names in the table.
columns = {
    "en": "EnglishDescription",
    "fr": "FrenchDescription"
}

lang = system.tag.read("[Client]lang").value
tag = system.tag.read("[Client]Last error 2").value

column = columns.get(lang)
if column is not None:
    query = "SELECT %s FROM Last_stop_errors WHERE no=?" % column
    data = system.db.runScalarPrepQuery(query, [tag])
    system.tag.write("[Client]Description 2", data)

The mapping values above are placeholders for the database’s real column names, not inferred schema. Keep those values under developer control. Do not use the raw client tag as an unrestricted SQL fragment. The row selector remains bound with ?, so a tag value containing SQL syntax is handled as data rather than executable query text.

Run the query only after checking inputs

1. Read the language and row-number tags, then confirm each has the expected type and a usable value.
2. Look up the language in the allowlist. If it is absent, choose a defined fallback or skip the query and report the invalid language; never interpolate it directly.
3. Build the statement with the validated column name and retain the prepared placeholder for no.
4. Call system.db.runScalarPrepQuery(query, [tag]) and write the returned scalar to the description tag only when the result is valid for the application.

This preserves the working distinction in the script: the selected column varies with language, while the record is still selected by a parameterized row number. If the statement returns more than one row or more than one column, a scalar-query function is not the right result shape; use a query method intended for tabular results and handle its result accordingly.

Verify the selected description and handle misses

Test at least two allowed language values against a known row number. Confirm that the query returns each corresponding description, not the language code, and that the client description tag receives the expected scalar. Then test an unrecognized language and a row number with no matching record. The invalid language must not reach SQL as an identifier, and a missing database result must not be mistaken for a valid description.

If the query fails, inspect the final SQL structure and the parameter list separately: the selected column belongs in the statement, and the row number belongs in the parameter list. Compare each allowlisted column name with the database schema, then check the database error and tag read values before changing the query.

FAQ

Stop if the approved column names or expected result shape are unclear; inspect the database schema and query error rather than broadening the interpolation. If the query still fails after validation and the SQL/parameter split is confirmed, escalate through the product’s official support channel with the final statement structure and the database error.