Variable

Variables in QFQ are surrounded by double curly braces. Four different types of variable substitution functionality is provided. Access to:

Some examples, including nesting:

# Get variable from store
#---------------------------------------------
{{r}}
{{index:FS}}
{{name:FS:alnumx:s:my default}}

# Set variable in store (only for store ARUVW)
#---------------------------------------------
{{hello AS _var:R}}
{{'hello world' AS '_var:R'}}

# SQL
#---------------------------------------------
{{SELECT name FROM Person WHERE id=1234}}

# Row columns via line number (deprecated)
#---------------------------------------------
{{10.pId}}
{{10.20.pId}}

# Row columns via label
#---------------------------------------------
{{<label1>.pId}}
{{<label2>.pId}}

# Nesting
#---------------------------------------------
{{SELECT name FROM Person WHERE id={{r}} }}
{{SELECT name FROM Person WHERE id={{key1:C:alnumx}} }} # explained below
{{SELECT name FROM Person WHERE id={{SELECT id FROM Persfunction LIMIT 1}} }} # it's more efficient to use only one query

# Link Columns
{{p:form=Person&r=1|t:Edit Person|E|s AS link}}
{{p:form=Person&r=1|t:Edit Person|E|s AS _link}}
{{"p:form=Person&r=1|t:Edit Person|E|s" AS '_link'}}

# Function Columns: output in {{fullname:R}}
{{getFullname(pId) => fullname AS function}}

# Function Columns: output direct tt_content
{{getFullname(pId) AS function}}

Leading and trailing spaces inside curly braces are removed.

  • {{ SELECT 'Hello World'  }} becomes {{SELECT 'Hello World'}}

  • {{ varname   }} becomes {{varname}}

Nested variable

  • If a variable is replaced and the new content contains again a variable, than that one is replaced too.

Example:

line1 {
  sql = SELECT 'John' AS _var1
}
line2 {
  sql = SELECT CONCAT('Hello {', '{var1:R}', '}') AS _var2
}

Output of {{var2:R}} will be: Hello John. The quirk with the CONCAT(…) is necessary, so that QFQ does not replace var1 already in line2. This is just an example and probably not of a practical use case.

But it is very useful for text templates. In a record a text template is saved. These text template might contain variables which should be dynamically replaced (e.g. a date or a person name, …).

  • Recursive limit: The maximum number of resursive substition per variable is 5. If you have more than 5 substituions by one variable, you will see an exception. This is not a practical limitation, cause to program a logic, where a variable will be replaced resursivly is quite uncommon.

Get store variable

Note

{{ Variable name : Store : Sanitize class : Escape/Action class : Default : Type message violate }}

Example:

{{pId}}
{{pId:FSE}}
{{pId:FSE:digit}}
{{pId::digit}}
{{name:FSE:alnumx:m}}
{{name:::m}}
{{name:FSE:alnumx:m:John Doe}}
{{name::::John Doe}}
{{name:FSE:alnumx:m:John Doe:forbidden characters}}
{{name:::::forbidden characters}}

Variable name

{{name:store:sanitize:escape:default:message}}

  • Any string.

  • Some stores, like STORE_RECORD, are completely user defined variables, others, like STORE_SYSTEM, are mainly predefined.

Example:

{{pId}}
{{personName}}

Store

{{name:store:sanitize:escape:default:message}}

  • Check out all possible Store.

  • Zero or more stores might be specified to be searched for the given VarName.

  • If no store is specified, the default for the searched stores are: FSRVD (=FORM > SIP > RECORD > VARS > DEFAULT).

  • If the VarName is not found in one store, the next store is searched, up to the last specified store.

  • If the VarName is not found and a default value is given, the default is returned.

  • If no value is found, nothing is replaced - the string {{<VarName>}} remains.

  • If anywhere along the line an empty string is found, this is a value: therefore, the search will stop.

Example:

{{personName:SE}}  >> look for `personName` in STORE_SIP and if not found return empty string.
{{pId:SRF0}}       >> look for `pId` in STORE_SIP and if not found in STORE_RECORD and if not found in STORE_FORM
                      and if not found return 0.

Sanitize class

{{name:store:sanitize:escape:default:message}}

Values in STORE_CLIENT C (Client=Browser) and STORE_FORM F (Form, HTTP ‘post’) are checked against a sanitize class. Values from other stores are not checked against any sanitize class, unless a sanitize class is specified.

  • Variables get by default the sanitize class defined in the corresponding FormElement. If not defined, the default class is digit.

  • A default sanitize class can be overwritten by individual definition: {{a:C:alnumx}}

  • If a value violates the specific sanitize class, see Type message violate for default or customized message. By default the value becomes !!<name of sanitize class>!!. E.g. !!digit!!.

For QFQ variables and FormElements:

Name

Form

Query

Pattern

alnumx

Form

Query

[a-z][A-Z][0-9]@*-_.,;: /()ÀÈÌÒÙàèìòùÁĆÉÍÓÚÝáćéíóúýÂÊÎÔÛâêîôûÃÑÕãñõÄËÏÖÜŸäëïöüÿçČčĐ𩹮žß

digit

Form

Query

[0-9]

numerical

Form

Query

[0-9.-+]

allbut

Form

Query

All characters allowed, but not [ ] { } % #. The used regexp: ^[^\[\]{}%\\#]+$',

all

Form

Query

no sanitizing

Only in FormElement:

auto

Form

Only supported for FormElements. Most suitable checktype is dynamically evaluated based on native column definition, the FormElement type, and other info. See below for details.

email

Form

Query

[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}

pattern

Form

Compares the value against a regexp.

Rules for CheckType Auto (by priority):

  • TypeAheadSQL or TypeAheadLDAP defined: alnumx

  • Table definition

    • integer type: digit

    • floating point number: numerical

  • FE Type

    • password, note: all

    • editor, text and encode = specialchar: all

  • None of the above: alnumx

Example:

{{personName:CE:allbut}}  >> look for `personName` in STORE_CLIENT (apply sanitize class `allbut`) and if not found return empty string.
{{pId:FR0}}       >> look for `pId` in STORE_FORM (apply sanitize class `digit` cause no sanitize class is defined)
                     and if still not found in STORE_RECORD and if still not found in
                     STORE_FORM and if still not found return 0.

Escape/Action class

{{name:store:sanitize:escape:default:message}}

The following escape & action types are available:

Token

Description

c

Config - the escape type configured in Configuration.

C

Colon : will be escaped by \:

d

Double ticks " will be escaped by \".

f

Prepare a search pattern: Transforms a string by replacing any repetition of characters, that aren’t Unicode letters or numbers by a single ‘%’. The result is wrapped with ‘%’. E.g.: Dr. John* Doe becomes `%Dr%John%Doe% Flexible (person) search

h

Encode data with htmlspecialchars(): & “ ‘ < >

H

Encode data with htmlentities(): all characters which have HTML character entity equivalents are translated into these entities. Additional ‘{’ and ‘}’ will be replaces by ‘&lbrace;’ and ‘&rbrace;’.

l

LDAP search filter values: ldap-escape() (LDAP_ESCAPE_FILTER).

L

LDAP DN values. ldap-escape() (LDAP_ESCAPE_DN).

m

real_escape_string() (m = mysql)

p

Password hashing with salting. Password hashing algorithm may be configured in: QFQ credentials (default is Argon2i)

P

Pipe Symbol | will be escaped by \|

s

Single ticks ' will be escaped by \'.

S

Stop replace. If the replaced value contains nested variables, they won’t be replaced.

t

Returns the central european timezone CET / CEST depending on the given date. If the TZ is not GMT+1 or 2, returns GMT+x

T

Strip all html-tags.

w

Wipe out current key/value pair from SIP store variable-escape-wipe-key

X

Throw exception if variable is not found in the given store(s). Outputs Type message violate

‘’

Nothing defined - the escape/action class type configured in Configuration.

-

No escaping.

E

Encrypt value: Action., Encryption key

D

Decrypt an encrypted value: Action., Encryption key

  • The escape/action class is defined by the fourth parameter of the variable. E.g.: {{name:FE:alnumx:m}} (m = mysql).

  • It’s possible to combine multiple escape/action classes, they will be processed in the order given. E.g. {{name:FE:alnumx:Ls}} (L, s).

  • Escaping is typically necessary for all user supplied content, especially if they are processed via SQL or LDAP queries.

  • Be careful when escaping nested variables. Best is to escape only the most outer variable.

  • In Configuration a global escapeTypeDefault can be defined. The configured escape/action class applies to all substituted variables, who do not contain a specific escape/action class.

  • Additionally a defaultEscapeType can be defined per Form (separate field in the Form editor). This overwrites the global definition of configuration. By default, every Form.defaultEscapeType = ‘c’ (=config), which means the setting in Configuration.

  • To suppress an escape type, define the escape type = ‘-’ on the specific variable. E.g.: {{name:FE:alnumx:-}}.

Escape

To escape a character typically means: a character, which have a special meaning/function, should not treated as a special character. E.g. a string is surrounded by single ticks '. If such a string should contain the same single tick inside, the inside single tick has to be escaped - if not, the string end’s at the second tick, not the third. This is typically done by a backlash: \

QFQ offers different ways of escaping. Which of them to use, depends on the situation.

Especially variables used in SQL statements might cause trouble when using: NUL (ASCII 0), \n, \r, \, ‘, “, or Control-Z.

Action

  • password - p: transforms the value of the variable into a Typo3 salted password hash. The hash function is the one used by Typo3 to encrypt and salt a password. This is useful to manipulate FE user passwords via QFQ. See Set FE-User password

  • stop replace - S: typically QFQ will replace nested variables as long as there are variables to replace, see ref::nested-variable. This options stops this, no recursive replacement.

  • exception - X: If a variable is not found in any given store, it’s replace by a default value or an error message. In special situation it might be useful to do a full stop on all current actions (no further procession). A custom message can be defined via: Type message violate.

  • wipe - w: In special cases it might be useful to get a value via SIP only one time and after retrieving the value it will be deleted in STORE SIP . Further access to the variable will return variable undefined. At time of writing only the STORE SIP supports the feature wipe. This is useful to suppress any repeating events by using the browser history. The following example will send a mail only the first when it is called with a given SIP:

sql = SELECT '...' AS _sendmail FROM Person AS p WHERE '{{action:S::w}}'='send' AND p.id={{pId:S}}

  • encryption - E: encrypts the value with AES (Advanced Encryption Standard).

  • decryption - D: decrypt QFQ encrypted values.

See Encryption key, Extension Manager: QFQ Configuration.

Example encrypt:

{
  sql = SELECT 'my secret' AS _myValue
}
{
  sql = UPDATE Person SET secret='{{myValue:RE:all:E}}' WHERE id = 1
}
{
  sql = UPDATE Person SET secret='{{myValue:RE:all:E=AES-256}}' WHERE id = 1
}

Default

{{name:store:sanitize:escape:default:message}}

  • Any string can be given to define a default value.

  • If a default value is given, it makes no sense to define more than one store: with a default value given, only the first store is considered.

  • If the default value contains a :, that one needs to be escaped by \

  • For dedicated variables this value has a special meaning. E.g. {{randomUniq:V}} uses this as expire argument.

Type message violate

{{name:store:sanitize:escape:default:message}}

If a value violates the sanitize class, the following actions are possible:

  • c - The violated class will be set as content, surrounded by !!. E.g. !!digit!!. This is the default.

  • e - Instead of the value an empty string will be set as content.

  • 0 - Instead of the value the string 0 will be set as content.

  • custom text … - Instead of the value, the custom text will be set as content. If the text contains a :, that one needs to be escaped by \ . Check Escape/Action class qualifier C to let QFQ do the colon escaping.

Set store variable

In general, STORE variables are mostly filled indirect. E.g.

  • in a report via sql = SELECT 'hello world' AS var (variable var in STORE_RECORD) or

  • if a user press save in a form, than all values are filled into STORE_FORM or

  • in T3 QFQ extension config into STORE_SYSTEM.

Additional explicit variable initialisation / setting is possible.

Note

To set a variable in a store:

{{<value> AS _<key>:<store>}}

  • The variable name (=`key`) has to preceeded by a _, followed by a : and a store character (ARUVW).

  • Allowed stores: STORE_ACCESS (A), STORE_RECORD (R), STORE_USER (U), STORE_VARS (V), STORE_WEB (W).

  • Quoting of ‘<value>’ and/or ‘_<key>:<store>’ is possible by single-/double-/backtick. E.g. {{"<value>" AS '_<key>:<store>'}}

  • Setting a variable, does not generate any output!

Usage example::

init {
  shead = {{'Hello world' AS '_var1:R'}}{{1 AS _pId:R}}
}

start {
  sql = SELECT p.name FROM Person AS p WHERE p.id={{pId:R}}
  shead = 'Greeting: {{var1:R}}'
}

Output:

  Greeting: Hello world Jane

  • Currently, it’s not possible to set a variable in head and using it on the same level in sql. Please define a separated init { ... as shown in the example.

SQL variables

  • The detection of an SQL command is case insensitive.

  • Leading whitespace will be skipped.

  • The following commands are interpreted as SQL commands:

    • SELECT

    • INSERT, UPDATE, DELETE, REPLACE, TRUNCATE

    • SHOW, DESCRIBE, EXPLAIN, SET

  • An SQL Statement might contain variables, including additional SQL statements. Inner SQL queries will be executed first.

  • All variables will be substituted one by one from inner to outer.

  • The number of variables inside an input field or an SQL statement is not limited.

Result: string

A result of an SQL statement will be imploded over all: concat all columns of a row, concat all rows - there is no glue string:

{{SELECT 'hello world'}}   >> hello world
{{SELECT firstName FROM Person}}   >> JaneJohnLisa
{{SELECT firstName, name FROM Person}}   >> JaneDoeJohnDoeLisaDoe

Result: row

A few functions needs more than a returned string, instead separate columns are necessary. To indicate an array result, specify those with an !:

{{!SELECT firstName, name FROM Person LIMIT 2}}
 >> Array: [ [ 'firstName' -> 'Jane', 'name' -> 'Doe' ], [ 'firstName' -> 'John', 'name' -> 'Doe' ] ]

This manual will specify the individual QFQ elements, who needs an array instead of a string. It’s an error to return a string where an array is needed and vice versa.

Database index

To access different databases in a Multi Database setup, the database index can be specified after the opening curly braces.

{{[1]SELECT ... }}

For using the indexData and indexQfq (Configuration), it’s a good practice to specify the variable name instead of the numeric index.

{{[{{indexData:Y}}]SELECT ...}}

If no dbIndex is given, {{indexData:Y}} is used.

Example:

{{SELECT id, name FROM Person}}
{{SELECT id, name, IF({{feUser:T0}}=0, 'Yes', 'No')  FROM Person WHERE id={{r:S}} }}
{{SELECT id, city FROM Address AS adr WHERE adr.accId={{SELECT id FROM Account AS acc WHERE acc.name={{feUser:T0}} }} }}
{{!SELECT id, name FROM Person}}
{{[2]SELECT id, name FROM Form}}
{{[{{indexQfq:Y}}]SELECT id, name FROM Form}}

Row column variables

Syntax: {{<alias>.<column>}} or (deprecated) {{<level>.<column>}}

Only used in report to access outer columns. See Access column values and Syntax of report.

There might be name conflicts between VarName / SQL keywords and <line identifier>. QFQ checks first for <alias> (<level>), than for SQL keywords and than for VarNames in stores.

All types might be nested with each other. There is no limit of nesting variables.

Very specific: Also, it’s possible that the content of a variable is again (including curly braces) a variable - this is sometimes used in text templates, where the template is retrieved from a record and specific locations in the text will be (automatically by QFQ) replaced by values from other sources.

General note: using this type of variables is only the second choice. First choice is {{column:R}} (see Access column values) - using the STORE_RECORD is more portable cause no renumbering is needed if the level keys change.

Special column names: To access the definition of the variable created by a special column name, use {{&.... Further details under Access column values .