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.
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);
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.
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.
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).
