Newer
Older
.. ==================================================
.. ==================================================
.. ==================================================
.. 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 <http://getbootstrap.com/>`_
.. Internal Link: :ref:`downloadButton` (default url text) or :ref:`download Button<downloadButton>` (explicit url text)
..
..
.. 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-function-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}}
{{getFullname(pId) => fullname AS function}}
{{getFullname(pId) AS function}}
Leading and trailing spaces inside curly braces are removed.
* ``{{ SELECT "Hello World" }}`` becomes ``{{SELECT "Hello World"}}``
* ``{{ varname }}`` becomes ``{{varname}}``
.. _`store-variables`:
{{ :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
^^^^^^^^^^^^^
{{**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}}
{{name:**store**:sanitize:escape:default:message}}
* 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`:
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,
* 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 `!!<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.
.. _`variable-escape`:
Escape/Action class
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() <http://php.net/manual/en/function.ldap-escape.php>`_ (LDAP_ESCAPE_FILTER). |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| L | LDAP DN values. `ldap-escape() <http://php.net/manual/en/function.ldap-escape.php>`_ (LDAP_ESCAPE_DN). |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| m | `real_escape_string() <http://php.net/manual/en/mysqli.real-escape-string.php>`_ (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<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. |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| E | Encrypt value with AES. See for more information here :ref:`variable-encrypt` |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| D | Decrypt an encrypted value. See for more information here :ref:`variable-decrypt` |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
* 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
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
* *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-encrypt`:
* *encryption* - ``E``: encrypts the value with AES (Advanced Encryption Standard).
To use this feature its necessary that the encryption key is configured. See :ref:`encryption-key`.
The default encryption method will be used if nothing defined in Variable. See: :ref:`extension-manager-qfq-configuration`.
Available defaults: AES-128, AES-256
Example::
10.sql = SELECT 'my secret' AS _myValue
20.sql = UPDATE Person SET secret='{{myValue:RE:all:E}}' WHERE id = 1
30.sql = UPDATE Person SET secret='{{myValue:RE:all:E=AES-256}}' WHERE id = 1
Carsten Rose
committed
.. _`variable-decrypt`:
* *decryption* - ``D``: decrypts values which are with QFQ encrypted.
.. _`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
A few functions needs more than a returned string, instead separate columns are necessary. To indicate an array
{{!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.
{{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: *{{<level>.<column>}}*
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 <line identifier>. QFQ checks first for *<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
:ref:`access-column-values`) - using the STORE_RECORD is more portable cause no renumbering is needed if the level keys change.
.. _`link-function-column-variables`:
Link/Function column variables
------------------------------
Link column
^^^^^^^^^^^
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}}
Optional: For better reading, the format string might be wrapped in single or double quotes: ::
{{"p:form&form=Person|s|N|t:new person" AS link}}
Function column
^^^^^^^^^^^^^^^
* `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.
Definition of a qfqFunction: Report with qfqFunction. Subheader: getFullname::
render = api
10.sql = SELECT CONCAT(lastName, ', ', firstName) FROM Person WHERE id = {{pId:R0}}
b) Somewhere in a different report, output is returned in STORE_RECORD variable `fullname`::