用PHP加密，用SQL服务器解密

What I am doing is encrypting data with PHP when inserting new customers. However later on I will need to decrypt the entire table using SQL Server commands. However I am running into a problem where I think DecryptByKey will only accept a Varbinary. However when I encrypt with PHP it doesn't produce a Varbinary variable. The two encryption algorithms should be the same.

我所做的是在插入新客户时使用PHP加密数据。但是稍后我将需要使用SQL Server命令对整个表进行解密。但是，我遇到了一个问题，我认为DecryptByKey只接受Varbinary。但是，当我用PHP加密时，它不会生成一个Varbinary变量。这两种加密算法应该是相同的。

What am I doing wrong?

我做错了什么?

PHP I use to encrypt:

我用来加密的PHP:

function fnE($sV, $sS)
{
    return trim(
        base64_encode(
            mcrypt_encrypt(
                MCRYPT_RIJNDAEL_256,
                $sS, $sV,
                MCRYPT_MODE_ECB,
                mcrypt_create_iv(
                    mcrypt_get_iv_size(
                        MCRYPT_RIJNDAEL_256,
                        MCRYPT_MODE_ECB
                    ),
                MCRYPT_RAND)
            )
        )
    );
}

This turns "James" into "/mucJj8Znec7aygh3ly2DI45bSxyv6vG4dzFz4SVVHk=" which is inserted into a table called 'customer_details' in column name 'first_name'.

这将“James”转换为“/ mucj8znec7aygh3ly2di45bsxyv6vg4dzfz4svhk =”，并将其插入到名为“first_name”的名为“customer_details”的表中。

Then in SQL Server I create a master key with the same password as I used in PHP.

然后在SQL Server中，我创建一个主键，其密码与PHP相同。

Then I do:

然后我做:

Create Symmetric Key AdvSym
With Algorithm =AES_256
Encryption by Certificate AdvCert
GO

But whenever I try to decrypt it just shows NULL:

但是每当我尝试解密它就会显示空值:

Select first_name,
Convert(Varchar(100), DecryptByKey(first_name)) as DecryptedName from customer_details
go

EDIT: It should be known that I am success in encrypting and decrypting when using ONLY SQL. However I need to be able to encrypt using PHP then decrypt using SQL.

编辑:只使用SQL时，应该知道我在加密和解密方面是成功的。但是，我需要能够使用PHP加密，然后使用SQL解密。

1 个解决方案

#1

are you using the same credentials when in sql and within the application.

在sql和应用程序中使用相同的凭据吗?

SQL documentation states that when unallowed database users attempt to decrypt a value, even when using the correct key name and salt if used, the result will return null if the database user has view definition false for said key.

SQL文档指出，当不允许的数据库用户尝试解密一个值时，即使使用了正确的键名和salt，如果数据库用户对该键的视图定义为false，结果也将返回null。

A workaround I've used, if you don't want you application user to be able to have permissions over a key, is to write the select within a user defined multiple statement table function and inside it change use the 'with execute as [allowedUserhere].'

我使用的一个解决方案是，如果您不希望您的应用程序用户能够对键拥有权限，那么可以在用户定义的多个语句表函数中编写select，并在其中使用'with execute as [allowedUserhere]。

Remember to allow your application user to "impersonate" your "key user" for this to work.

请记住，要让您的应用程序用户“模拟”您的“关键用户”以使其工作。

#1