View Issue Details

This bug affects 1 person(s).
 12
IDProjectCategoryView StatusLast Update
18275Bug reportsSurvey takingpublic2024-09-11 12:27
Reporterritapas Assigned To 
PrioritynoneSeverityminor 
Status confirmedResolutionopen 
Product Version3.28.x 
Summary18275: Long free text question cannot handle excessively long input
Description

When a user fills a Long free text question, they have no idea of the actual limit of the cell unless the designer put one. However, the designers themselves have no idea about that.
In our configuration, if a user puts a text longer than 60.000 characters as a response, the system crasches without being able to catch the exception and recovery from that.
We acknowledge the limit should be found in the DB settings but we believe the application could catch the exception.

Steps To Reproduce

Steps to reproduce

create simple survey with example (pre-given) group and question. Activate. In the answer put a big series of characters (we saw that 60k is ok while 70k is not). Submit the answer.

Expected result

Response should be acquired or rejected with an error mesage.

Actual result

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column '745942X3X3' at row 1. The SQL statement executed was: UPDATE survey_745942 SET lastpage=:yp0, 745942X3X3=:yp1 WHERE survey_745942.id=17

/var/www/html/limesurvey3/framework/db/CDbCommand.php(358)

TagsNo tags attached.
Attached Files
immagine.png (187,399 bytes)
Bug heat12
Complete LimeSurvey version number (& build)3.28.14 (build 220608)
I will donate to the project if issue is resolvedNo
BrowserMozilla Firefox
Database type & versionmariadb-10.3.32-2
Server OS (if known)Red Hat Enterprise Linux release 8.5 (Ootpa)
Webserver software & version (if known) httpd-2.4.37
PHP Versionphp-7.2.24

Relationships

related to 19741 assignedp_teichmann Out of memory because of adding inline images in questoins/questionGroups description. 

Users monitoring this issue

ritapas

Activities

gabrieljenik

gabrieljenik

2022-07-28 19:12

manager   ~71235

Related? 18187

Mazi

Mazi

2022-10-11 15:11

updater   ~72197

Should be pretty simple to just limit the input for different text types according to the DB limits:
https://www.w3schools.com/tags/att_textarea_maxlength.asp

DenisChenu

DenisChenu

2022-10-11 15:23

developer   ~72198

Last edited: 2022-10-11 15:24

Related? 18187

Not really since this one broke and the previous one not …

Should be pretty simple to just limit the input for different text types according to the DB limits:

See https://github.com/LimeSurvey/LimeSurvey/blob/e3efd7fb1771e39d18396315fa7b2be56b78e800/application/helpers/expressions/em_manager_helper.php#L9832
Not really … because depend on DB a lot.
And each have own specificity (MSSQL accept 8Mo for whole lines … but text data can take between 8 and 24 BYTES … really complex)

Else : https://gitlab.com/SondagesPro/ExportAndStats/generateGraphQuestion/-/blob/master/generateGraphQuestion.php#L521

gabrieljenik

gabrieljenik

2022-10-18 16:24

manager   ~72333

@c_schmitz Maybe we can set an arbitrary number considering the minimum size that the different DB engines can handle. What do you think?

gabrieljenik

gabrieljenik

2022-10-18 16:26

manager   ~72334

Or, as MySQL (and Postgre?) can handle 65k in text fields, we can set that as that as a limit, when using MySQL engine.
If not, we set no limits, as other engines handle really big limits.

What do you think?

DenisChenu

DenisChenu

2022-10-18 16:27

developer   ~72335

Maybe we can set an arbitrary number considering the minimum size that the different DB engines can handle. What do you think?

Please : don't broke plugin … for mysql need to check column type (TEXT (LimeSurvey default) vs MEDIUMTEXT vs LONGTEXT)
Surely for PGSQL too
Unsure for MSSQL

gabrieljenik

gabrieljenik

2022-10-18 16:32

manager   ~72336

Please : don't broke plugin

What kind of comment is that?

I was thinking on a limit on the html control, not on an update to the DB

c_schmitz

c_schmitz

2022-10-18 16:37

administrator   ~72339

@gabrieljenik Set the limit depending on the database type.

DenisChenu

DenisChenu

2022-10-18 16:43

developer   ~72340

What kind of comment is that?

If a plugin or an user update TEXT to LONGTEXT : you need more :) TEXT is the default

I was thinking on a limit on the html control, not on an update to the DB

Not in checkValidityAnswer ? Seems the best place ?
If HTML : no issue for plugin, only for hacked database.

@c_schmitz

Set the limit depending on the database type.

database AND column type : right ?

DenisChenu

DenisChenu

2022-10-18 16:44

developer   ~72341

Or, as MySQL (and Postgre?) can handle 65k in text fields, we can set that as that as a limit, when using MySQL engine.

TEXT : 65k
MEDIUMTEXT : 16,777,215
LONGTEXT : 4,294,967,295

gabrieljenik

gabrieljenik

2022-10-25 19:00

manager   ~72419

Last edited: 2022-10-25 19:04

Please find below some analysis done.

Renderer Part:

  • Need to make the HTML control not to allow more than XX amount of characters
  • Use Maxlength question attribute
  • Set default (or validate it is not more than a maximum)
  • Review renderers (ex: application/core/QuestionTypes/HugeFreeText/RenderHugeFreeText.php)

Calculating Max Amount:

  • How shall we get the "max amount of characters"?

    • Input: question type, DB engine
    • Output: Max Size
    • It is necessary to know the codePage of the DB --> Config File?
  • We can split the provess:

    1) Question Type --> Field Type
    2) Field Type + engine --> Max Size

  • Question Type --> Field Type

    • Translates from question type to field size
    • New method on QuestionType class ?
    • That logic today is embedded in the prepareTableDefinition of the SurveyActviator
      • Needs code reorganization as to reuse parts of the code
      • Already considers quesiton themes, which can redefine the field type
  • Field Type + engine --> Max Size

    • application/helpers/database_helper.php (or similar)
gabrieljenik

gabrieljenik

2022-10-25 19:01

manager   ~72420

Also, if validation side is also needed:

Validation Part (not required, but may be good idea):

  • Need to make the validation not to accept more than XX amount of characters
  • Review LimeExpressionManager::checkValidityAnswer()
  • Actually the Maxlength is not considered there, so need to implement the rule.
DenisChenu

DenisChenu

2022-10-25 19:09

developer   ~72421

Validation Part (not required, but may be good idea):

? Server side validation is always required … 

DenisChenu

DenisChenu

2022-10-25 19:10

developer   ~72422

PS in checkValidityAnswer : we already have the sgq, then the column name for some question type : we can directly use it DB type.

gabrieljenik

gabrieljenik

2022-10-25 20:37

manager   ~72423

Last edited: 2022-10-25 20:39

PS in checkValidityAnswer : we already have the sgq, then the column name for some question type : we can directly use it DB type.

Yes, but you would have to add 1 query for every question... isn't that something to avoid if possible taking into account that is the survey taking process?

If not, create some extra metadata on survey activate.
That metadata can hold each field max sixe.
Then on a single query, get all field sizes.

DenisChenu

DenisChenu

2022-10-26 08:20

developer   ~72424

If not, create some extra metadata on survey activate.

Maybe more in $knownVars variable ?
https://github.com/LimeSurvey/LimeSurvey/blob/86ada39411e982eb43ce2f053fd82f9f95b439e2/application/helpers/expressions/em_manager_helper.php#L119

  1. It was in session : https://github.com/LimeSurvey/LimeSurvey/blob/86ada39411e982eb43ce2f053fd82f9f95b439e2/application/helpers/expressions/em_manager_helper.php#L3279
  2. can be extended : https://github.com/LimeSurvey/LimeSurvey/blob/86ada39411e982eb43ce2f053fd82f9f95b439e2/application/helpers/expressions/em_manager_helper.php#L4012

or in getQuestionAttributesForEM ? Since it's enter in EMCache

About knownVars : except static var : i think all other can enter in EMCache in future

ritapas

ritapas

2023-02-10 13:23

reporter   ~73796

I see this problem is also present in my 5.4.14 installation, the only difference is that it is triggered by a larger input. I succeeded with a 650K chars input

c_schmitz

c_schmitz

2023-06-16 10:47

administrator   ~75663

Last edited: 2023-06-16 10:48

OK, if setting the limit depending on the database type is too hard, how about we focus on gracefully catching the error., then?

c_schmitz

c_schmitz

2024-09-04 16:15

administrator   ~80899

For the Long/huge free text

  • MySQL uses the TEXT field type, which can store 64kb bytes (NOT characters, as many UTF-8 chars use more than 1 byte)
  • MSSQL uses nvarchar(max) which is 2 Gigabytes
  • Postgres uses TEXT which is basically unlimited
DenisChenu

DenisChenu

2024-09-04 16:32

developer   ~80900

Last edited: 2024-09-04 16:33

Maybe for MYSQL/MARIADB we can use LONGTEXT https://mariadb.com/kb/en/longtext/ for Long text question and/or Huge text question ?
4GB ?

c_schmitz

c_schmitz

2024-09-11 12:12

administrator   ~80953

I think 64kb for a long text is a bit too short. while 4GB is way too large.

Suggestion:

  • we set it to MEDIUMTEXT in MYSQL (16MB max)
  • we limit entry to the same maximum limit of 10MB for all database types
  • have a question type attribute that is default set to 1MB, and an option in the question setting to change it (up to 10MB)
Mazi

Mazi

2024-09-11 12:26

updater   ~80955

What's the benefit of the question attribute? I think we can skip this. Why should be offer a limitation of 10% of what is theoretically possible? I think there is no real use case for this (correct me if I am wrong).

DenisChenu

DenisChenu

2024-09-11 12:27

developer   ~80956

I think 64kb for a long text is a bit too short. while 4GB is way too large.

Why not 4GB for Huge text ?

Short text : TEXT
Long text : MEDIUM
Huge : LONG

Issue History

Date Modified Username Field Change
2022-07-26 14:34 ritapas New Issue
2022-07-26 14:34 ritapas File Added: immagine.png
2022-07-28 18:56 gabrieljenik Status new => confirmed
2022-07-28 19:12 gabrieljenik Note Added: 71235
2022-07-28 19:12 gabrieljenik Bug heat 0 => 2
2022-10-11 08:59 ritapas Issue Monitored: ritapas
2022-10-11 08:59 ritapas Bug heat 2 => 4
2022-10-11 15:11 Mazi Note Added: 72197
2022-10-11 15:11 Mazi Bug heat 4 => 6
2022-10-11 15:23 DenisChenu Note Added: 72198
2022-10-11 15:23 DenisChenu Bug heat 6 => 8
2022-10-11 15:24 DenisChenu Note Edited: 72198
2022-10-18 16:24 gabrieljenik Note Added: 72333
2022-10-18 16:26 gabrieljenik Note Added: 72334
2022-10-18 16:27 DenisChenu Note Added: 72335
2022-10-18 16:32 gabrieljenik Note Added: 72336
2022-10-18 16:37 c_schmitz Note Added: 72339
2022-10-18 16:37 c_schmitz Bug heat 8 => 10
2022-10-18 16:43 DenisChenu Note Added: 72340
2022-10-18 16:44 DenisChenu Note Added: 72341
2022-10-25 19:00 gabrieljenik Note Added: 72419
2022-10-25 19:01 gabrieljenik Note Added: 72420
2022-10-25 19:03 gabrieljenik Note Edited: 72419
2022-10-25 19:04 gabrieljenik Note Edited: 72419
2022-10-25 19:09 DenisChenu Note Added: 72421
2022-10-25 19:10 DenisChenu Note Added: 72422
2022-10-25 20:37 gabrieljenik Note Added: 72423
2022-10-25 20:39 gabrieljenik Note Edited: 72423
2022-10-26 08:20 DenisChenu Note Added: 72424
2023-02-10 13:23 ritapas Note Added: 73796
2023-02-10 13:23 ritapas Bug heat 10 => 12
2023-06-16 10:47 c_schmitz Note Added: 75663
2023-06-16 10:47 c_schmitz Note Edited: 75663
2023-06-16 10:48 c_schmitz Note Edited: 75663
2024-09-04 16:15 c_schmitz Note Added: 80899
2024-09-04 16:32 DenisChenu Note Added: 80900
2024-09-04 16:33 DenisChenu Note Edited: 80900
2024-09-10 15:52 DenisChenu Relationship added related to 19741
2024-09-11 12:12 c_schmitz Note Added: 80953
2024-09-11 12:26 Mazi Note Added: 80955
2024-09-11 12:27 DenisChenu Note Added: 80956