Некоторые бинарные строки не определяются по оператору LIKE, проблема не дебажится, в чём может быть причина?

Question

[Игорь]

Опишу проблему максимально подробно.

В базе есть два столбца двоично закодированных строк инфо-хешей (BitTorrent), один из них 20 байт (sha1 хеш), второй равен 40 байтам (sha2).
Из-за кривой реализации в протоколе, клиенты отправляют sha2 хеш тоже в усечённом виде до 20 байт, вместо 40.

Код запроса (разбит для понимания):

$info_hash = rtrim(DB()->escape($info_hash), ' ');

$info_hash_where = "WHERE tor.info_hash = '$info_hash' OR tor.info_hash_v2 LIKE '$info_hash%'";

$sql = "
    SELECT tor.topic_id, tor.poster_id, tor.tor_type, tor.info_hash, tor.info_hash_v2, u.*
    FROM " . BB_BT_TORRENTS . " tor
    LEFT JOIN " . BB_BT_USERS . " u ON u.auth_key = '$passkey_sql'
    $info_hash_where
    LIMIT 1
";
$row = DB()->fetch_row($sql);

Что интересно, что запрос отлично работает с sha1, с sha2 проблем тоже нет, но на некоторых специфичных sha2 усечённых до 20 байт наблюдаются проблемы.
Сверял строки, отправляемые данные, но запрос всё равно не идёт.
Строки отправляются url закодированном виде, PHP автоматом их помещает в массив в исправном виде, с ними проблем вообще нет, иначе бы ничего не работало.

Если дать полную строку (40 байт) для поиска без LIKE то всё нормально, проблема именно с укороченным вариантом.

Вот пример sha2 строк:

Полный: 97978479e7eba00af6f24953f6b94b229b556627167b710472ad4c9bc4ceac41 // Найден

Укороченный: 97978479e7eba00af6f24953f6b94b229b556627 // Найден

Закодированный url: %97%97%84y%e7%eb%a0%0a%f6%f2IS%f6%b9K%22%9bUf%27

Проблема:

Полный: b5c69a98c0235cc2af0fa0c956a2984dc10410b1ab0eee46c9cb0c876d0c5189 // Найден

Укороченный: b5c69a98c0235cc2af0fa0c956a2984dc10410b1 // НЕ НАЙДЕН!

Закодированный url: %b5%c6%9a%98%c0%23%5c%c2%af%0f%a0%c9V%a2%98M%c1%04%10%b1

Answer 1

[Daemon23RUS]

"WHERE tor.info_hash = '$info_hash' OR SUBSTRING(tor.info_hash_v2, 1,20)= '$info_hash'"

что то вроде такого.
SUBSTRING- может подойдет для бинарных данных, не уверен, но Вам этот способ проверить проще. Помню, где то применял подобную конструкцию. Возможно будет решением Вашей проблемы.

Comments

[Игорь]

Ничего себе, заработало, спасибо большое!
А это будет использовать индекс не знаете?

[Daemon23RUS]

Игорь, Так Вы сами можете это проверить, добавьте в начале запроса EXPLAIN и посмотрите что возвращается в поле Extra
Мне сложно сказать не видя как у Вас организована БД
P.S. Ведь и индекс можно задать нужной длины, а вообще это вопрос оптимизации, а у вас куда более серьезные проблемы с кодом, обратите внимание на комментарии коллег в части подготовленных запросов.

[Игорь]

Я знаю, но переписывать все sql запросы это уже сложно, движок слишком большой, поэтому приходится выкручиваться пока функциями escape.
Самое интересное, как substring заработал, а LIKE нет.

В Extra возвращается "using where":
array(10) {
["id"]=>
string(1) "1"
["select_type"]=>
string(6) "SIMPLE"
["table"]=>
string(3) "tor"
["type"]=>
string(3) "ALL"
["possible_keys"]=>
string(20) "hashes,hashes22,gdfg"
["key"]=>
NULL
["key_len"]=>
NULL
["ref"]=>
NULL
["rows"]=>
string(1) "2"
["Extra"]=>
string(11) "Using where"
}

[Daemon23RUS]

["possible_keys"]=> string(20) "hashes,hashes22,gdfg"
["key"]=> NULL

возможные индексы hashes,hashes22,gdfg
использованные индексы NULL
Ответ: индекс в обработке запроса не участвует
Сколько записей в таблице (какой размер базы), может есть смысл добавить поле с обрезанным хешем, чтобы не потрошить легаси, а в базу добавить хранимую процедуру, которая при Insert будет заполнять это поле ?

[Daemon23RUS]

Игорь,

Самое интересное, как substring заработал, а LIKE нет.

Причина в неявном приведении типа, последовательность байтов может выглядеть как строка "^@%&^#%" + UTF-8, а сравнение идет с красивым текстовым хешем.

[Игорь]

Daemon23RUS, Спасибо, есть возможность указать на какие-то оптимизации?

[Daemon23RUS]

Игорь, не понял вопроса

[Daemon23RUS]

Игорь, Создайте триггер BEFORE INSERT. Который выполняется до запуска триггерного оператора (INSERT), позволяет вам манипулировать данными, которые будут переданы в базу данных до того, как это произойдет.

CREATE TRIGGER trunc_hash BEFORE INSERT ON tor
FOR EACH ROW
SET NEW.info_hash_truncated = SUBSTRING(NEW.info_hash_v2, 1,20);

Адаптируйте под свою ситуацию
Это к варианту если решите добавлять поле. Ваше легаси продолжит вставлять данные как обычно, а заботы о хранении обрезанного хеша, его индексации лягут на базу. Новые участки кода смогут использовать это поле. И да, при создании этого поля придется пробежаться по базе и внести обрезанный.

[Игорь]

Daemon23RUS, Все таки секретом остается почему это не работало и меня это напрягает.

Вы сказали, что причина в неявном приведении типов, но оба хеша, что в базе, что в сравнении - бинарные, может я что-то недопонял, можете ещё раз объяснить?

[Daemon23RUS]

Игорь, сравнение текстового с бинарным приведенным к текстовому, и вот в этом самом приведении может быть такая последовательность байт, которая адекватно в текст не перевелась (неполная UTF-8 последовательность, которая продолжается например на 21 и/или 22й байт )
а в случае с substr сравнение бинарное

[Игорь]

Daemon23RUS, Значит оператор LIKE, прежде чем начать поиск, конвертирует строку в читабельный вид?
Теперь понятно, спасибо.

Answer 2

[Akina]

LIKE в принципе не пригоден для указанной операции. Это чисто текстовый оператор. В частности, он безусловно применяет к обоим операндам регистронезависимый collation - в этих условиях сравнение БИНАРНЫХ строк способно давать весьма неожиданные эффекты.

Если надо работать именно с бинарными строками - всё плохо. В MySQL нет нормальных функций для работы с ними.

Для решения задачи рекомендую преобразовать бинарную строку в шестнадцатеричное представление, укоротить до 40-символьного (что соответствует 20-байтовому бинарному значению), преобразовать обратно, и сравнить.

WHERE CONV(LEFT(HEX('$entered_hash'), 40), 16, 10) = correct_hash

К тому же такое условие SARGable.

PS. В выражении

WHERE '{value1}' = '{value2}' OR '{value1}' LIKE '{value2}%'

первое условие заведомо лишнее (ведь если оно выполняется, то и второе тоже выполняется).

Comments

[Игорь]

Спасибо за ответ, дело в том что реализация была написана несколько лет назад (не мной), в далеком 2013ом, и ею пользуются многие, если даже отправлять новые данные в базу в виде текстового hex, то возможность находки предыдущих бинарных будет потеряна, что интересно с предыдущим sha1 такого никогда не было.
Запрос с OR мы делаем, потому что мы не знаем версию sha которая нам отправляется торрент клиентами (её просто не отличить). Sha2, которая обрезается, по длине не отличается от sha1.

Помогите чем можете.

[Akina]

Игорь, по-моему, Вы просто не поняли моего ответа. Если описание проблемы соответствует самОй проблеме, то Вам дано практически готовое решение. В чём ещё помогать-то?

[Игорь]

Akina, Опишу ситуацию по-подробнее.

Битторрент трекер - сервер, который просто отвечает списком ip адресов клиентов.
Какой именно список он узнает по отправленному хешу в GET параметре.
Эта строка пристутствует в двух форматах.
v1 (2004 год), где используется строка с хешированием sha1, и v2 (2021), где используется улучшенный по безопасности sha2.
Разработчики протокола пошли по пути обратной совместимости (коряво как по мне), так как трекеры по умолчанию на хеш больше чем 20 байтов отвечают ошибкой, программное обеспечение просто не в курсе существования v2 на 40 байтов (64 символа).
Они обрезают эту sha2 строку (64 символа до 40 символов и отправляют на трекер в бинарном виде - 20 байтов).
Т.е., я как разработчик просто не знаю v1 хеш это или v2, и мне приходится сверять их обоих в базе с полными хешами.
Я не могу обрезать хеш как здесь:
WHERE CONV(LEFT(HEX('$entered_hash'), 40), 16, 10)
Так как попросту не имею его полного значения.

[Akina]

Игорь, Гм... то есть у тебя хранится полный хэш, а приходит обрубок? ну тогда надо просто делать наоборот.

WHERE '$entered_hash' = CONV(LEFT(HEX(correct_hash), 40), 16, 10)

Но такая фигня не станет использовать индекс. Решение - вычисляемое поле и индекс по нему:

ALTER TABLE tablename
ADD COLUMN hash20 BINARY(20) AS (CONV(LEFT(HEX(correct_hash), 40), 16, 10)) VIRTUAL INVISIBLE,
ADD INDEX idx_hash20 (hash20);

И соответственно
WHERE '$entered_hash' = hash20

[Игорь]

SUBSTRING помог:

WHERE tor.info_hash_v2 = '$info_hash_sql'" : "WHERE tor.info_hash = '$info_hash_sql' OR SUBSTRING(tor.info_hash_v2, 1, 20) = '$info_hash_sql'

Не в курсе будет ли это использовать индекс

[Игорь]

Самый главный вопрос здесь - как?!

[Akina]

Игорь,

будет ли это использовать индекс

Крайне сомнительно.

Answer 3

[Freeman]

Из-за кривой реализации в протоколе, клиенты отправляют sha2 хеш тоже в усечённом виде до 20 байт, вместо 40.

Это не кривая реализация, а гибридный торрент. Всё по стандарту.

Полный: b5c69a98c0235cc2af0fa0c956a2984dc10410b1ab0eee46c9cb0c876d0c5189 // Найден
Укороченный: b5c69a98c0235cc2af0fa0c956a2984dc10410b1 // НЕ НАЙДЕН!
Закодированный url: %b5%c6%9a%98%c0%23%5c%c2%af%0f%a0%c9V%a2%98M%c1%04%10%b1

Вы фигню какую-то делаете. В закодированном URL указан двоичный хеш, а вы его преобразовываете в незнамо что. Используйте тип binary или varbinary в БД, раскодируйте хеш и храните как есть.

Плюс обязательно подготовленные запросы, полностью согласен с alexalexes.

Answer 4

[alexalexes]

Вероятно, проблема в состоянии индекса на поле info_hash_v2 (вы же используете индексы в базе для ускорения поиска?). Запустите перерасчет индекса для устранения глюка.

PS:

$info_hash_where = "WHERE tor.info_hash = '$info_hash' OR tor.info_hash_v2 LIKE '$info_hash%'";

Ай-ай-ай. Склейка параметров запроса с текстом запроса, ой не хорошо. Не хотите познакомиться с подготовленными запросами?
Или нормально исследуйте класс объекта DB, вдруг там есть нормальный метод bind_param, а то вдруг используете инструмент не до конца по назначению.

Comments

[Игорь]

Вечером попробую и напишу, хотя сомневаюсь, что это поможет, проблема не только у меня.

[Игорь]

Помог SUBSTRING.
Да, вы абсолютно правы, но движок большой и старый, и переписывать каждый sql запрос под prepare пока не посилу, мы даже ещё на pdo не переехали, разработка open-source.
Bind param нет, но обязательно будет, хотелось бы иметь пре-компилированные запросы ради скорости.