Introduction

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.-+]
email 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.