本文介绍了LIBSODIUM像使用AES_DECRYPT一样在mysql查询中解密数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一些sql查询,其中我选择了靠近用户位置的行.使用AES_DECRYPT,我可以在查询内部进行操作:

I have some sql querys where im selecting rows near to the users location.With AES_DECRYPT i could do inside the query:

AES_DECRYPT(lat, :key)

我需要查询中的解密值:
1.订购
2.分隔给定区域中的条目
3.以及其他查询的类似内容

I need the decrypted value inside the query to:
1. order them
2. delimit entrys in a given area
3. and similar things for other querys

一个查询的简短示例:

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个.

I can't select all rows on a different query and manipulate the result with php, that would be very ineffiecient with 100k + rows while the rows near the user might be only ~100.

对位置数据进行加密对我来说非常重要,因为到目前为止,由于不安全的加密,我才启动我的项目.

Its very important for me to keep location data encrypted, i did not start my project because of unsecure encryption until now.

现在我的问题是我如何使用新的PHP实现的Libsodium来做同样的事情?

Now my question is how i can do the same thing with newly php implemented Libsodium ?

我找不到一个可行的例子.我刚刚找到了一个带有盲索引的示例,在该示例中,如果您像存储的术语一样对搜索项进行加密,则可以找到一个值,但是在我的情况下这没有帮助,因为我需要获取该值才能通过同一个内部的距离公式运行查询.

I couldnt find one example that would work. I just found a example with blind index where you can find a value if you encrypt the search term same like the stored one, but that isnt helpful in my case because i need to get the value to run that through the distance formula inside the same query.

推荐答案

Libsodium不是MySQL内置的,所以您不能只从MySQL查询中调用与AES_ENCRYPT()等效的东西并获得您期望的结果.

Libsodium isn't built into MySQL, so you can't just call something equivalent to AES_ENCRYPT() from within a MySQL query and get the results you expect.

但是,另一种方法是使用 CipherSweet 之类的库,该库提供可搜索的经过身份验证的加密.确保您了解其功能限制,然后再决定使用它.

However, an alternate approach is to use a library like CipherSweet, which provides searchable authenticated encryption. Make sure you understand its features and limitations before deciding to use it.

<?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(
    // Example key, chosen randomly, hex-encoded:
    'a981d3894b5884f6965baea64a09bb5b4b59c10e857008fc814923cf2f2de558'
);
$engine = new CipherSweet($provider, new FIPSCrypto());

/** @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查询中使用).

You should see something similar to this. The values in the [0] will change, but the values in [1] will not. This is becuase [0] contains the row data with (some fields encrypted). [1] only contains blind indexes (usable later in SELECT queries).

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"
    }
  }
}

请注意,即使输入的精度有所不同,浮点字段也将始终生成固定长度的输出.这样做是为了防止攻击者从密文长度中学习信息.

Note that the floating point fields will always produce a fixed-length output, even if the inputs have varying levels of precision. This is done intentionally to prevent attackers from learning information from ciphertext length.

如果您选择 ModernCrypto而不是FIPSCrypto ,以上所有操作都将用libsodium完成.每个人使用的确切加密方法是在此处记录的(如果有人好奇的话).

If you select ModernCrypto instead of FIPSCrypto, all of the above will be done with libsodium. The exact encryption used by each is documented here, if anyone is curious.

请注意,您必须对PHP中而不是SQL中的解密值进行自己的计算.

Note that you'll have to do your own calculations of the decrypted values in PHP rather than SQL.

毕竟,在将数据存储到数据库之前对数据进行加密的全部目的是将其隐藏在数据库服务器(以及可能损害该服务器的所有攻击者)中.

After all, the whole point of encrypting data before storing it in a database is to hide it from the database server (and any attackers who could have compromised said server).

这篇关于LIBSODIUM像使用AES_DECRYPT一样在mysql查询中解密数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 21:07
查看更多