SQL Interactions

Order of Operations

Commands are executed if they are assigned to the HTTP method that was used to call the page. GET commands will always be generated after all the relevant POST/PUT/PATCH/DELETE commands are executed.

Never perform a write (INSERT/UPDATE/DELETE) on the GET method. Mutating data on GET is vulnerable to cross-site request forgery: the SameSite=Lax session cookie is still sent on a top-level cross-site GET navigation, so an attacker can trigger the write simply by getting a logged-in user to open a crafted URL. Put writes on POST/PUT/PATCH/DELETE. Omitting the method lets the server auto-detect it from the SQL verb (a write becomes a non-GET method), which is safe; only an explicit GET: prefix on a write is the hazard. The server logs a SECURITY warning at startup for any page that writes on GET.

When multiple commands are defined for a method, they will be executed sequentially based on their order of appearance in the file.

By default, all commands for a method will execute even if a preceding command fails and the SQL transaction will be committed after the last command runs, even if some of the commands failed. If you want a command to stop execution of remaining commands and trigger a SQL transaction rollback, mark that command as critical using method or alias modifiers.

Alternative commands (| modifier) are not executed unless the preceding command under the same alias has failed.

SQL Transactions

When the POST, PUT, PATCH, and DELETE methods have multiple SQL commands, they will be processed inside a SQL transaction by default. You can designate the GET method to be wrapped in a SQL transaction by prefixing it with ! (e.g. !GET:alias:command ...) Prefixing the other methods with a ! is valid but redundant. If a method only invokes one command, a SQL Transaction will not be used even if ! is specified on the method.

All the commands for a POST, PUT, PATCH, or DELETE method are committed or rolled back before the GET commands are processed to generate the output for the page.

SQL Parameters

All form fields, HTTP query parameters, and route variables are available to SQL commands as named parameters using the @ prefix. Even if the database does not support named parameters or natively uses something other than @ as a prefix, the @ format is used for templates connecting to all types of databases and the server will make the necessary translations to the query before running it on the database engine.

Variable names must begin with a letter and can contain letters, numbers, and the underscore character.

Parameter detection is quote-aware: an @name is only treated as a parameter when it appears in ordinary SQL text. An @ inside a single-quoted string literal (e.g. an email address like 'support@example.com'), a double-quoted or [bracketed] identifier, or a -- / /* */ comment is left untouched - no escaping is needed. A doubled @@ in ordinary text is passed through verbatim and never treated as a parameter, so MSSQL global variables such as @@IDENTITY work as written. (Note: PostgreSQL dollar-quoted strings $tag$...$tag$ are not recognized as string literals, so avoid embedding @name inside them.)

Never build SQL text from @parameters (dynamic SQL). Every @parameter is sent to the database as a bound value - HTMQL never concatenates user input into SQL text, so injection cannot be written by accident. The one way to reintroduce it is dynamic SQL inside the database: feeding a parameter into string-building such as MSSQL EXEC('SELECT ... ' + @value) or sp_executesql with a concatenated statement (or the same pattern inside a stored procedure the page calls). The database then assembles a new statement from the value, and the protection of binding is lost. Calling a procedure with parameters (EXEC procname @value) is fully parameterized and completely safe - it is only string-built statements that reopen injection.

If there is a conflict between the variable names provided by form fields, HTTP query parameters, and the slug variable, then the priority will be (1) slug variable, (2) form field, (3) HTTP query parameter.

Default parameter values can be set in the template comment by putting a line containing @variable=value. This default will only be used if the variable is not provided by a query parameter or form field. You can set defaults on a per-method basis by specifying it, e.g. POST:@id=0, GET:@test=value, otherwise the variable is defaulted for all methods.

Default values can also be supplied using a SQL query, e.g. @startdate:SELECT min(datefield) from table;. If multiple records and columns are returned by the query, only the first column from the first record is used to set the value.

Default values using SQL queries can utilize parameters themselves, e.g. @value2:select id from table where name=@value1;. Be aware that SQL commands are executed in sequence and the variables must be defined beforehand and will not be re-evaluated if the value changes.

If a command references a variable that was not passed as a form field, query parameter, or route variable, and has no default provided, the command cannot run:

• A non-critical command is silently skipped, and any remaining commands continue (skips are logged only when logging.skipped is enabled).
• A critical command (!/!!, or any command under a !! method) treats this as a failure: the method halts and the transaction rolls back, exactly as if the command had failed when executed. A critical command that never runs cannot have guarded anything, so it is not silently skipped.

To intentionally skip a critical command when a parameter is absent, give it an alternate | command that can run without that parameter - the alternate runs when the primary cannot, so the alias succeeds instead of failing.

Blank values (e.g. string "" or number 0) count as a parameter being passed.

All query parameters and form field values are internally typed as strings. The SQL driver handles converting them to the desired type for the SQL command.

You can retrieve the identity of an inserted row (e.g. for passing to redirects) by adding an OUTPUT list to the INSERT command, then those values will be available as template data under the alias name, and as SQL parameters using the @inserted_[columnname] (e.g. @inserted_id.) The SQL parameter will only return the values from the first row returned by the INSERT command.

SQL Error Handling

If any critical commands fail, an error message is displayed at the top of the page. See the Messages section for syntax to override or suppress the default error message.

The error box can be customized by creating an error.html file in the system folder. This template receives two data fields: .message (the error text) and .details (the raw error returned by the database engine).

If critical commands for a POST/PUT/PATCH/DELETE method fail, the submitted form field values are automatically overlaid on the GET query results where field names match, re-populating the form so the user can correct and resubmit. Field name matching is case-insensitive. This only applies when a critical command fails - non-critical failures do not trigger form re-population.

SQL Confirmation Messages

If all critical commands complete successfully, a confirmation message is displayed at the top of the page. See the Messages section for syntax to override or suppress the default success message.

The confirmation box can be customized by creating a success.html file in the system folder. This template receives a .message data field containing the success message text.