View Issue Details

This bug affects 1 person(s).
 8
IDProjectCategoryView StatusLast Update
15546Feature requestsCentral participant databasepublic2019-11-18 19:04
Reportermarksim Assigned To 
PrioritynoneSeverityfeature 
Status newResolutionopen 
Summary15546: In order to use JWT as token -> increase field
Description

For authentication purpose at any 3rd party system, the usage of JWT is quite common.
Unfortunately the regarding token field in table limetoken<SID> is limited to character varying (35).
Increase size of token field to VAR_CHAR(1024) and we can import JWT in order to authorize Limesurvey participants at any 3rd party system.
At least for PostgreSQL it's no big issue.

Obstacles expected w/ mysql:
Error: #1071 - Specified key was too long; max key length is 1000 bytes
which means, the key will become too long. Max size was VARCHAR(334).

Solution:
Take only a subset of the JWT as KEY via
KEY idx_token_token_123456 (token(72))
As first 36 char are the header, which will always be the same I've used 36+1+35 = 72 char which (header+dot+35char as we have now)

CREATE TABLE tokens_123456 (
tid int(11) NOT NULL AUTO_INCREMENT,
participant_id varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
firstname varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
lastname varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
email text COLLATE utf8_unicode_ci,
emailstatus text COLLATE utf8_unicode_ci,
token varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
language varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
blacklisted varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
sent varchar(17) COLLATE utf8_unicode_ci DEFAULT 'N',
remindersent varchar(17) COLLATE utf8_unicode_ci DEFAULT 'N',
remindercount int(11) DEFAULT '0',
completed varchar(17) COLLATE utf8_unicode_ci DEFAULT 'N',
usesleft int(11) DEFAULT '1',
validfrom datetime DEFAULT NULL,
validuntil datetime DEFAULT NULL,
mpid int(11) DEFAULT NULL,
PRIMARY KEY (tid),
KEY idx_token_token_123456 (token(72))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

TagsNo tags attached.
Attached Files
Bug heat8
Story point estimate
Users affected %

Users monitoring this issue

conjoint

Activities

marksim

marksim

2019-11-07 19:53

reporter   ~54475

As the blog did some fancy formatting with my input (but didn't tell about using markups), I have attached plain SQL for mysql.

create_token.sql (1,041 bytes)   
CREATE TABLE `tokens_123456` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `participant_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstname` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastname` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` text COLLATE utf8_unicode_ci,
  `emailstatus` text COLLATE utf8_unicode_ci,
  `token` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
  `language` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `blacklisted` varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sent` varchar(17) COLLATE utf8_unicode_ci DEFAULT 'N',
  `remindersent` varchar(17) COLLATE utf8_unicode_ci DEFAULT 'N',
  `remindercount` int(11) DEFAULT '0',
  `completed` varchar(17) COLLATE utf8_unicode_ci DEFAULT 'N',
  `usesleft` int(11) DEFAULT '1',
  `validfrom` datetime DEFAULT NULL,
  `validuntil` datetime DEFAULT NULL,
  `mpid` int(11) DEFAULT NULL,
  PRIMARY KEY (`tid`),
  KEY `idx_token_token_123456` (`token`(72))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
create_token.sql (1,041 bytes)   
f_funke

f_funke

2019-11-07 21:10

developer   ~54476

Thanks for reporting!

conjoint

conjoint

2019-11-18 19:04

reporter   ~54662

at least field length of VARCAR 36 would enable the storage of uuid-4 tokens like 123e4567-e89b-12d3-a456-426655440000

Issue History

Date Modified Username Field Change
2019-11-07 19:48 marksim New Issue
2019-11-07 19:53 marksim File Added: create_token.sql
2019-11-07 19:53 marksim Note Added: 54475
2019-11-07 21:10 f_funke Note Added: 54476
2019-11-18 19:04 conjoint Note Added: 54662
2019-11-18 19:04 conjoint Issue Monitored: conjoint