Concept¶
The QFQ extension is activated through tt-content records of type QFQ. One (or more) tt-content records per Typo3 page are necessary to render forms and reports.
Access privileges¶
The Typo3 FE Groups can be used to implement access privileges. Such groups are assigned to * Typo3 FE users, * Typo3 pages, * and/or Typo3 content records (e.g. QFQ records).
This will be used for general page structure privileges.
A record base privileges controlling (e.g. which user can edit which person record) will be implizit configured, by the way that records are viewable / editable (or not) through SQL in the specifiq QFQ tt-content statements.
Typo3 QFQ content element¶
Insert one or more QFQ content elements on a Typo3 page. Specify column and language per content record as wished.
The title of the QFQ content element will not be rendered. It’s only visible in the backend for orientation.
QFQ Keywords (Bodytext)¶
Name Explanation form Formname defined in ttcontent record bodytext * Fix. E.g.: form = person * by SIP: form = {{form}} * by SQL: form = {{SELECT c.form FROM conference AS c WHERE c.id={{a:C}} }} r <record id> The form will load the record with the specified id * Variants: r = 123, by SQL: r = {{SELECT ...}} * If not specified, the default is ‘0’ <level>.db Select a DB. Only necessary if a different than the standard DB should be used. <level>.fbeg Start token for every field (=column) <level>.fend End token for every field (=column) <level>.head Start token for whole <level> <level>.tail End token for whole <level> <level>.rbeg Start token for row. <level>.rbgd Alternating (per row) token <level>.rend End token for row. Will be rendered before subsequent levels are processed <level>.renr End token for row. Will be rendered after subsequent levels are processed <level>.rsep Seperator token between rows <level>.fsep Seperator token between fields (=columns) <level>.sql SQL Query <level>.althead If <level>.sql is empty, these token will be rendered debugShowBodyText If =‘1’ and config.qfq.ini:showDebugInfo=yes - shows a tooltip with bodytext
Debug¶
config.ini: SHOW_DEBUG_INFO = yes|no|auto
yes:
Form:
- For every internal link/button, show tooltips with decoded SIP on mouseover.
- Shows ‘Edit form’ (wrench symbol) button on a form.
Report: Will be configured per tt-content record.
debugShowBodyText = 1
no: No debug info.
auto: Depending if there is a Typo3 BE session, set internally:
- SHOW_DEBUG_INFO = yes (BE session exist)
- SHOW_DEBUG_INFO = no (no BE session)
Variables¶
Most fields of a form or report specification might contain:
‘’constants’’ (=strings), this is the standard use case.
‘’variables’’ retrieved from the stores (see below),
‘’SQL statements’’ (limited set of),
or any combination of the above.
A variable (or SQL) statement is surrounded by curly braces:
{{VarName[:<store / prio>[:<sanitize class>[:<escape>]]]}}
Example:
{{r}}
{{index:FS}}
{{name:FS:alnumx:s}}
{{SELECT name FROM person WHERE id=1234}}
{{SELECT name FROM person WHERE id={{r}} }}
{{SELECT name FROM person WHERE id={{key1:C:alnumx}} }}
Leading and trailing spaces inside curly braces are removed.
- {{ SELECT “Hello World” }} acts as {{SELECT “Hello World”}}
- {{ varname }} acts as {{varname}}
There are several stores, from where to retrieve the value. If a value is not found in one store, the next store is searched, until a value is found or there are no more stores available.
If anywhere along the line an empty string is found, this is a value: therefore, the search will stop.
If no value is found, the value is an <empty string>.
URL Parameter¶
- URL (=GET) Parameter can be used in forms and reports as variables.
- If a value violates a parameter sanitize class, the value becomes an empty string.
Escape¶
- Variables used in SQL Statements might cause trouble, if they contain single or double ticks.
- Escaping of single or double is defined by the parameter <escape> (fourth parameter):
- ‘s’ - single ticks will be escaped.
- ‘d’ - double ticks will be escaped.
- It’s not possible to escape single and double ticks at the same time.
- Which of them to escape (single or double) depends on the surrounding SQL query.
- Escaping is only necessary inside of SQL queries.
Sanitize class¶
- All values in Store C (Client) and store F (Form) will be sanitized:
- All Predefined variable names have a specific default sanitize class. For these variables, it’s not necessary to specify a sanitize class.
- All other variables (Store: C, F) get by default the sanitize class defined in the corresponding form. If not defined the default class is ‘digit’.
- A default sanitize class can be overwritten by individual definition: {{a:C:all}}
Name | Form | Query | Pattern |
---|---|---|---|
alnumx | Form | Query | [A-Za-z][0-9]@-_.,;: /() ÀÈÌÒÙàèìòùÁÉÍÓÚÝáéíóúýÂÊÎÔÛâêîôûÃÑÕãñõÄËÏÖÜŸäëïöüÿ |
digit | Form | Query | [0-9] |
numerical | Form | Query | [0-9.-+] |
Form | Query | [a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,} | |
min|max | Form | Compares the value against an lower and upper limit (numeric or string). | |
min|max date | Form | Compares the value against an lower and upper date or datetime. | |
pattern | Form | Compares the value against a regexp. | |
allbut | Form | Query | All characters allowed, but not [ ] { } % & #. The used regexp: ‘^[^[]{}%&\#]+$’, |
all | Form | Query | no sanitizing |
Store¶
Only variables that are known in a specified store can be substituted.
Name Description Content F Store: FORM - F: data not saved in database yet. All native FormElements. Recent values from the Browser. S Store: SIP - S: Client parameter ‘s’ will indicate the current SIP, which will be loaded from the SESSION repo to the SIP-Store. sip, r (recordId), form R Store: RECORD - R: Record - the current record loaded in the form All columns of the current record from the current table B Store: BEFORE - B: Record - the current record loaded in the form before any update All columns of the current record from the current table P Parent record. E.g.: on multi forms the current record of the outer query All columns of the MultiSQL Statement from the table for the current row D Default values column : The table.column specified default value. M Column type: The table.column specified type C Store: CLIENT - C: POST variable, if not found: GET variable Parameter sent from the Client (=Browser). T Store: TYPO3 (Bodytext) - T: a) Bodytext (ttcontent record), b) Typo3 internal variables See Typo3 tt_content record configuration V Store: VARS - V: Generic variables 0 Zero - allways value: 0, might be helpful if a variable is empty or undefined and will be used in an SQL statement. Any key E Empty - allways an empty string, might be helpful if a variable is empty or undefined and will be used in an SQL statement Any key Y Store: SYSTEM - Y: a) Database, b) helper vars for logging/debugging: SYSTEM_SQL_RAW ... SYSTEM_FORM_ELEMENT_COLUMN, c) Any custom fields: CONTACT, HELP, ...
- 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?’.
Predefined variable names¶
Store: FORM - F¶
- Sanatized: yes
- Represents the values in the form, typically before saving them.
- Used for:
- FormElements who 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 - S¶
- Sanatized: 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 paramter in one string <user defined> additional user defined link parameter
Store: RECORD - R¶
- Sanatized: no
- Current record loaded in Form.
- If r=0, alle values are empty.
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:R}}
Store: BEFORE - B¶
- Sanatized: no
- Current record loaded in Form without any modification.
- If r=0, alle 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:R}}
Store: CLIENT - C¶
- Sanatized: yes
Name Explanation s =SIP r record id. Typically stored in SIP, rarely specified on the URL keySemId always current Semester Id keySemIdUser {{keySemIdUser}}, may be changed by user HTTP_HOST current HTTP HOST REMOTE_ADDR Client IP address ‘$_SERVER[*]’ All other variables accessable by $_SERVER[]. Only the often used have a pre-defined sanitize class. form Unique name of current form ANREDE {{sex}} == male >> Sehr geehrter Herr, {{sex}} == female Sehr geehrte Frau EANREDE {{sex}} == male >> Dear Mr., {{sex}} == female >> Dear Mrs.
Store: TYPO3 (Bodytext) - T¶
- Sanatized: no
Name Explanation Note form Formname defined in ttcontent record bodytext
- Fix. E.g. form = person
- via SIP. E.g. form = {{form}}
see note pageId Record id 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
- note: not available * in ‘dynamicUpdate’ or * by FormElement class ‘action’ with type ‘beforeSave’, ‘afterSave’, ‘beforeDelete’, ‘afterDelete’.
Store: VARS - V¶
- Sanatized: no
Name Explanation random random string with length of 32 chars, alphanum slaveId see FormElement action filename Original filename of an uploaded file via an ‘upload’-FormElement. Valid only during processing of the current ‘upload’-formElement. fileDestinaton Destination (path & filename) for an uploaded file. Defined in an ‘upload’-FormElement.parameter. Valid: same as ‘filename’.
Store: SYSTEM - Y¶
- Sanatized: no
Name Explanation DB_USER defined in config.ini DB_SERVER defined in config.ini DB_NAME defined in config.ini DB_INIT defined in config.ini SQL_LOG defined in config.ini SQL_LOG_MODE defined in config.ini SHOW_DEBUG_INFO defined in config.ini CSS_LINK_CLASS_INTERNAL defined in config.ini CSS_LINK_CLASS_EXTERNAL defined in config.ini CSS_CLASS_QFQ_CONTAINER defined in config.ini EXT_PATH computed during runtime SITE_PATH computed during runtime DATE_FORMAT defined in config.ini class defined in config.ini (CSS_CLASS_QFQ_FORM) or form definition classPill defined in config.ini (CSS_CLASS_QFQ_FORM_PILL) or form definition classBody defined in config.ini (CSS_CLASS_QFQ_FORM_BODY) or form definition data-pattern-error defined in config.ini or form definition data-require-error defined in config.ini or form definition data-match-error defined in config.ini or form definition data-error defined in config.ini or form definition bsLabelColumns defined in config.ini (FORM_BS_LABEL_COLUMNS) or form definition bsInputColumns defined in config.ini (FORM_BS_INPUT_COLUMNS) or form definition bsNoteColumns defined in config.ini (FORM_BS_NOTE_COLUMNS) or form definition sqlFinal computed during runtime, used for error reporting sqlParamArray computed during runtime, used for error reporting sqlCount computed during runtime, used for error reporting
SQL Statement¶
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 parameters, including additional SQL statements. Inner SQL queries will be executed first.
All variables will be substituted one by one from inner to outer.
Maximum recursion depth: 5 (a recursion depth of 2 is sometimes used for mailing with templates, 3 and more probably confuses too much and is therefore not practicable, but supported until depth of 5)
The number of variables inside an input field or a SQL statement is not limited.
A resultset of a SQL statement will be imploded over all: concat all columns of a row, concat all rows - there is no glue string.
Example:
{{SELECT id, name FROM Person}} {{SELECT id, name, IF({{feUser}}=0,'Yes','No') FROM Vorlesung WHERE sem_id={{keySemId:Y}} }} {{SELECT id, city FROM Address AS adr WHERE adr.pId={{SELECT id FROM Account AS acc WHERE acc.name={{feUser}} }} }}
Special case for SELECT input fields. To deliver a result array specify an ‘!’ before the SELECT:
{{!SELECT ...}}
- This is only possible for the outermost SELECT.