Study guide · Database Access and DataHandler
Reading and writing data in TYPO3
Almost everything in TYPO3 lives in a database. This page works from the bottom up: databases and SQL, then Doctrine DBAL with QueryBuilder and restrictions, then DataHandler and registry. Afterwards you know which tool to use when, and how to build safe queries.
Databases: relational and key-value
A database management system, or DBMS, is software that stores and manages data and answers queries.
Relational means that data is kept in tables of rows and columns, linked via keys. The primary key uniquely identifies each row. A foreign key points to the primary key of another table. Transactions ensure consistent changes according to the ACID principle.
You query the data with SQL. Every system speaks its own dialect of it. TYPO3 14 runs on MySQL, MariaDB, PostgreSQL and SQLite. TYPO3 does not support Oracle, Microsoft SQL Server or MongoDB.
A key-value store such as Redis or Memcached works quite differently. It stores every value under a unique key and returns it via that key. There is no fixed schema and there are no queries with JOIN. In return it is very fast, and entries can have an expiry time. That makes it a good fit for caches, sessions and configuration values.
Reading and changing data with SQL
SELECT reads rows, and WHERE restricts them. ORDER BY sorts, ascending with ASC and descending with DESC. GROUP BY combines rows into groups, and functions such as COUNT calculate per group. HAVING filters the groups.
SELECT category, COUNT(*) FROM products
WHERE active = 1
GROUP BY category
HAVING COUNT(*) > 5;The order is fixed: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. WHERE filters rows before grouping, HAVING filters groups after it. That is why COUNT must not appear in WHERE.
COUNT with a star counts all rows. COUNT with a column name only counts rows in which that column is not NULL. If the speaker table has ten rows and three have no email, the result is 10 and 7. An empty string is not NULL.
A JOIN connects tables. An INNER JOIN returns only matching pairs. A LEFT JOIN from categories to products returns all categories, including those without products. The product columns are then NULL.
You write with INSERT, UPDATE and DELETE. INSERT creates a row. DELETE removes rows, while the table itself stays. UPDATE and DELETE without WHERE affect all rows, and the database does not refuse that.
Structure, relationships and TYPO3 tables
Statements for data are called DML. Statements for the structure are called DDL, such as CREATE TABLE, ALTER TABLE and CREATE INDEX. You add a column with ALTER TABLE and ADD.
An index speeds up searching and sorting on its columns. But it takes up storage and slows down writes, so do not index every column. An index on pid and deleted helps queries that filter by both. An index never changes the result of a query.
You describe relationships by their cardinality:
- One to one: a person has exactly one identity card.
- One to n: a blog has many posts, and each post belongs to one blog. The foreign key sits on the n side. If a speaker gives many talks, the speaker's uid is stored in the table of talks.
- M to n: posts have many tags, and tags belong to many posts. This needs an intermediate table with foreign keys to both sides.
In TYPO3 the intermediate table is called an MM table, with the columns uid_local, uid_foreign and sorting. A comma-separated list in one column violates the first normal form, which requires atomic values. The solution is again an MM table.
Every table managed by TYPO3 has fixed fields:
uidis the unique primary key of the record.pidis the uid of the page the record is stored on. The value 0 means the root level.tstampholds the last change,crdatethe creation.deletedmarks deleted records,hiddenhidden ones.
Products or news without a content page of their own go into a page of the type Folder, which is doktype 254.
If ctrl.delete is set in the TCA, deleting in the backend only sets the field deleted to 1. This is called soft delete. The row stays but is hidden everywhere, and the Recycler can restore it. A hidden record, in contrast, stays visible in the backend.
You describe your own tables in ext_tables.sql. TYPO3 creates the management fields itself. The file only needs what differs, for example an index. The schema comparison in the Maintenance module or the command extension:setup applies it to the database.
CREATE TABLE tx_myextension_domain_model_talk (
KEY event_date (event_date)
);Doctrine DBAL and the QueryBuilder
The TYPO3 database layer is based on Doctrine DBAL, the Database Abstraction Layer. It provides connections, the QueryBuilder and schema management, and translates everything into the dialect of the system. Doctrine ORM maps rows to objects and manages their state. It is not part of the Core. Extbase has its own ORM based on the TCA.
The entry point is the ConnectionPool. It returns a connection with getConnectionForTable() and a QueryBuilder with getQueryBuilderForTable().
The Connection has shortcuts such as insert(), update(), delete() and select(). They bind the values as parameters automatically. For a technical table without TCA, such as an import log, this is the usual way. If an import writes to two tables, use a transaction. Then both changes succeed together, or neither does.
$connection = $this->connectionPool->getConnectionForTable('tx_myextension_log');
$connection->insert('tx_myextension_log', ['message' => $message, 'crdate' => time()]);
$connection->transactional(function () use ($connection) { /* both writes */ });
$connection->beginTransaction();
try {
/* both writes */
$connection->commit();
} catch (\Throwable $e) {
$connection->rollBack();
throw $e;
}For everything else you use the QueryBuilder. It has one method per clause, and the ExpressionBuilder behind expr() builds conditions. Get a new QueryBuilder from the ConnectionPool for every query, because it keeps its state.
$qb = $this->connectionPool->getQueryBuilderForTable('tx_myextension_domain_model_talk');
$rows = $qb
->select('t.uid', 't.title')
->from('tx_myextension_domain_model_talk', 't')
->join('t', 'tx_myextension_domain_model_conference', 'c',
$qb->expr()->eq('c.uid', $qb->quoteIdentifier('t.conference')))
->where($qb->expr()->like('t.title',
$qb->createNamedParameter('%' . $qb->escapeLikeWildcards($term) . '%')))
->executeQuery()
->fetchAllAssociative();There are two ways to execute a query:
executeQuery()is for SELECT and COUNT and returns a result object. From it,fetchAllAssociative()reads all rows,fetchAssociative()the next one andfetchOne()a single value. If there is no row,fetchAssociative()returns false.executeStatement()is for INSERT, UPDATE and DELETE and returns the number of affected rows.
Let the database do the counting. The method count() replaces the SELECT part with COUNT, and fetchOne() reads the number. Loading all rows and counting them in PHP costs memory and time.
$count = $qb->count('uid')
->from('tx_myextension_domain_model_talk')
->where($qb->expr()->eq('pid', $qb->createNamedParameter(12, Connection::PARAM_INT)))
->executeQuery()
->fetchOne();
$qb->expr()->in('uid', $qb->createNamedParameter($uids, Connection::PARAM_INT_ARRAY));A list needs an array type. PARAM_INT_ARRAY is the TYPO3 constant for ArrayParameterType::INTEGER from Doctrine, and both are equivalent. For strings there is PARAM_STR_ARRAY.
Unlike the Doctrine QueryBuilder, the TYPO3 one automatically adds restrictions to SELECT and COUNT and quotes names.
Restrictions: what is hidden automatically
A query on tt_content returns no deleted and no hidden records without any action on your part. The reason is restrictions: conditions that the QueryBuilder adds based on the TCA. They apply in the frontend, the backend and on the command line.
The DefaultRestrictionContainer is always active. It contains the DeletedRestriction, HiddenRestriction, StartTimeRestriction and EndTimeRestriction. It does not check user groups or workspaces.
The FrontendRestrictionContainer adds the WorkspaceRestriction and the FrontendGroupRestriction. You set it yourself, for example when a console command should read like a visitor who is not logged in.
You adjust restrictions via getRestrictions():
removeByType()removes exactly one. If a backend module should also show hidden records, remove theHiddenRestriction. Deleted records stay hidden.removeAll()removes all of them, including the one for deleted records.add()adds one. To see everything except deleted records, callremoveAll()and thenadd()with theDeletedRestriction.
$qb->getRestrictions()->removeByType(HiddenRestriction::class);
$qb->getRestrictions()->removeAll()
->add(GeneralUtility::makeInstance(DeletedRestriction::class));
$qb->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));An enforced restriction implements the EnforceableQueryRestrictionInterface. It survives removeAll(), but removeByType() still removes it. The Connection shortcuts select() and count() also use the default restrictions. You just cannot adjust them there.
Querying safely
In an SQL injection, input is built into a query in a way that changes its meaning. This happens when you paste a value from the request directly into the SQL text. An attacker then sends SQL instead of a title and reads or deletes data.
$qb->where('title = "' . $request->getQueryParams()['title'] . '"'); // unsafe
$qb->where($qb->expr()->eq('uid', $qb->createNamedParameter($uid, Connection::PARAM_INT))); // safeThe defense is the prepared statement. The database first receives the query with placeholders and then, separately, the values. A value can therefore never be read as a command. If the same query runs many times, it can even be faster, because the database parses it only once.
That is why in the QueryBuilder every input goes through createNamedParameter(). With PARAM_INT the value reaches the database as a number, not as text. Custom SQL run with executeStatement() also needs parameters. The Connection shortcuts, Extbase queries using the query object and the DataHandler bind their values themselves.
Prepared statements have limits:
- Table and column names cannot be passed as placeholders. For field names there is
quoteIdentifier(), but it is wrong for values. - In LIKE, the percent sign and the underscore in the input stay wildcards. You escape them with
escapeLikeWildcards(). You still need your own percent signs around the term, otherwise you only get exact matches. - They do not protect against cross-site scripting in the output.
You also limit the damage like this: give the database user only the privileges it needs. Do not show database error messages to visitors, because they reveal the structure. Home-made escaping, such as removing quotes with str_replace, is error-prone.
Persistence in the Core
TYPO3 writes records in two ways. In the backend, the DataHandler saves. Plugins and Extbase modules use Extbase persistence. It writes past the DataHandler via Doctrine DBAL, but reads the TCA for the mapping.
The TCA describes every table with its fields, types and relations. Instead of reading the raw array, use the Schema API: the TcaSchemaFactory returns, via get(), a schema object with the fields and capabilities of a table.
The reference index in sys_refindex stores references between records and files. The backend uses it to show where something is used. The DataHandler keeps it up to date, and since TYPO3 14 Extbase does too. A direct SQL import bypasses this, and afterwards the display is wrong. Then the command referenceindex:update helps, or, since TYPO3 14, the Maintenance module with Check and Update Reference Index.
The DataHandler
The DataHandler creates, changes and deletes records the way the backend does. It checks the permissions of the backend user and applies the TCA. It writes history and log entries, maintains the reference index and clears caches. It does not process fields without TCA.
The datamap contains field values, ordered by table, uid and field. Instead of a uid, a new record gets a placeholder that starts with NEW, such as NEW1. Leave out underscores, because in relation fields TYPO3 reads an underscore as the separator between table and number. The pid tells it on which page the record is created.
$data['tx_myextension_domain_model_talk']['NEW1'] = ['pid' => 12, 'title' => 'New'];
$dataHandler = GeneralUtility::makeInstance(DataHandler::class);
$dataHandler->start($data, []);
$dataHandler->process_datamap();
if ($dataHandler->errorLog !== []) { /* log the errors */ }
$newUid = $dataHandler->substNEWwithIDs['NEW1'];The method process_datamap() returns nothing. The DataHandler collects errors in errorLog, missing permissions included. The new uids are in substNEWwithIDs.
The cmdmap contains commands, ordered by table, uid and command, and runs with process_cmdmap(). Commands include copy, move, delete and localize. New in TYPO3 14 is discard. It discards workspace changes, replaces the old actions clearWSID and flush, and expects the uid of the workspace version. When copying, a positive value is a target page. A negative value is a record of the same table after which the copy is inserted.
$cmd['tx_myextension_domain_model_talk'][5]['delete'] = 1;
$cmd['tt_content'][1203]['copy'] = -303;
$cmd['pages'][$workspaceVersionUid]['discard'] = true;
$dataHandler->start([], $cmd);
$dataHandler->process_cmdmap();Do not set the field deleted yourself via the datamap. That bypasses permissions, history and the reference index.
In your own code, keep this in mind:
- Get a new DataHandler with makeInstance every time, because it keeps state.
- It checks the permissions of the backend user, even when you call it from PHP. In a console command you therefore call the method
initializeBackendAuthentication()of the class Bootstrap beforestart(). It then works as the administrator _cli_. - It is slower than direct SQL, but sorting, reference index and history stay correct. Process large imports in batches.
You hook your own logic on saving into it. Register the class in ext_localconf.php under processDatamapClass or processCmdmapClass.
$GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_tcemain.php']['processDatamapClass'][]
= \MyVendor\MyExtension\Hooks\TalkHook::class;The method processDatamap_preProcessFieldArray() receives the incoming values by reference, before the DataHandler checks and stores them. processDatamap_afterDatabaseOperations() only runs after writing. Hooks only take effect when data is written through the DataHandler.
The system registry
The registry is a small key-value store in the table sys_registry. It stores values permanently under a namespace and a key. Clearing the caches does not remove them.
$this->registry->set('tx_myextension', 'lastImport', time());
$lastImport = $this->registry->get('tx_myextension', 'lastImport', 0);The third argument of get() is the default value. As the namespace you use the extension key with the prefix tx_. It must be at least two characters long, and core is reserved for the Core.
The registry suits small technical states such as the last import. It does not suit settings that editors maintain. Those belong in records with TCA or in site settings. Cached data belongs in a cache, and large amounts of data belong in the file system.
Key points
- TYPO3 14 runs on MySQL, MariaDB, PostgreSQL and SQLite, using Doctrine DBAL and not Doctrine ORM.
- WHERE filters before grouping, HAVING after it. UPDATE and DELETE without WHERE affect all rows.
- In a one to n relationship the foreign key sits on the n side, and m to n needs an MM table.
- Get a new QueryBuilder for every query. Read with
executeQuery(), write withexecuteStatement(). - The DefaultRestrictionContainer hides deleted, hidden and time-controlled records.
- Every input goes through
createNamedParameter(), column names only via a fixed list. - The DataHandler needs a backend user. Errors are in errorLog, new uids in substNEWwithIDs.
Terms
- DBAL
- Database Abstraction Layer, unifies access to different database systems.
- datamap
- Data array of the DataHandler with field values per table and uid.
- cmdmap
- Command array of the DataHandler, for example to copy, delete or discard.
- MM table
- Intermediate table for an m to n relationship.
- Prepared statement
- Query with placeholders whose values are sent separately.
- Reference index
- Table sys_refindex with all references between records and files.
- Restriction
- Condition that the QueryBuilder automatically adds to a query.
- Soft delete
- Deleting by setting the field deleted instead of removing the row.