LIBSODIUM расшифровывает данные в запросе mysql, как это было с AES_DECRYPT

Question

У меня есть несколько запросов SQL, где я выбираю строки рядом с местоположением пользователей. С AES_DECRYPT я мог бы сделать внутри запроса:

AES_DECRYPT(lat, :key)

Мне нужно расшифрованное значение внутри запроса, чтобы:
1. заказать их
2. Разграничить записи в данной области
3. и аналогичные вещи для других запросов

Краткий пример одного запроса:

SELECT something,
(
    6371 * acos( cos( radians(".$userdatafromdbfetchedbefore['lat'].") ) * cos( radians( AES_DECRYPT(lat, :key) ) ) * cos( radians( AES_DECRYPT(lng, :key) ) - radians(".$userdatafromdbfetchedbefore['lng'].") ) + sin( radians(".$userdatafromdbfetchedbefore['lat'].") ) * sin(radians( AES_DECRYPT(lat, :key))) )
) AS distance
FROM 
    table
HAVING 
    distance <= ".$userdatafromdbfetchedbefore['maxrange']."
ORDER BY 
    e.orderdate 
DESC,
    distance  
ASC
LIMIT
    ".$start.", ".$offset."

Я не могу выбрать все строки в другом запросе и манипулировать результатом с помощью php, что было бы очень неэффективно с 100k + строками, в то время как строки рядом с пользователем могли бы быть только ~100.

Для меня очень важно сохранить данные о местоположении в зашифрованном виде, я до сих пор не начал свой проект из-за небезопасного шифрования.

Теперь мой вопрос: как я могу сделать то же самое с недавно внедренным php Libsodium?

Я не мог найти один пример, который работал бы. Я только что нашел пример со слепым индексом, где вы можете найти значение, если вы зашифруете поисковый термин, такой же, как и сохраненный, но в моем случае это бесполезно, потому что мне нужно получить значение, чтобы выполнить его через формулу расстояния внутри того же самого запрос.

Answer 1

Libsodium не встроен в MySQL, поэтому вы не можете просто вызвать что-то эквивалентное AES_ENCRYPT() из запроса MySQL и получите ожидаемые результаты.

Однако альтернативный подход заключается в использовании библиотеки, подобной CipherSweet, которая обеспечивает аутентифицированное шифрование с возможностью поиска. Убедитесь, что вы понимаете его особенности и ограничения, прежде чем использовать его.

<?php
use ParagonIE\CipherSweet\CipherSweet;
use ParagonIE\CipherSweet\EncryptedRow;
use ParagonIE\CipherSweet\Transformation\AlphaCharactersOnly;
use ParagonIE\CipherSweet\Transformation\FirstCharacter;
use ParagonIE\CipherSweet\Transformation\Lowercase;
use ParagonIE\CipherSweet\Backend\FIPSCrypto;
use ParagonIE\CipherSweet\KeyProvider\StringProvider;

$provider = new StringProvider(
    new FIPSCrypto(),
    // Example key, chosen randomly, hex-encoded:
    'a981d3894b5884f6965baea64a09bb5b4b59c10e857008fc814923cf2f2de558'
);
$engine = new CipherSweet($provider);

/** @var CipherSweet $engine */
$row = (new EncryptedRow($engine, 'contacts'))
    ->addTextField('first_name')
    ->addTextField('last_name')
    ->addFloatField('latitude')
    ->addFloatField('longitude');

// Notice the ->addRowTransform() method:
$row->addCompoundIndex(
    $row->createCompoundIndex(
        'contact_first_init_last_name',
        ['first_name', 'last_name'],
        64, // 64 bits = 8 bytes
        true
    )
        ->addTransform('first_name', new AlphaCharactersOnly())
        ->addTransform('first_name', new Lowercase())
        ->addTransform('first_name', new FirstCharacter())
        ->addTransform('last_name', new AlphaCharactersOnly())
        ->addTransform('last_name', new Lowercase())
);

$prepared = $row->prepareRowForStorage([
    'first_name' => 'Jane',
    'last_name' => 'Doe',
    'latitude' => 52.52,
    'longitude' => -33.106,
    'extraneous' => true
]);

var_dump($prepared);

Вы должны увидеть что-то похожее на это. Значения в [0] изменится, но значения в [1] не буду. Это потому что [0] содержит данные строки с (некоторые поля зашифрованы). [1] содержит только слепые индексы (можно использовать позже в запросах SELECT).

array(2) {
  [0]=>
  array(5) {
    ["first_name"]=>
    string(141) "fips:nrtzoaxvPIOA7jPskWVwJmC0q8WJqrsnqjPh3ifNPsRd2TAx6OwTDfSiMVCXSsSRNQb_nxJlW7TbAtf5UvQRWWKTGhk_kXxpZKdnTrpjbmxi0IgstSrZ126Qz6E0_lvjew0Ygw=="
    ["last_name"]=>
    string(137) "fips:98f5CLB24w0zSqCGPR0D2oq9wQvUwzxo_byAp6mKgMgoJkUHZX1oTtk4Cm8FXI7fsUI8HOG5sKQFGRn6cXMw1EOMGgpXZqiXEDb3jxEbg9s95d4g2NeVd4xs2tmX0xlZ0nSM"
    ["latitude"]=>
    string(145) "fips:d3TVGfnRFlvWxbfihgHqjpXlXU3HtkCAHzM0-4f1l5dAeQf2Vk5RDDVOGMQNM09r0O4UOAub6QTyHGezQ0bWKQ5omqoYCTBJE0Uf_2DSPfO7U4dG74phaP04iFgqpJ8G41q54Kv5t54="
    ["longitude"]=>
    string(145) "fips:IcnUnBZZOxJPYXk-F3v12O_krNb9JsexljiV4gJzgctTpxLFm7ql0tJRF7xP3wLrUtd1VyfYBf75ot7iOSIIIFqsuyKZQdI9UyKbqd87RTMsHbHgPouxgZBg1urlqpuWqbOYEFGiti4="
    ["extraneous"]=>
    bool(true)
  }
  [1]=>
  array(1) {
    ["contact_first_init_last_name"]=>
    array(2) {
      ["type"]=>
      string(13) "w6dsrxbathjze"
      ["value"]=>
      string(16) "546b1ffd1f83c37a"
    }
  }
}

Обратите внимание, что поля с плавающей запятой всегда будут давать выходные данные фиксированной длины, даже если входные данные имеют различные уровни точности. Это сделано намеренно, чтобы не дать злоумышленникам узнать информацию из длины зашифрованного текста.

Если вы выберете ModernCrypto вместоFIPSCryptoВсе вышеперечисленное будет сделано с помощью libsodium. Точное шифрование, используемое каждым, задокументировано здесь, если кому-то интересно.

---

Обратите внимание, что вам придется делать свои собственные расчеты дешифрованных значений в PHP, а не в SQL.

В конце концов, весь смысл шифрования данных перед их сохранением в базе данных состоит в том, чтобы скрыть их от сервера базы данных (и любых злоумышленников, которые могли скомпрометировать указанный сервер).