.. ==================================================
.. ==================================================
.. ==================================================
.. Header hierarchy
.. ==
.. --
.. ^^
.. ""
.. ;;
.. ,,
..
.. --------------------------------------------used to the update the records specified ------
.. Best Practice T3 reST: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/CheatSheet.html
.. Reference: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/Index.html
.. Italic *italic*
.. Bold **bold**
.. Code ``text``
.. External Links: `Bootstrap `_
.. Internal Link: :ref:`downloadButton` (default url text) or :ref:`download Button` (explicit url text)
.. Add Images: .. image:: ../Images/a4.jpg
..
..
.. Admonitions
.. .. note:: .. important:: .. tip:: .. warning::
.. Color: (blue) (orange) (green) (red)
..
.. Definition:
.. some text becomes strong (only one line)
.. description has to indented
.. -*- coding: utf-8 -*- with BOM.
.. include:: Includes.txt
.. _variables:
Variable
========
Variables in QFQ are surrounded by double curly braces. Four different types of variable substitution functionality is
provided. Access to:
* :ref:`store-variables`
* :ref:`sql-variables`
* :ref:`row-column-variables`
* :ref:`link-column-variables`
Some examples, including nesting::
# Store
#---------------------------------------------
{{r}}
{{index:FS}}
{{name:FS:alnumx:s:my default}}
# SQL
#---------------------------------------------
{{SELECT name FROM Person WHERE id=1234}}
# Row columns
#---------------------------------------------
{{10.pId}}
{{10.20.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}}
Leading and trailing spaces inside curly braces are removed.
* ``{{ SELECT "Hello World" }}`` becomes ``{{SELECT "Hello World"}}``
* ``{{ varname }}`` becomes ``{{varname}}``
.. _`store-variables`:
Store variables
---------------
.. note::
{{ :ref:`variable-name` : :ref:`store` : :ref:`sanitize-class` : :ref:`variable-escape` : :ref:`variable-default` : :ref:`variable-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`:
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 :ref:`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 ``{{}}`` 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`:
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, even if 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 :ref:`variable-type-message-violate` for default or customized message.
By default the value becomes `!!!!`. E.g. ``!!digit!!``.
For QFQ variables and FormElements:
+------------------+------+-------+-----------------------------------------------------------------------------------------+
| Name | Form | Query | Pattern |
+==================+======+=======+=========================================================================================+
| **alnumx** | Form | Query | [A-Za-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.
.. _`variable-escape`:
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 :ref:`configuration`. |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| C | Colon ``:`` will be escaped by ``\:`` |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| d | Double ticks ``"`` will be escaped by ``\"``. |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| 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: :ref:`qfq.json` (default is Argon2i) |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| w | wipe out current key/value pair from SIP store :ref:`variable-escape-wipe-key` |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| X | Throw exception if variable is not found in the given store(s). Outputs :ref:`variable-type-message-violate` |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| '' | Nothing defined - the escape/action class type configured in :ref:`configuration`. |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| \- | No escaping. |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
* 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 :ref:`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 :ref:`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 :ref:`setFeUserPassword`
* *stop replace* - ``S``: typically QFQ will replace nested variables as long as there are variables to replace. This options
stops this
* *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: :ref:`variable-type-message-violate`.
.. _`variable-escape-wipe-key`:
* *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::
10.sql = SELECT '...' AS _sendmail FROM Person AS p WHERE '{{action:S::w}}'='send' AND p.id={{pId:S}}
.. _`variable-default`:
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.
.. _`variable-type-message-violate`:
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 :ref:`variable-escape` qualifier ``C`` to let QFQ do the colon escaping.
.. _`sql-variables`:
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 :ref:`multi-database` setup, the database index can be specified after the opening curly
braces. ::
{{[1]SELECT ... }}
For using the indexData and indexQfq (:ref:`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`:
Row column variables
--------------------
Syntax: *{{.}}*
Only used in report to access outer columns. See :ref:`access-column-values` and :ref:`syntax-of-report`.
There might be name conflicts between VarName / SQL keywords and . QFQ checks first for **,
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
:ref:`access-column-values`) - using the STORE_RECORD is more portable cause no renumbering is needed if the level keys change.
.. _`link-column-variables`:
Link column variables
---------------------
These variables return a link, completely rendered in HTML. The syntax and all features of :ref:`column-link` are available.
The following code will render a *new person* button::
{{p:form&form=Person|s|N|t:new person AS link}}
For better reading, the format string might be wrapped in single or double quotes (this is optional): ::
{{"p:form&form=Person|s|N|t:new person" AS link}}
These variables are especially helpful in:
* `report`, to create create links or buttons outside of an SQL statement. E.g. in `head`, `rbeg`, ...
* `form`, to create links and buttons in labels or notes.