Precise Similar Topics

SQL Error - Precise Similar Topics

SQL Error

by agaveville » Sun Nov 29, 2020 12:57 am

I love this extension, but find I am unable to use it because certain topics (a very small percentage) trigger an SQL error. I have v3.3.2.

The error below seems to be generated by the thread starter's improper punctuation.

Thread title is: What Does 'Compact" Really Mean?

When I edit it to: What Does Compact Really Mean?, problem solved. So to speak.

Any help would be appreciated.
General Error
SQL ERROR [ postgres ]

ERROR: syntax error in tsquery: "What|Does|'Compact|Really|Mean?" []

SQL

SELECT f.forum_id, f.forum_name, t.*, ts_rank_cd('{1,1,1,1}', to_tsvector('simple', t.topic_title), to_tsquery('simple', 'What|Does|''Compact|Really|Mean?'), 32) AS score, tt.mark_time, ft.mark_time as f_mark_time FROM phpbb_topics t LEFT JOIN phpbb_forums f ON (f.forum_id = t.forum_id) LEFT JOIN phpbb_topics_track tt ON (tt.topic_id = t.topic_id AND tt.user_id = 2) LEFT JOIN phpbb_forums_track ft ON (ft.forum_id = f.forum_id AND ft.user_id = 2) WHERE to_tsquery('simple', 'What|Does|''Compact|Really|Mean?') @@ to_tsvector('simple', t.topic_title) AND ts_rank_cd('{1,1,1,1}', to_tsvector('simple', t.topic_title), to_tsquery('simple', 'What|Does|''Compact|Really|Mean?'), 32) >= 0.5 AND t.topic_status <> 2 AND t.topic_visibility = 1 AND t.topic_time > (extract(epoch from current_timestamp)::integer - 189216000) AND t.topic_id <> 10531 AND f.similar_topics_ignore = 0 ORDER BY score DESC, t.topic_time DESC LIMIT 5 OFFSET 0

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 1020
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/postgres.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/postgres.php
LINE: 257
CALL: phpbb\db\driver\postgres->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 296
CALL: phpbb\db\driver\postgres->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 337
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/ext/vse/similartopics/core/similar_topics.php
LINE: 240
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: [ROOT]/ext/vse/similartopics/event/listener.php
LINE: 63
CALL: vse\similartopics\core\similar_topics->display_similar_topics()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 214
CALL: vse\similartopics\event\listener->display_similar_topics()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 44
CALL: Symfony\Component\EventDispatcher\EventDispatcher->doDispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 62
CALL: Symfony\Component\EventDispatcher\EventDispatcher->dispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 46
CALL: phpbb\event\dispatcher->dispatch()

FILE: [ROOT]/viewtopic.php
LINE: 2417
CALL: phpbb\event\dispatcher->trigger_event()
agaveville
Registered User
Posts: 48
Joined: Fri Nov 13, 2020 6:19 pm

Re: SQL Error

by MattF » Sun Nov 29, 2020 1:30 am

The simple solution for now is to add the ' character to Similar Topics - Special words to ignore setting.
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 6097
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: SQL Error

by agaveville » Sun Nov 29, 2020 1:41 am

Thanks, that did it. I will apply that to any similar issues that crop up in the future.
agaveville
Registered User
Posts: 48
Joined: Fri Nov 13, 2020 6:19 pm

Re: SQL Error

by neptronix » Fri Feb 05, 2021 1:10 am

Sounds like there's some possibility to do SQL injection on this mod by crafting a special title name, if it can't handle ' characters.
neptronix
Registered User
Posts: 12
Joined: Fri Jul 06, 2018 3:53 pm

Re: SQL Error

by MattF » Fri Feb 05, 2021 1:14 am

neptronix wrote:Sounds like there's some possibility to do SQL injection on this mod by crafting a special title name, if it can't handle ' characters.
No. It handles quotes just fine. The problem was postgresql databases don't handle quotes in titles the way every other database does. And this has already been fixed in the latest release.
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 6097
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman