MySQL query error [1406]: Data too long for column 'template_parsed' at row 1 (Template modification)

[Yenxji]

Affected version

2.3.6

If you are trying to put

/(.*?)/is

on template modification (Find) it can cause take a longer time to pop-up error:

Server error log
[LIST]
[*]XF\Db\Exception: MySQL query error [1406]: Data too long for column 'template_parsed' at row 1
[*]src\XF\Db\AbstractStatement.php:230
[*]Generated by: Test
[*]Mar 14, 2025 at 2:04 PM
[/LIST]
[HEADING=2]Stack trace[/HEADING]
UPDATE  `xf_template` SET `template_parsed` = ? WHERE `template_id` = 7560
------------

#0 src\XF\Db\Mysqli\Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1406, '22001')
#1 src\XF\Db\Mysqli\Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1406, '22001')
#2 src\XF\Db\AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src\XF\Db\AbstractAdapter.php(323): XF\Db\AbstractAdapter->query('UPDATE  `xf_tem...', Array)
#4 src\XF\Mvc\Entity\Entity.php(1577): XF\Db\AbstractAdapter->update('xf_template', Array, '`template_id` =...')
#5 src\XF\Mvc\Entity\Entity.php(1300): XF\Mvc\Entity\Entity->_saveToSource()
#6 src\XF\Entity\TemplateModification.php(101): XF\Mvc\Entity\Entity->save()
#7 src\XF\Entity\TemplateModification.php(157): XF\Entity\TemplateModification->reparseModification()
#8 src\XF\Mvc\Entity\Entity.php(1324): XF\Entity\TemplateModification->_postSave()
#9 src\XF\Mvc\FormAction.php(72): XF\Mvc\Entity\Entity->save(true, false)
#10 src\XF\Mvc\FormAction.php(190): XF\Mvc\FormAction->{closure:XF\Mvc\FormAction::saveEntity():70}(Object(XF\Mvc\FormAction))
#11 src\XF\Admin\Controller\TemplateModificationController.php(131): XF\Mvc\FormAction->run()
#12 src\XF\Mvc\Dispatcher.php(362): XF\Admin\Controller\TemplateModificationController->actionSave(Object(XF\Mvc\ParameterBag))
#13 src\XF\Mvc\Dispatcher.php(264): XF\Mvc\Dispatcher->dispatchClass('XF:TemplateModi...', 'Save', Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\TemplateModificationController), NULL)
#14 src\XF\Mvc\Dispatcher.php(121): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\TemplateModificationController), NULL)
#15 src\XF\Mvc\Dispatcher.php(63): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#16 src\XF\App.php(2826): XF\Mvc\Dispatcher->run()
#17 src\XF.php(806): XF\App->run()
#18 admin.php(15): XF::runApp('XF\\Admin\\App')
#20 {main}
[HEADING=2]Request state[/HEADING]
array(4) {
  ["url"] => string(43) "/admin.php?template-modifications/1247/save"
  ["referrer"] => string(60) "http://domain.test/admin.php?template-modifications/1247/edit"
  ["_GET"] => array(1) {
    ["template-modifications/1247/save"] => string(0) ""
  }
  ["_POST"] => array(14) {
    ["_xfToken"] => string(8) "********"
    ["type"] => string(6) "public"
    ["template"] => string(25) "xfrm_resource_add_chooser"
    ["modification_key"] => string(43) "test"
    ["description"] => string(44) "test"
    ["action"] => string(12) "preg_replace"
    ["find"] => string(9) "/(.*?)/is"
    ["replace"] => string(1694) "<xf:title>{{ phrase('xfrm_post_resource_in') }}</xf:title>

<div class="block">
</div>

But

/^.*$/s

works perfectly. This should prevent errors and longer time.

Following from this one: https://xenforo.com/community/threa...using-template-modification.55395/post-590423
and

XF 1.2 Post in thread 'How to replace all the contents in a template using Template Modification'

Apr 20, 2015

I use this to replace all content in a template:

Find: /^.*$/s
Replace: <xen:comment></xen:comment>

• AndyB

• 

@AndyB @Chris D @Jeremy P

 

[Yenxji]

Dear @Chris D @Jeremy P


I have fixed and improved validation in src\XF\Entity\TemplateModification.php, A good approach would be strengthening the checks in the _preSave() method within the TemplateModification class. This will stop regex patterns that can cause create excessively large outputs problem.

Before:

    protected function _preSave()
    {
        if (($this->action == 'preg_replace' || $this->action == 'callback') && $this->find)
        {
            if (preg_match('/\W[\s\w]*e[\s\w]*$/', $this->find))
            {
                // can't run a /e regex
                $this->error(\XF::phrase('please_enter_valid_regular_expression'), 'find');
            }
            else
            {
                try
                {
                    preg_replace($this->find, '', '');
                }
                catch (\ErrorException $e)
                {
                    $this->error(\XF::phrase('please_enter_valid_regular_expression'), 'find');
                }
            }
        }
        if ($this->action == 'callback' && ($this->isChanged(['replace', 'action']) || $this->isChanged('action')))
        {
            if (preg_match('/^([a-z0-9_\\\\]+)::([a-z0-9_]+)$/i', $this->replace, $match))
            {
                if (!Php::validateCallbackPhrased($match[1], $match[2], $errorPhrase))
                {
                    $this->error($errorPhrase, 'replace');
                }
            }
            else
            {
                $this->error(\XF::phrase('please_enter_valid_callback_method'), 'replace');
            }
        }
    }

After (Fixed):

    protected function _preSave()
    {
        if (($this->action == 'preg_replace' || $this->action == 'callback') && $this->find)
        {
            if (preg_match('/\W[\s\w]*e[\s\w]*$/', $this->find))
            {
                $this->error(\XF::phrase('please_enter_valid_regular_expression'), 'find');
            }
            else
            {
                if (preg_match('~/\(.*?\)/is~', $this->find) || strpos($this->find, '/(.*?)/') !== false)
                {
                    $this->error(\XF::phrase('potentially_unsafe_regex_pattern_can_cause_database_errors'), 'find');
                }
              
                try
                {
                    preg_replace($this->find, '', '');
                }
                catch (\ErrorException $e)
                {
                    $this->error(\XF::phrase('please_enter_valid_regular_expression'), 'find');
                }
            }
        }
      
        if ($this->action == 'callback' && ($this->isChanged(['replace', 'action']) || $this->isChanged('action')))
        {
            if (preg_match('/^([a-z0-9_\\\\]+)::([a-z0-9_]+)$/i', $this->replace, $match))
            {
                if (!Php::validateCallbackPhrased($match[1], $match[2], $errorPhrase))
                {
                    $this->error($errorPhrase, 'replace');
                }
            }
            else
            {
                $this->error(\XF::phrase('please_enter_valid_callback_method'), 'replace');
            }
        }
    }

Add/Create phrase to XenForo system self:

<phrase title="potentially_unsafe_regex_pattern_can_cause_database_errors" version_id="1000010" version_string="1.0.0"><![CDATA[The pattern you are trying to have causes database errors due to excessive template size. Please use more specific patterns and avoid using /(.*?)/is carefully.]]></phrase>

Add phrase,

Title: potentially_unsafe_regex_pattern_can_cause_database_errors
Phrase text: The pattern you are trying to have causes database errors due to excessive template size. Please use more specific patterns and avoid using /(.*?)/is carefully.

 

[Yenxji]

An optional increase in database column size can also help template_parsed

ALTER TABLE `xf_template` MODIFY `template_parsed` MEDIUMTEXT

This may and will change the column from TEXT (Maximum is 65.535 bytes) to MEDIUMTEXT (Maximum is 16.777.215 bytes), because that would accommodate larger parsed templates more. But this approach could affect database performance as well.

This is a 2nd method, it's up to you @Chris D , @Jeremy P