php mysql 加密查询

本文已参与「新人创作礼」活动，一起开启掘金创作之路。本文已参与「新人创作礼」活动，一起开启掘金创作之路。

1.SHA256加密 不可模糊查询

1.简述

使用sha256加密解密，需要php开启openssl扩展

2.工具类方法

 /**
* sha256加密
*/
public  function rsaEncrypt($str)
{
    $pi_key = openssl_pkey_get_private(config('sha256_private_key'));

    if (!$pi_key) return "秘钥不可用"; //秘钥不可用

openssl_private_encrypt($str . "", $encrypted, $pi_key);

    $encrypted = base64_encode($encrypted);

    return $encrypted;
}


/**
* sha256解密
*/
public  function rsaDecrypt($str)
{
    $pu_key = openssl_pkey_get_public(config('sha256_public_key'));

    if (!$pu_key) return false; //秘钥不可用

openssl_public_decrypt(base64_decode($str), $decrypted, $pu_key);

    return $decrypted;
}

/**
* 获取公钥私钥
*/
public  function rsaGetKey()
{
    //创建公私钥
$res = openssl_pkey_new();
    //获取私钥
openssl_pkey_export($res, $private_key);
    //获取公钥
$public_key = openssl_pkey_get_details($res)['key'];
    //组合rsa
$rsa = [
        'public_key'  => $public_key,
        'private_key' => $private_key,
    ];
    return $rsa;
}

3.操作步骤

3.1 通过 rsaGetKey()方法 获取公钥-私钥对

3.2 将获取到的公钥-私钥配置到config和.env中

config.php

 //sha256加密
'sha256_private_key'=>env('SHA256_PRIVATE_KEY',"-----BEGIN PRIVATE KEY-----SHA256_PRIVATE_KEY-----BEGIN PRIVATE KEY-----"),
'sha256_public_key'=>env('SHA256_PUBLIC_KEY',"-----BEGIN PUBLIC KEY-----SHA256_PUBLIC_KEY-----BEGIN PUBLIC KEY-----"),

.env

SHA256_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQC+mKboGm5i5c7P\nqIRN2p+BAryQpwuBTvsAE9NFNxHEs7hZQ7PfTwTRY1gmP0+ye7SsSRCfRlXnWPk4\ndtB0Bm4yf/NfgpXFlCmOF/84W6tQSlGdxILJYwrO8AmI5Q7Ji/yxKtSb1WEzrgXO\nd2oHiPAAvCkp6/Uy0RPoU7OL4QZoMVJHJwKEfnDl9E37RsJVTh471Ybf6DsT53Nj\nvXl3TXmdc26dlBaiZEmqoivxEanff6990NQ7FE7NTb99QqI1nmHmGjl4ScnLaQum\nXIvvuVStEtcwYHIurkJJ+Xhv546yR5N0UyK6j/lXrL5Qwie3RizfqI3WyM0jXgJQ\n11zH63SzAgMBAAECggEAOVmln2GUpZ2QxVtntCV8qU0xgDVDeP1S+Yr9n24aop2R\ncwq0LRWR1Y+XDqsuR/6BNvYlUkfPwXU8SIk34DGvJvLpQGwCtRkICAGggkzTF63F\nMTvYPtDWh74ioFaMRIki2AyTPPDP9sT+akn0VaAW16HGoR6nAUt4Ogg1lnl3fBIW\nOPBHXXHcRhff5nwufIJF2oe6q4Ufd6NmCZkEnv6lXw4maiFRrjvFCCRhwkJo6pQX\nfY3UVdZqt2XE7My0jcnBcnJOK/kXNzIm4TDEyVySNxlT5VsXcu2vgcJbfbd/4ufQ\nZmQQpynZhlgQXUZ9wONORvUB1y3Q1mNhCVVwXraQwQKBgQDjxEsJ2DqvwSF4Ibmr\n4cNpm3d0S5dJSB9jDigADRY+CCkaW0pnC1juJOohqwU8YavLx7maJvC7N0juc6RB\nscMH9Eof8VC2MjbmwKboZCpDk7iTRO6m8afee8U/fYhKO39QrpcGD8nn8vFt96i2\nJ+1allvAbsZHtZboP5cS14ZoNwKBgQDWONSymd3Z9y2GoWrAWPICdF1hxxp8e6xC\n2KjrB+1wlBoseq8aC88vBaYMeOIDmX3fHs9wc1ZamTtjOwgvxnL88NRZhgw/hmFs\nM5rEVdlGo9kIOIRs3F/GyRdIa4fny4furItQIwEMTVgL1scMT3aI1Qt3xrqncDcq\n7zTyVhThZQKBgEAX2q3YhRs0E8EEbIUrsJulPgYhx169xudO8n6hMCMglMBv9A3v\nKYVKKgBzabPGIFvlxhrU3mUwd3tyW0SJS0KPwdQfSgEbPEvO5qj6ltIRXZ1Dhoy/\n7+cxmR32q0WIXJlNp/3yr3Rv7vRs/rLTpX0aVpNeGLnRJbgRPOf2ckIdAoGBAJnc\nobBR4U0vQqyi/Uh/2fq3V2bmGXj1Xul4MgL1Y63rQc1WpLOBbeKpHPVyUWsicIyz\n1Ww/+vMBge0oth2SXH6d/vb514w82XZcrjl8K52YC0bjSQHjWostXaKvUSLERbLA\njSMWsScD2kxOjBKkdE+dLSpvOxsN9yHIQ3AXMdIdAoGBALq4Vbh3C12OEwvs5zUX\nQSvABJHtI3fDbSVrG776vml5SpL3PHSRFoSRX+kJgbVKMyCYOqkMCrHXefThSVT5\nTEnTTbeqBUwPWDxigKG6E+teBPWEPDrLgsaIisjMTNlRG/HbQH7yp4KZ7Y9kZDtj\nAZTdq8xgoZAlKGSe4YW0Srec\n-----END PRIVATE KEY-----\n"
SHA256_PUBLIC_KEY="-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvpim6BpuYuXOz6iETdqf\ngQK8kKcLgU77ABPTRTcRxLO4WUOz308E0WNYJj9Psnu0rEkQn0ZV51j5OHbQdAZu\nMn/zX4KVxZQpjhf/OFurUEpRncSCyWMKzvAJiOUOyYv8sSrUm9VhM64FzndqB4jw\nALwpKev1MtET6FOzi+EGaDFSRycChH5w5fRN+0bCVU4eO9WG3+g7E+dzY715d015\nnXNunZQWomRJqqIr8RGp33+vfdDUOxROzU2/fUKiNZ5h5ho5eEnJy2kLplyL77lU\nrRLXMGByLq5CSfl4b+eOskeTdFMiuo/5V6y+UMInt0Ys36iN1sjNI14CUNdcx+t0\nswIDAQAB\n-----END PUBLIC KEY-----\n"

3.3 创建cast/Sha256.php

 <?php

namespace App\Casts;

class Sha256 implements CastsAttributes
{
    /**
*  将取出的数据进行转换
*
*  @param  string  $key
*  @param  mixed  $value
*  @param  array  $attributes
*  @return  float
*/
 public  function get($model, $key, $value, $attributes)
    {
        //获取时不解密
        return  $value;
        //获取时解密
        return rsaDecrypt($value);
    }

    /**
* 转换成将要进行存储的值
*
*  @param  string  $key
*  @param  mixed  $value
*  @param  array  $attributes
*  @return  integer
*/
 public  function set($model, $key, $value, $attributes)
    {
        return rsaEncrypt($value);
    }
}

3.4 model中需要转换的字段定义casts即可

protected $casts
    = [
        'phone'     => Sha256::class,
        'old_phone' => Sha256::class,
    ];

3.5 使用案例(按手机号查询)

public function getUserByPhone($phone){
    return User::where("phone",rsaEncrypt( $phone ) )
}

2.AES加密 可模糊查询

1.简述

使用AES加密解密，支持与mysql AES交互

2.工具类方法

 /**
* AES 加密
*/
public  static  function aesEncrypt($str)
{
    return openssl_encrypt($str, 'AES-128-ECB', config('aes_key'), 0);
}

/**
* AES解密
*/
public  static  function aesDecrypt($str)
{
    return openssl_decrypt($str, 'AES-128-ECB', config('aes_key'), 0);
}

3.操作步骤

3.1 配置一个秘钥到config和.env中

config.php

 //aes加密
'aes_key'=>env('AES_KEY','AES_KEY'),

.env

AES_KEY=qwerqwdsawrwqe

3.2 创建cast/AES.php

 <?php

namespace App\Casts;

use App\Service\Utils\Func;
use Hyperf\Contract\CastsAttributes;

class AES implements CastsAttributes
{
    /**
*  将取出的数据进行转换
*
*  @param  string  $key
*  @param  mixed  $value
*  @param  array  $attributes
*  @return  float
*/
 public  function get($model, $key, $value, $attributes)
    {
     //   return Func::aesDecrypt($value);
 return $value;
    }

    /**
* 转换成将要进行存储的值
*
*  @param  string  $key
*  @param  mixed  $value
*  @param  array  $attributes
*  @return  integer
*/
 public  function set($model, $key, $value, $attributes)
    {
        return aesEncrypt($value);
    }
}

3.3 model中需要转换的字段定义casts即可

 //aes加密
protected $casts
    = [
        'phone'     => AES::class,
        'old_phone' => AES::class,
    ];

3.4 使用案例(按手机号查询模糊查询)

先base64解密 因为php AES加密会自动包一层base64加密；

public function getUserByPhone($phone){
    return User::whereRaw('AES_DECRYPT(FROM_BASE64(old_phone),"'
    .config('aes_key').'") LIKE "%'.$keyword.'%"')->get();
}

\