Manual.rst

    * Replace the 'To' with the configured one.
    * Clear 'CC' and 'Bcc'
    * Write a note and the original configured receiver at the top of the email body.

_`mail-log-page`

Mail Log page
-------------

For debugging purposes you may like to add a Mail Log page in the frontend.
The following QFQ code could be used for that purpose (put it in a QFQ PageContent element)::

    # Page parameters
    1.sql = SELECT @grId := '{{grId:C0:digit}}' AS _grId
    2.sql = SELECT @summary := IF('{{summary:CE:alnumx}}' = 'true', 'true', 'false') AS _s

    # Filters
    10 {
      sql = SELECT gr.id, IF(gr.id = @grId, "' selected>", "'>"), gr.value, ' (Id: ', gr.id, ')'
               FROM gGroup AS gr
               INNER JOIN MailLog AS ml ON ml.grId = gr.id
               GROUP BY gr.id
      head = <form onchange='this.submit();' class='form-inline'><input type='hidden' name='id' value='{{pageAlias:T0}}'>
               Filter By Group: <select name='grId' class='form-control'><option value=''></option>
      rbeg = <option value='
      rend = </option>
      tail = </select>
    }
    20 {
      sql = SELECT IF(@summary = 'true', ' checked', '')
      head = <div class='checkbox'><label><input type='checkbox' name='summary' value='true'
      tail = >Summary</label></div></form>
    }

    # Mail Log
    50 {
      sql = SELECT id, '</td><td>', grId, '</td><td>', xId, '</td><td>',
                REPLACE(receiver, ',', '<br>'), '</td><td>', REPLACE(sender, ',', '<br>'), '</td><td>',
                DATE_FORMAT(modified, '%d.%m.%Y<br>%H:%i:%s'), '</td><td style="word-break:break-word;">',
                CONCAT('<b>', subject, '</b><br>', IF(@summary = 'true', CONCAT(SUBSTR(body, 1,
                       LEAST(IF(INSTR(body, '\n') = 0, 50, INSTR(body, '\n')), IF(INSTR(body, '<br>') = 0, 50,
                       INSTR(body, '<br>')))-1), ' ...'), CONCAT('<br>', REPLACE(body, '\n', '<br>'))) )
              FROM MailLog WHERE (grId = @grId OR @grId = 0)
              ORDER BY modified DESC
              LIMIT 100
      head = <table class="table table-condensed table-hover"><tr>
                 <th>Id</th><th>grId</th><th>xId</th><th>To</th><th>From</th><th>Date</th><th>E-Mail</th></tr>
      tail = </table>
      rbeg = <tr><td>
      rend = </td></tr>
    }

_`form-submit-log-page`

Form Submit Log page
--------------------

For debugging purposes you may like to add a Form Submit Log page in the frontend.
The following QFQ code could be used for that purpose (put it in a QFQ PageContent element)::

    # Filters
    20.shead = <form onchange='this.submit()' class='form-inline'><input type='hidden' name='id' value='{{pageAlias:T0}}'>
    20 {
      sql = SELECT id, IF(id = '{{formId:SC0}}', "' selected>", "'>"), name
            FROM Form ORDER BY name
      head = <label for='formId'>Form:</label> <select name='formId' id='formId' class='form-control'><option value=0></option>
      tail = </select>
      rbeg = <option value='
      rend = </option>
    }
    30 {
      sql = SELECT feUser, IF(feUser = '{{feUser:SCE:alnumx}}', "' selected>", "'>"), feUser
            FROM FormSubmitLog GROUP BY feUser ORDER BY feUser
      head = <label for='feUser'>FE User:</label> <select name='feUser' id='feUser' class='form-control'><option value=''></option>
      tail = </select>
      rbeg = <option value='
      rend = </option>
    }
    30.stail = </form>

    # Show Log
    50 {
      sql = SELECT l.id,
          CONCAT('<b>Form</b>: ', f.name,
            '<br><b>Record Id</b>: ', l.recordId,
            '<br><b>Fe User</b>: ', l.feUser,
            '<br><b>Date</b>: ', l.created,
            '<br><b>Page Id</b>: ', l.pageId,
            '<br><b>Session Id</b>: ', l.sessionId,
            '<br><b>IP Address</b>: ', l.clientIp,
            '<br><b>User Agent</b>: ', l.userAgent,
            '<br><b>SIP Data</b>: <div style="margin-left:20px;">', "<script>var data = JSON.parse('", l.sipData,
              "'); for (var key in data) {
              document.write('<b>' + key + '</b>: ' + data[key] + '<br>'); }</script>", '</div>'),
          CONCAT("<script>var data = JSON.parse('", l.formData,
            "'); for (var key in data) {
              document.write('<b>' + key + '</b>: ' + data[key] + '<br>'); }</script>")
          FROM FormSubmitLog AS l
          LEFT JOIN Form AS f ON f.id = l.formId
          WHERE (l.formId = '{{formId:SC0}}' OR '{{formId:SC0}}' = 0)
            AND (l.feUser = '{{feUser:SCE:alnumx}}' OR '{{feUser:SCE:alnumx}}' = '')
          ORDER BY l.created DESC LIMIT 100
      head = <table class="table table-hover">
             <tr><th>Id</th><th style="min-width:250px;">Environment</th><th>Submitted Data</th>
      tail = </table>
      rbeg = <tr>
      renr = </tr>
      fbeg = <td>
      fend = </td>
    }

.. _variables:

Variable
========

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

* `store-variables`_
* `sql-variables`_
* `row-column-variables`_
* `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 pf 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}}*

Types
-----

.. _`store-variables`:

Store variables
^^^^^^^^^^^^^^^

Syntax:  *{{VarName[:<store(s)[:<sanitize class>[:<escape>[:<default value>[:type violate message]]]]]}}*

See also:

 * `store`_
 * `sanitize-class`_
 * `variable-escape`_
 * `variable-default`_
 * `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}}

* 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.

.. _`sanitize-class`:

Sanitize class
--------------

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 `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-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**


.. _`variable-escape`:

Escape/Action class
-------------------

The following `escape` & `action` types are available:


+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Token | Description                                                                                                                      |
+=======+==================================================================================================================================+
| c     | Config - the escape type configured in `configuration`_.                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| C     | Colon ':' will be escaped against \\:.                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| d     | Double ticks " will be escaped against \\".                                                                                      |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| 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).                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| s     | Single ticks ' will be escaped against \\'.                                                                                      |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| S     | Stop replace. If the replaced value contains nested variables, they won't be replaced.                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| m     | `real_escape_string() <http://php.net/manual/en/mysqli.real-escape-string.php>`_ (m = mysql)                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| p     | Password hashing: depends on the hashing type in the Typo3 installation, includes salting if configured.                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| 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 `variable-type-message-violate`_                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| ''    | Nothing defined - the escape/action class type configured in `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 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 a single tick inside (like 'Miller's'),
the inside single tick has to be escaped. This is typically done by a backlash: 'Millers\\'s'.

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 `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: `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
-------

* 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 '\'.

.. _`variable-type-message-violate`:

Type message violate
--------------------

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 `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

* A 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 a SQL statement is not limited.

Result: string
""""""""""""""

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

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 ...}}

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`:

Row column variables
""""""""""""""""""""

Syntax:  *{{<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 '<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.


.. _`link-column-variables`:

Link column variables
^^^^^^^^^^^^^^^^^^^^^

These variables return a link, completely rendered in HTML. The syntax and all features of `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 a SQL statement. E.g. in `head`, `rbeg`, ...
* `form`, to create links and buttons in labels or notes.

Security
========

All values passed to QFQ will be:

* Checked against max. length and allowed content, on the client and on the server side. On the server side, the check
  happens before any further processing. The 'length' and 'allowed' content is specified per `FormElement`. 'digit' or
  'alnumx' is the default. Violating the rules will stop the 'save record' process (Form) or result in an empty
  value (Report). If a variable is not replaced, check the default sanitize class.

* Only elements defined in the `Form` definition or requested by `Report` will be processed.

* UTF8 normalized (normalizer::normalize) to unify different ways of composing characters. It's more a database interest,
  to work with unified data.

SQL statements are typically fired as `prepared statements` with separated variables.
Further *custom* SQL statements will be defined by the webmaster - those do not use `prepared statements` and might be
affected by SQL injection. To prevent SQL injection, every variable is by default escaped with `mysqli::real_escape_string`.

**QFQ notice**:

* Variables passed by the client (=Browser) are untrusted and use the default sanitize class 'digit' (if nothing else is
  specified). If alpha characters are submitted, the content violates `digit` and becomes therefore
  `!!<name of sanitize class>!!` - there is no error message. Best is to always use SIP (value is trustful) or at least
  digits for GET (=client) parameter (user might change those and therefore those are *not* trustful).

Get Parameter
-------------

**QFQ security restriction**:

* GET parameter might contain urlencoded content (%xx). Therefore all GET parameter will be processed by 'urldecode()'.
  As a result a text like '%nn' in GET variables will always be decoded. It's not possible to transfer '%nn' itself.

* GET values are limited to securityGetMaxLength (extension-manager-qfq-configuration_) chars - any violation will
  stop QFQ. Individual exceptions are defined via ExceptionMaxLength_.

* GET parameter 'type' and 'L' might affected by (T3, configuration dependent) cache poisoning. If they contain non digit
  values, only the first character is used (if this is a digit) or completely cleaned (else).

Post Parameter
--------------

Per `FormElement` (HTML input) the default is to `htmlspecialchars()` the input. This means &<>'" will be encoded as htmlentity
and saved as a htmlentity in the database. In case any of these characters (e.g. for HTML tags) are
required, the encoding can be disabled per FormElement: `encode=none` (default is `specialchar`).

During Form load, htmlentities are decoded again.

$_SERVER
--------

All $_SERVER vars are htmlentities encoded (all, not only specialchars!) .

Honeypot
--------

Every QFQ Form contains 'honeypot'-HTML input elements (HTML: hidden & readonly). Which of them to use is configured in
`configuration`_ (default:   'username', 'password' and 'email'). On every start of QFQ (form, report, save, ...),
these variables are tested if they are non-empty. In such a case a probably malicious bot has send the request and the
request will not be processed.

If any of the default configured variable names are needed (which will never be the case for QFQ), an explicit variable name
list have to be configured in `configuration`_.

**QFQ security restriction**:

* The honeypot variables can't be used in GET or POST as regular HTML input elements - any values of them will terminate QFQ.

Violation
---------

On any violation, QFQ will sleep `securityAttackDelaySeconds` (`configuration`_) and than exit the running PHP process.
A detected attack leads to a complete white (=empty) page.

If `securityShowMessage`: true (`configuration`_), at least a message is displayed after the delay.

Client Parameter via SIP
------------------------

Links with URL parameters, targeting to the local website, are typically SIP encoded. Instead of transferring the parameter
as part of the URL, only one unique GET parameter 's' is appended at the link. The parameter 's' is unique (equal to a
timestamp) for the user. Assigned variables are stored as a part of the PHP user session on the server.
Two users might have the same value of parameter 's', but the content is completely independent.

Variables needed by Typo3 remains on the link and are not 'sip-encoded'.

.. _`SecureDirectFileAccess`:

Secure direct file access
-------------------------

If the application uploads files, mostly it's not necessary and often a security issue, to offer a direct download of
the uploaded files. Best is to create a directory, e.g. `<site path>/fileadmin/protected` and deny direct access via
webbrowser to it. E.g. for Apache set a rule: ::

    <Directory "/var/www/html/fileadmin/protected">
        Require all denied
    </Directory>

If you only have access to `.htaccess`, create a file `<site path>/fileadmin/protected/.htaccess` with: ::

    <IfModule mod_authz_core.c>
         Require all denied
    </IfModule>

.. important::

    All QFQ uploads should save files only in/below such a protected directory.

To offer download of those files, use the reserved column name '_download' (see `download`_) or variants.

.. important::

    To protect the installation against executing of uploaded malicious script code, disable PHP for the final
    upload directory. E.g. `fileadmin` (Apache)::

        <Directory "/var/www/html/fileadmin">
          php_admin_flag engine Off
        </Directory>

This is in general a good security improvement for directories with user supplied content.

File upload
-----------

By default the mime type of every uploaded file is checked against a white list of allowed mime types. The mime type of
a file can be (easily) faked by an attacker. This check is good to handle regular user file upload for specific file types
but won't help to prevent attacks against uploading and executing malicious code.

Instead prohibit the execution of user contributed files by the webserver config (`SecureDirectFileAccess`_).

Typo3 Setup - best practice
---------------------------

* Activate notification emails for every BE login (if there are only few BE users). In case the backend has been hacked,
  unusual login's (time or username) will appear: ::

        [BE][warning_email_addr] = <your email>
        [BE][warning_mode] = 1

.. _`store`:

Store
=====

Only variables that are known in a specified store can be substituted.

 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 |Name |Description                                                                             | Content                                                                        |
 +=====+========================================================================================+================================================================================+
 | B   | :ref:`STORE_BEFORE`: Record - the current record loaded in the form before any update. | All columns of the current record from the current table. See `STORE_BEFORE`_. |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | C   | :ref:`STORE_CLIENT`: POST variable, if not found: GET variable.                        | Parameter sent from the Client (=Browser). See `STORE_CLIENT`_.                |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | D   | Default values column : The *table.column* specified *default value*.                  |                                                                                |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | E   | *Empty* - always an empty string, might be helpful if a variable is empty or undefined | Any key                                                                        |
 |     | and will be used in an SQL statement.                                                  |                                                                                |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | F   | :ref:`STORE_FORM`: data not saved in database yet.                                     | All native *FormElements*. Recent values from the Browser. See: `STORE_FORM`_  |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | L   | :ref:`STORE_LDAP`: Will be filled on demand during processing of a *FormElement*.      | Custom specified list of LDAP attributes. See `STORE_LDAP`_.                   |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | M   | Column type: The *table.column* specified *type*.                                      |                                                                                |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | P   | Parent record. E.g.: on multi & copy forms the current record of the outer query.      | All columns of the MultiSQL Statement from the table for the current row       |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | R   | :ref:`STORE_RECORD`: Record - the current record loaded in the form.                   | All columns of the current record from the current table. See `STORE_RECORD`_. |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | S   | :ref:`STORE_SIP`: Client parameter 's' will indicate the current SIP, which will be    | sip, r (recordId), form. See `STORE_SIP`_.                                     |
 |     | loaded from the SESSION repo to the SIP-Store.                                         |                                                                                |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | T   | :ref:`STORE_TYPO3`: a) Bodytext (ttcontent record), b) Typo3 internal variables.       | See Typo3 tt_content record configuration. See `STORE_TYPO3`_.                 |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | U   | :ref:`STORE_USER`: per user variables, valid as long as the browser session lives.     | Set via report: '...' AS '_=<var name>' See: `STORE_USER`_,                    |
 |     |                                                                                        | `store_user_examples`_                                                         |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | V   | :ref:`STORE_VARS`: Generic variables.                                                  | See `STORE_VARS`_.                                                             |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | Y   | :ref:`STORE_SYSTEM`: a) Database, b) helper vars for logging/debugging:                | See `STORE_SYSTEM`_.                                                           |
 |     | SYSTEM_SQL_RAW ... SYSTEM_FORM_ELEMENT_COLUMN, c) Any custom fields: CONTACT, HELP, ...|                                                                                |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
 | 0   | *Zero* - always value: 0, might be helpful if a variable is empty or undefined and     | Any key                                                                        |
 |     | will be used in an SQL statement.                                                      |                                                                                |
 +-----+----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+

* Default *<prio>*: *FSRVD* - Form / SIP / Record / Vars / Table definition.
* Hint: Preferable, parameter should be submitted by SIP, not by Client (=URL).

  * Warning: Data submitted via 'Client' can be easily spoofed and altered.
  * Best: Data submitted via SIP never leaves the server, cannot be spoofed or altered by the user.
  * SIPs can _only_ be defined by using *Report*. Inside of *Report* use columns 'Link' (with attribute 's'), 'page?' or 'Page?'.

.. _STORE_FORM:

Store: *FORM* - F
-----------------

* Sanitized: *yes*
* Represents the values in the form, typically before saving them.
* Used for:

  * *FormElements* which will be rerendered, after a parent *FormElement* has been changed by the user.
  * *FormElement* actions, before saving the form.
  * Values will be sanitized by the class configured in corresponding the *FormElement*. By default, the sanitize class is `alnumx`.

 +---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | Name                            | Explanation                                                                                                                                |
 +=================================+============================================================================================================================================+
 | <FormElement name>              | Name of native *FormElement*. To get, exactly and only, the specified *FormElement* (for 'pId'): *{{pId:F}}*                               |
 +---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+

.. _STORE_SIP:

Store: *SIP* - S
----------------

* Sanitized: *no*
* Filled automatically by creating links. E.g.:

  * in `Report` by using `_page?` or `_link` (with active 's')
  * in `Form` by using subrecords: 'new', 'edit', 'delete' links (system) or by column type `_page?`, `_link`.

 +-------------------------+-----------------------------------------------------------+
 | Name                    | Explanation                                               |
 +=========================+===========================================================+
 | sip                     | 13 char uniqid                                            |
 +-------------------------+-----------------------------------------------------------+
 | r                       | current record id                                         |
 +-------------------------+-----------------------------------------------------------+
 | form                    | current form name                                         |
 +-------------------------+-----------------------------------------------------------+
 | table                   | current table name                                        |
 +-------------------------+-----------------------------------------------------------+
 | urlparam                | all non Typo3 parameter in one string                     |
 +-------------------------+-----------------------------------------------------------+
 | <user defined>          | additional user defined link parameter                    |
 +-------------------------+-----------------------------------------------------------+

.. _STORE_RECORD:

Store: *RECORD* - R
-------------------


* Sanitized: *no*
* *Form*: Current record.
* *Report*: See `access-column-values`_
* If r=0, all values are empty.

 +------------------------+-------------------------------------------------------------------------------------------------------------------------+
 | Name                   | Type     | Explanation                                                                                                  |
 +========================+==========+==============================================================================================================+
 | <column name>          | Form     | Name of a column of the primary table (as defined in the current form). Example: *{{pId:R}}*                 |
 +------------------------+----------+--------------------------------------------------------------------------------------------------------------+
 | <column name>          | Report   | Name of a column of a previous fired SQL query. Example: *{{pId:R}}*                                         |
 +------------------------+----------+--------------------------------------------------------------------------------------------------------------+
 | &<column name>         | Report   | Name of a column of a previous fired SQL query, typically used by columns with a `special-column-names`_.    |
 |                        | (final)  | Final value. Example: '{{link:R}}' returns 'p:home&form=Person|s|b:success|t:Edit'.                          |
 |                        |          | Whereas '{{&link:R}}' returns '<span class="btn btn-success"><a href="?home&s=badcaffee1234">Edit</a></span> |
 +------------------------+----------+--------------------------------------------------------------------------------------------------------------+

.. _STORE_BEFORE:

Store: *BEFORE* - B
-------------------


* Sanitized: *no*
* Current record loaded in Form without any modification.
* If r=0, all values are empty.

This store is handy to compare new and old values of a form.

 +------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
 | Name                   | Explanation                                                                                                                                      |
 +========================+==================================================================================================================================================+
 | <column name>          | Name of a column of the primary table (as defined in the current form). To get, exactly and only, the specified form *FormElement*: *{{pId:B}}*  |
 +------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+

.. _STORE_CLIENT:

Store: *CLIENT* - C
-------------------

* Sanitized: *yes*

 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
 | Name                    | Explanation                                                                                                                              |
 +=========================+==========================================================================================================================================+
 | s                       | =SIP                                                                                                                                     |
 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
 | r                       | record id. Only if specified as GET parameter - typically stored in SIP (=STORE_SIP)                                                     |
 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
 | form                    | Name of form to load. Only if specified as GET parameter - typically stored in SIP (=STORE_SIP)                                          |
 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
 | HTTP_HOST               | current HTTP HOST                                                                                                                        |
 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
 | REMOTE_ADDR             | Client IP address                                                                                                                        |
 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
 | '$_SERVER[*]'           | All other variables accessible by *$_SERVER[]*. Only the often used have a pre-defined sanitize class.                                   |
 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
 | Authorization           | Value of the HTTP Header 'Authorization'. This is typically not set. Mostly used for authentication of REST requests                     |
 +-------------------------+------------------------------------------------------------------------------------------------------------------------------------------+

.. _STORE_TYPO3:

Store: *TYPO3* (Bodytext) - T
-----------------------------

* Sanitized: *no*

 +-------------------------+-------------------------------------------------------------------+----------+
 | Name                    | Explanation                                                       | Note     |
 +=========================+===================================================================+==========+
 | form                    | | Formname defined in ttcontent record bodytext                   | see note |
 |                         | | * Fix. E.g. *form = person*                                     |          |
 |                         | | * via SIP. E.g. *form = {{form:SE}}*                            |          |
 +-------------------------+-------------------------------------------------------------------+----------+
 | pageId                  | Record id of current Typo3 page                                   | see note |
 +-------------------------+-------------------------------------------------------------------+----------+
 | pageAlias               | Alias of current Typo3 page. If empty, take  pageId.              | see note |
 +-------------------------+-------------------------------------------------------------------+----------+
 | pageTitle               | Title of current Typo3 page                                       | see note |
 +-------------------------+-------------------------------------------------------------------+----------+
 | pageType                | Current selected page type (typically URL parameter 'type')       | see note |
 +-------------------------+-------------------------------------------------------------------+----------+
 | pageLanguage            | Current selected page language (typically URL parameter 'L')      | see note |
 +-------------------------+-------------------------------------------------------------------+----------+
 | ttcontentUid            | Record id of current Typo3 content element                        | see note |
 +-------------------------+-------------------------------------------------------------------+----------+
 | feUser                  | Logged in Typo3 FE User                                           |          |
 +-------------------------+-------------------------------------------------------------------+----------+
 | feUserUid               | Logged in Typo3 FE User uid                                       |          |
 +-------------------------+-------------------------------------------------------------------+----------+
 | feUserGroup             | FE groups of logged in Typo3 FE User                              |          |
 +-------------------------+-------------------------------------------------------------------+----------+
 | beUser                  | Logged in Typo3 BE User                                           |          |
 +-------------------------+-------------------------------------------------------------------+----------+
 | beUserLoggedIn          | 'yes' | 'no' - Status if a BE-User is logged in                   |          |
 +-------------------------+-------------------------------------------------------------------+----------+

* **note**: not available:

  * in :ref:`dynamic-update` or
  * by *FormElement* class 'action' with type 'beforeSave', 'afterSave', 'beforeDelete', 'afterDelete'.

.. _STORE_VARS:

Store: *VARS* - V
-----------------

* Sanitized: *no*

 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | Name                    | Explanation                                                                                                                                |
 +=========================+============================================================================================================================================+
 | random                  | Random string with length of 32 alphanum chars (lower & upper case). This is variable is always filled.                                    |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | slaveId                 | see *FormElement* `action`                                                                                                                 |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+

.. _`store_vars_form_element_upload`:

* FormElement 'upload':

 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | Name                    | Explanation                                                                                                                                |
 +=========================+============================================================================================================================================+
 | filename                | Original filename of an uploaded file via an 'upload'-FormElement. Valid only during processing of the current 'upload'-formElement.       |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | filenameOnly            | Like filename, but without path.                                                                                                           |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | filenameBase            | Like `filename`, but without an optional extension. E.g. filename='image.png' comes to filenameBase='image'                                |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | filenameExt             | Like `filename`, but only the optional extension. E.g. filename='image.png' comes to filenameExt='png'                                     |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | fileDestination         | Destination (path & filename) for an uploaded file. Defined in an 'upload'-FormElement.parameter. Valid: same as 'filename'.               |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | fileSize                | Size of the uploaded file.                                                                                                                 |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | mimeType                | Mime type of the uploaded file.                                                                                                            |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+


The directive `fillStoreVar` will fill the store VARS with custom values. Existing Store VARS values will be merged together with them.
E.g.: ::

    fillStoreVar = {{SELECT p.name, p.email FROM Person AS p WHERE p.id={{pId:S}} }}

* After filling the store, the values can be retrieved via `{{name:V}}` and `{{email:V}}`.
* Be careful by specifying general purpose variables like `id`, `r`, `pageId` and so on. This might conflict with existing variables.
* `fillStoreVar` can be used in `form-parameter`_ and `fe-parameter-attributes`_


.. _STORE_LDAP:

Store: *LDAP* - L
-----------------

* Sanitized: *yes*
* See also :ref:`LDAP`:

 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
 | Name                    | Explanation                                                                                                                                |
 +=========================+============================================================================================================================================+
 | <custom defined>        | See *ldapAttributes*                                                                                                                       |
 +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+



.. _STORE_SYSTEM:

Store: *SYSTEM* - Y
-------------------

* Sanitized: *no*

See configuration_ for a list of all settings.



.. _STORE_USER:

Store: *USER* - U
-----------------

* Sanitized: *no*

At start of a new browser session (=user calls the website the first time or was logged out before) the store is empty.
As soon as a value is set in the store, it remains as long as the browser session is alive (=until user logs out).

Values can be set via report '... AS "_=<var name>"'

See also: `store_user_examples`_

.. _LDAP:

LDAP
====

A form can retrieve data from LDAP server(s) to display or to save them. Configuration options for LDAP will be specified
in the *parameter* field of the *Form* and/or the *FormElement*. Definitions of the *FormElement* will overwrite definitions
of the *Form*. One LDAP Server can be configured per *FormElement*. Multiple *FormElements* might use individual LDAP
Server configurations.

To decide which Parameter should be placed on *Form.parameter* and which on *FormElement.parameter*: If LDAP access is ...

* only necessary in one *FormElement*, most useful setup is to specify all values in that specific *FormElement*,
* needed on multiple *FormElement*s (of the same *Form*, e.g. one *input* with *typeAhead*, one *note* and one *action*), it's more
  efficient to specify the base parameter *ldapServer*, *ldapBaseDn* in *Form.parameter* and the rest on the current
  *FormElement*.

+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| Parameter                   | Example                          | Description                                                   | Form | FormElement | Used for |
+=============================+==================================+===============================================================+======+=============+==========+
| ldapServer                  | directory.example.com            | Hostname. For LDAPS: `ldaps://directory.example.com:636`      | x    | x           | TA, FSL  |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| ldapBaseDn                  | ou=Addressbook,dc=example,dc=com | Base DN to start the search                                   | x    | x           | TA, FSL  |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| ldapAttributes              | cn, email                        | List of attributes to save in STORE_LDAP                      | x    | x           | FSL      |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| ldapSearch                  | (mail=john.doe@example.com)      | Regular LDAP search expression                                | x    | x           | FSL      |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| ldapTimeLimit               | 3 (default)                      | Maximum time to wait for an answer of the LDAP Server         | x    | x           | TA, FSL  |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| ldapUseBindCredentials      | ldapUseBindCredentials=1         | Use LDAP_1_* credentials from config-qfq-php_ for ldap_bind() | x    | x           | TA, FSL  |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadLdap               |                                  | Enable LDAP as 'Typeahead' data source                        |      | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadLdapSearch         | `(|(cn=*?*)(mail=*?*))`          | Regular LDAP search expression, returns upto typeAheadLimit   | x    | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadLdapSearchPrefetch | `(mail=?)`                       | Regular LDAP search expression, typically return one record   | x    | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadLdapSearchPerToken |                                  | Split search value in token and OR-combine every search with  | x    | x           | TA       |
|                             |                                  |  the individual tokens                                        |      |             |          |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadLdapValuePrintf    | `'%s / %s', cn, mail`            | Custom format to display attributes, as `value`               | x    | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadLdapIdPrintf       | `'%s', mail`                     | Custom format to display attributes, as `id`                  | x    | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadLimit              | 20 (default)                     | Result will be limited to this number of entries              | x    | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadPedantic           | typeAheadPedantic=0              | Turn off 'pedantic' mode - allow any values (see below)       | x    | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| typeAheadMinLength          | 2 (default)                      | Minimum number of characters before starting the search       | x    | x           | TA       |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+
| fillStoreLdap               |                                  | Activate `Fill STORE LDAP` with the first retrieved record    |      | x           | FSL      |
+-----------------------------+----------------------------------+---------------------------------------------------------------+------+-------------+----------+

* *typeAheadLimit*: there might be a hard limit on the server side (e.g. 100) - which can't be extended.
* *ldapUseBindCredentials* is only necessary if `anonymous` access is not possible. RDN and password has to be configured in
  config-qfq-php_.

.. _LDAP_Typeahead:

Typeahead (TA)
--------------

*Typeahead* offers continous searching of a LDAP directoy by using a regular *FormElement* of type *text*.
The *FormElement.parameter*=*typeAheadLdap* will trigger LDAP searches on every user **keystroke**
(starting after *typeAheadMinLength* keystrokes) for the current *FormElement* - this is different from *dynamicUpdate*
(triggered by leaving focus of an input element). Typeahead delivers a list of elements.

* *FormElement.parameter.typeAheadLdap* - activate the mode *Typeahead* - no value is needed, the existence is suffucient.
* *Form.parameter* or *FormElement.parameter*:

  * *ldapServer* = `directory.example.com`
  * *ldapBaseDn* =  `ou=Addressbook,dc=example,dc=com`
  * *typeAheadLdapSearch* = `(|(cn=*?*)(mail=*?*))`
  * *typeAheadLdapValuePrintf* = `'%s / %s', cn, email`
  * *typeAheadLdapIdPrintf* = `'%s', email`
  * Optional: *ldapUseBindCredentials* = 1

All fetched LDAP values will be formatted with:

* *typeAheadLdapValuePrintf*, shown to the user in a drop-down box and
* *typeAheadLdapIdPrintf*, which represents the final data to save.

The `id/value` translation is compareable to a regular select drop-down box with id/value pairs.
Only attributes, defined in *typeAheadLdapValuePrintf* / *typeAheadLdapIdPrintf* will be fetched from the LDAP directory.
To examine all possible values of an LDAP server, use the commandline tool `ldapsearch`. E.g.::

  ldapsearch -x -h directory.example.com -L -b ou=Addressbook,dc=example,dc=com "(mail=john.doe@example.com)"

All occurrences of a '?' in *ldapSearch* will be replaced by the user data typed in via the text-*FormElement*.
The typed data will be escaped to fulfill LDAP search limitations.
Regular *Form* variables might be used on all parameter and will be evaluated during form load (!) - *not* at the time when
the user types something.

Pedantic
^^^^^^^^

The *typeAheadPedantic* mode ensures that the typed value (technically this is the value of the *id*, latest in the moment
when loosing the focus) is valid (= exists on the LDAP server or is defined in `typeAheadSql`).
If the user typed something and that is not a valid *id*, the client (=browser) will delete the input when losing the focus.
To identify the exact *id*, an additional search filter is necessary: `typeAheadLdapSearchPrefetch` - see next topic.