环境
- SQL Server 2016 标准版
- PHP 8.2
背景
我已设置列级加密,并且许多列均能正常运行。我的数据库已通过 PHP 成功与客户端交互。
问题
尝试存储文件时,我无法使列级加密工作。在加密之前,我使用以下(简化)代码使其完全正常工作:
PHP的:
$filePath = $_FILES["file_ToUpload"]["tmp_name"];
// Read the file into a binary variable
$fileData = file_get_contents($filePath);
$fileData = unpack('H*hex', $fileData); // Convert binary data to hex
$fileData = $fileData['hex'];
// Prepare the SQL query
$query = "{CALL spUploadFile(?)}";
$params = array(
$fileData
);
$result = sqlsrv_query($conn, $query, $params);
SQL 过程:
ALTER PROCEDURE [dbo].[spUploadFile]
@FileData NVARCHAR(MAX)
AS
BEGIN
DECLARE @BinaryData VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @FileData, 2); -- Convert hex string back to binary
INSERT INTO MyFiles (File_Data)
VALUES (@BinaryData)
END
我的尝试
我感觉我已经尝试了一切。我尝试在 PHP 中将 $filedata 作为二进制传递。以下是我认为在我的 SQL 过程中最成功的方法:
ALTER PROCEDURE [dbo].[spUploadFile]
@FileData NVARCHAR(MAX)
AS
BEGIN
OPEN SYMMETRIC KEY MY_SymmetricKey
DECRYPTION BY CERTIFICATE MY_ColumnEncryption;
Declare @EncryptedFileData VARBINARY(MAX) = ENCRYPTBYKEY(KEY_GUID('MY_SymmetricKey'), @FileData)
CLOSE SYMMETRIC KEY MY_SymmetricKey;
INSERT INTO MyFiles (File_Data)
VALUES (@EncryptedFileData)
END
但是,这会引发错误:字符串或二进制数据将被截断。我也尝试过:
Declare @EncryptedFileData VARBINARY(MAX) = ENCRYPTBYKEY(KEY_GUID('MY_SymmetricKey'), convert(varchar(MAX), @FileData))
结果相同。.txt 文件可以正常工作,但大多数其他文件则不行。
尝试将文件作为字节数组传递也会出现截断错误:
PHP的:
$filePath = $_FILES["file_ToUpload"]["tmp_name"];
// Read the file into a binary variable
$fileData = file_get_contents($filePath);
// Prepare the SQL query
$query = "{CALL spUploadFile(array($fileData, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'))}";
$params = array(
$fileData
);
$result = sqlsrv_query($conn, $query, $params);
SQL:
ALTER PROCEDURE [dbo].[spUploadFile]
@FileData VARBINARY(MAX)
AS
BEGIN
OPEN SYMMETRIC KEY MY_SymmetricKey
DECRYPTION BY CERTIFICATE MY_ColumnEncryption;
Declare @EncryptedFileData VARBINARY(MAX) = ENCRYPTBYKEY(KEY_GUID('MY_SymmetricKey'), @FileData)
CLOSE SYMMETRIC KEY MY_SymmetricKey;
INSERT INTO MyFiles (File_Data)
VALUES (@EncryptedFileData)
END