Error when searching for <script> with fulltext index enabled

Started by Tomcraft, September 19, 2019, 07:32:55 PM

Previous topic - Next topic

Tomcraft

Hi,

we noticed an error when the search fulltext index is enabled and someone types <script> in the search field and hits enter.

syntax error, unexpected '<'
File: /.../.../.../.../.../.../.../.../.../Sources/SearchAPI-Fulltext.php
Line: 234


Any suggestions how to fix this?


Arantor


Tomcraft

Thanks for your reply.

SMF 2.0.15
10.1.41-MariaDB-0+deb9u1 - Debian 9.9

Arantor


Tomcraft

You can reproduce this in a fresh install activating the full text search index and after that convert at least the smf_messages table to InnoDB.
It doesn't happen with MyISAM.

I found this out trying to reproduce the error in a test install.
Funny thing besides: The installer produces an error:
QuoteYour settings have now been saved and the database has been populated with all the data required to get your forum up and running. Summary of population:

    Created 62 tables.
    Inserted 777 rows.

Some of the queries were not executed properly. This could be caused by an unsupported (development or old) version of your database software.

Technical information about the queries:

    Line #1338:
    Line #1346:

Click "Continue" to progress to the admin account creation page.

The correspondig lines in "install_2-0_mysql.sql" are:
#
# Table structure for table `messages`
#

CREATE TABLE {$db_prefix}messages (
  id_msg int(10) unsigned NOT NULL auto_increment,
  id_topic mediumint(8) unsigned NOT NULL default '0',
  id_board smallint(5) unsigned NOT NULL default '0',
  poster_time int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  id_msg_modified int(10) unsigned NOT NULL default '0',
  subject varchar(255) NOT NULL default '',
  poster_name varchar(255) NOT NULL default '',
  poster_email varchar(255) NOT NULL default '',
  poster_ip varchar(255) NOT NULL default '',
  smileys_enabled tinyint(4) NOT NULL default '1',
  modified_time int(10) unsigned NOT NULL default '0',
  modified_name varchar(255) NOT NULL default '',
  body text NOT NULL,
  icon varchar(16) NOT NULL default 'xx',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_msg),
  UNIQUE topic (id_topic, id_msg),
  UNIQUE id_board (id_board, id_msg),
  UNIQUE id_member (id_member, id_msg),
  KEY approved (approved),
  KEY ip_index (poster_ip(15), id_topic),
  KEY participation (id_member, id_topic),
  KEY show_posts (id_member, id_board),
  KEY id_topic (id_topic),
  KEY id_member_msg (id_member, approved, id_msg),
  KEY current_topic (id_topic, id_msg, id_member, approved),
  KEY related_ip (id_member, poster_ip, id_msg)
) ENGINE=MyISAM;

#
# Dumping data for table `messages`
#

INSERT INTO {$db_prefix}messages
(id_msg, id_msg_modified, id_topic, id_board, poster_time, subject, poster_name, poster_email, poster_ip, modified_name, body, icon)
VALUES (1, 1, 1, 1, UNIX_TIMESTAMP(), '{$default_topic_subject}', 'Simple Machines', '[email protected]', '127.0.0.1', '', '{$default_topic_message}', 'xx');
# --------------------------------------------------------


When using phpMyAdmin to send the SQL-commands I receive the error:
CREATE TABLE smf_messages (
  id_msg int(10) unsigned NOT NULL auto_increment,
  id_topic mediumint(8) unsigned NOT NULL default '0',
  id_board smallint(5) unsigned NOT NULL default '0',
  poster_time int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  id_msg_modified int(10) unsigned NOT NULL default '0',
  subject varchar(255) NOT NULL default '',
  poster_name varchar(255) NOT NULL default '',
  poster_email varchar(255) NOT NULL default '',
  poster_ip varchar(255) NOT NULL default '',
  smileys_enabled tinyint(4) NOT NULL default '1',
  modified_time int(10) unsigned NOT NULL default '0',
  modified_name varchar(255) NOT NULL default '',
  body text NOT NULL,
  icon varchar(16) NOT NULL default 'xx',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_msg),
  UNIQUE topic (id_topic, id_msg),
  UNIQUE id_board (id_board, id_msg),
  UNIQUE id_member (id_member, id_msg),
  KEY approved (approved),
  KEY ip_[...]

MySQL meldet: Dokumentation
#1071 - Specified key was too long; max key length is 1000 bytes


Any ideas now?

Arantor

FTS is not supported on InnoDB as far as SMF is concerned, it should even tell you this if you try to make the FTS index.

Tomcraft

1.) We converted the tables afterwards. You are right that SMF dows not give the option to create the full text index when the table "smf_messages" is converted to InnoDB before. You need to convert it afterwards.

The thing ist that MariaDB is capable of FTS on InnoDB tables since version 10.0.5, see: https://mariadb.com/kb/en/library/full-text-index-overview/

2.) Do you have an idea on the install problem of table smf_messages (#1071 - Specified key was too long; max key length is 1000 bytes)? That was really confusing when trying to setup a clean test installation of SMF 2.0.15.

Arantor

Yes, because it isn't supported.

As for the install issue, no, I can't see why - I can't see which indexes would be that big.

Tomcraft

Ok thanks anyways. Hope to see InnoDB support in near future. ;)

Arantor

Won't happen in 2.0, and I don't know if it's been done in 2.1 especially as there have been better options available for years (namely Sphinx, and if anyone's done ElasticSearch yet)

Tomcraft

We'd love to use sphinx, but it currently still lacks the ability to update the search index upon new message creation and the last commit on the API is two years ago. :-\

Arantor

Given that SMF 2.0 doesn't support pushing updates to the real time index anyway (because fundamentally 2.0's search API can't support it, even though Sphinx does)...

Unless you have a site where you need to be able to search posts within minutes of them being made (not common) and you have multiple millions of posts (also not so common), Sphinx should be able to do the job, it does it here just fine.


Arantor



Advertisement: