PHP Drivers for SQL Server에서 보안 enclave를 사용한 Always Encrypted 사용
적용 가능 대상
- Microsoft Drivers 5.8.0 for PHP for SQL Server
소개
보안 Enclave를 사용한 Always Encrypted는 SQL Server에 대한 Always Encrypted 기능을 두 번 반복하는 기능입니다. 보안 Enclave를 사용한 Always Encrypted를 사용하면 데이터베이스의 암호화된 데이터를 복호화하여 계산을 수행할 수 있는 서버의 메모리 영역인 보안 Enclave를 생성하여 암호화된 데이터에 대해 풍부한 계산을 수행할 수 있습니다. 지원되는 연산에는 LIKE 절을 사용한 비교 및 패턴 일치가 포함됩니다.
보안 Enclave를 사용한 Always Encrypted를 사용하도록 설정
SQL Server용 PHP 드라이버 5.8.0 이상부터는 항상 암호화된 보안 Enclave에 대한 지원을 사용할 수 있습니다. 보안 enclave를 사용한 Always Encrypted에는 SQL Server 2019 이상 및 ODBC 드라이버 버전 17.4 이상이 필요합니다. PHP Drivers for SQL Server를 사용한 Always Encrypted의 일반 요구 사항에 대한 자세한 내용은 여기에서 확인할 수 있습니다.
보안 Enclave를 사용한 Always Encrypted에서는 enclave를 증명합니다. 즉, 외부 증명 서비스에 대해 enclave를 확인하여 암호화된 데이터의 보안을 유지할 수 있습니다. 보안 enclave를 사용하려면 ColumnEncryption 키워드가 쉼표로 구분된 연결된 증명 데이터와 함께 증명 형식 및 프로토콜을 식별해야 합니다. ODBC 드라이버 버전 17.4는 Enclave 유형 및 프로토콜에 대한 VBS(가상화 기반 보안) 및 HGS(호스트 보호 서비스) 프로토콜만 지원합니다. 연결된 증명 데이터는 증명 서버의 URL입니다. 따라서 연결 문자열에 다음 설정이 추가됩니다.
ColumnEncryption=VBS-HGS,http://attestationserver.mydomain/Attestation
프로토콜이 올바르지 않으면 드라이버가 이를 인식하지 못하고 연결이 실패하며 오류가 반환됩니다. 증명 URL만 잘못된 경우 연결이 성공하고 Enclave 사용 계산을 시도할 때 오류가 throw되지만, 그렇지 않은 경우 동작은 원래의 항상 암호화 동작과 동일합니다. ColumnEncryption을 enabled로 설정하면 일반적인 Always Encrypted 기능이 제공되지만, Enclave가 활성화된 작업을 시도하면 오류가 반환됩니다.
호스트 보호 서비스를 설정하고 필요한 암호화 키를 만드는 작업을 포함하여 보안 enclave를 사용한 Always Encrypted를 지원하도록 환경을 구성하는 방법에 대한 자세한 내용은 여기를 참조하세요.
예시
다음 예시(SQLSRV용 1개 및 PDO_SQLSRV용 1개)는 여러 데이터 유형이 포함된 테이블을 일반 텍스트로 만든 다음 암호화하고 비교 및 패턴 일치를 수행합니다. 다음 세부 정보를 참고하세요.
ALTER TABLE로 테이블을 암호화할 때는ALTER TABLE을 호출할 때마다 하나의 열만 암호화할 수 있으므로 여러 열을 암호화하려면 여러 번의 호출이 필요합니다.- 비교 임계값을 char 및 nchar 형식 비교를 위한 매개 변수로 전달하는 경우 열 너비를 해당
SQLSRV_SQLTYPE_*에 지정해야 합니다. 그렇지 않으면HY104오류Invalid precision value가 반환됩니다. - 패턴 일치의 경우
Latin1_General_BIN2절을 사용하여 데이터 정렬을COLLATE로 지정해야 합니다. - 패턴 매칭 문자열을 문자 및 nchar 유형 매칭을 위한 매개 변수로 전달할 때, 문자 및 nchar 유형은 문자열 끝의 공백을 패딩하므로
SQLSRV_SQLTYPE_*또는sqlsrv_query에 전달되는sqlsrv_prepare은 열의 크기가 아닌 일치시킬 문자열의 길이를 지정해야 합니다. 예를 들어, 문자열%abc%를 char(10) 열과 일치시킬 때는SQLSRV_SQLTYPE_CHAR(5)를 지정합니다. 대신SQLSRV_SQLTYPE_CHAR(10)을 지정하면 쿼리는%abc%(5개의 공백이 추가된 상태)와 일치하고, 공백이 5개 미만인 열의 데이터는 일치하지 않습니다(따라서abcdef는 4개의 공백이 패딩되어 있으므로%abc%와 일치하지 않음). UNICODE 문자열의 경우mb_strlen또는iconv_strlen함수를 사용하여 문자 수를 가져옵니다. - PDO 인터페이스에서는 매개 변수의 길이를 지정할 수 없습니다. 대신
PDOStatement::bindParam의 길이를 0 또는null로 지정합니다. 길이를 명시적으로 다른 숫자로 설정한 경우 매개 변수는 출력 매개 변수로 처리됩니다. - 패턴 일치는 Always Encrypted의 문자열이 아닌 형식에 대해서는 작동하지 않습니다.
- 명확성을 위해 오류 검사는 제외됩니다.
다음 데이터는 두 예시를 위한 공통 데이터입니다.
<?php
// Data for testing - integer, datetime2, char, nchar, varchar, and nvarchar
// String data is random, showing that we can match or compare anything
$testValues = array(array(1, "2019-12-31 01:00:00", "abcd", "㬚㔈♠既", "abcd", "㬚㔈♠既"),
array(-100, "1753-01-31 14:25:25.25", "#e@?q&zy+", "ઔܛ᎓Ե⅜", "#e@?q&zy+", "ઔܛ᎓Ե⅜"),
array(100, "2112-03-15 23:40:10.1594", "zyxwv", "㶋㘚ᐋꗡ", "zyxwv", "㶋㘚ᐋꗡ"),
array(0, "8888-08-08 08:08:08.08", "7t", "㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ", "7t", "㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ"),
);
// Queries to create the table and insert data
$createTable = "DROP TABLE IF EXISTS $myTable;
CREATE TABLE $myTable (c_integer int NULL,
c_datetime2 datetime2(7) NULL,
c_char char(32) NULL,
c_nchar nchar(32) NULL,
c_varchar varchar(32) NULL,
c_nvarchar nvarchar(32) NULL);";
$insertData = "INSERT INTO $myTable (c_integer, c_datetime2, c_char, c_nchar, c_varchar, c_nvarchar) VALUES (?, ?, ?, ?, ?, ?)";
// This is the query that encrypts the table in place
$encryptQuery = " ALTER TABLE $myTable
ALTER COLUMN [c_integer] integer
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON);
ALTER TABLE $myTable
ALTER COLUMN [c_datetime2] datetime2(7)
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON);
ALTER TABLE $myTable
ALTER COLUMN [c_char] char(32) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON);
ALTER TABLE $myTable
ALTER COLUMN [c_nchar] nchar(32) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON);
ALTER TABLE $myTable
ALTER COLUMN [c_varchar] varchar(32) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON);
ALTER TABLE $myTable
ALTER COLUMN [c_nvarchar] nvarchar(32) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK-enclave], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON);
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;";
?>
SQLSRV
<?php
// Specify Azure Key Vault credentials using the KeyStoreAuthentication, KeyStorePrincipalId, and KeyStoreSecret keywords
// Otherwise, the local Windows Certificate Store will be used
$options = array('database'=>$myDatabase,
'uid'=>$myUsername,
'pwd'=>$myPassword,
'CharacterSet'=>'UTF-8',
'ReturnDatesAsStrings'=>true,
'ColumnEncryption'=>"VBS-HGS,http://myattestationserver.mydomain/Attestation",
);
$conn = sqlsrv_connect($myServer, $options);
// Create the table and insert the test data
$stmt = sqlsrv_query($conn, $createTable);
foreach ($testValues as $values) {
$stmt = sqlsrv_prepare($conn, $insertData, $values);
sqlsrv_execute($stmt);
}
// Encrypt the table in place
$stmt = sqlsrv_query($conn, $encryptQuery);
// Test comparison and pattern matching on the encrypted table
echo "Test comparisons:\n";
$intThreshold = 0;
$testGreater = "SELECT c_integer FROM $myTable WHERE c_integer > ?";
$param = array($intThreshold, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_INT);
$stmt = sqlsrv_prepare($conn, $testGreater, array($param));
getResults($stmt);
// Expect:
// 1
// 100
$datetimeThreshold = "3000-01-01 00:00:00.0";
$testLess = "SELECT c_datetime2 FROM $myTable WHERE c_datetime2 < ?";
$param = array($datetimeThreshold, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_DATETIME2);
$stmt = sqlsrv_prepare($conn, $testLess, array($param));
getResults($stmt);
// Expect:
// 2019-12-31 01:00:00.0000000
// 1753-01-31 14:25:25.2500000
// 2112-03-15 23:40:10.1594000
$charThreshold = "abcd";
$ncharThreshold = "㬚㔈♠既";
$testGreaterEqual = "SELECT c_char FROM $myTable WHERE c_char >= ?";
$param = array($charThreshold, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_CHAR(32));
$stmt = sqlsrv_prepare($conn, $testGreaterEqual, array($param));
getResults($stmt);
// Expect:
// abcd
// zyxwv
$testLessEqual = "SELECT c_nchar FROM $myTable WHERE c_nchar <= ?";
$param = array($ncharThreshold, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING('UTF-8'), SQLSRV_SQLTYPE_NCHAR(32));
$stmt = sqlsrv_prepare($conn, $testLessEqual, array($param));
getResults($stmt);
// Expect:
// 㬚㔈♠既
// ઔܛ᎓Ե⅜
// 㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
$testNotGreater = "SELECT c_varchar FROM $myTable WHERE c_varchar !> ?";
$param = array($charThreshold, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_VARCHAR);
$stmt = sqlsrv_prepare($conn, $testNotGreater, array($param));
getResults($stmt);
// Expect:
// abcd
// #e@?q&zy+
// 7t
$testNotLess = "SELECT c_nvarchar FROM $myTable WHERE c_nvarchar !< ?";
$param = array($ncharThreshold, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING('UTF-8'), SQLSRV_SQLTYPE_NVARCHAR);
$stmt = sqlsrv_prepare($conn, $testNotLess, array($param));
getResults($stmt);
// Expect:
// 㬚㔈♠既
// 㶋㘚ᐋꗡ
echo "\nTest pattern matching:\n";
$charMatch = "%zy%";
$ncharMatch = "%㔈♠既%";
$param = array($charMatch, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_CHAR(strlen($charMatch)));
$testCharMatch = "SELECT c_char FROM $myTable WHERE c_char LIKE ? COLLATE Latin1_General_BIN2";
$stmt = sqlsrv_prepare($conn, $testCharMatch, array($param));
getResults($stmt);
// Expect:
// #e@?q&zy+
// zyxwv
$param = array($ncharMatch, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING("UTF-8"), SQLSRV_SQLTYPE_NCHAR(iconv_strlen($ncharMatch)));
$testNCharMatch = "SELECT c_nchar FROM $myTable WHERE c_nchar LIKE ? COLLATE Latin1_General_BIN2";
$stmt = sqlsrv_prepare($conn, $testNCharMatch, array($param));
getResults($stmt);
// Expect:
// 㬚㔈♠既
// 㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
$param = array($charMatch, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_VARCHAR(strlen($charMatch)));
$testVarcharMatch = "SELECT c_varchar FROM $myTable WHERE c_varchar LIKE ? COLLATE Latin1_General_BIN2";
$stmt = sqlsrv_prepare($conn, $testVarcharMatch, array($param));
getResults($stmt);
// Expect:
// #e@?q&zy+
// zyxwv
$param = array($ncharMatch, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING("UTF-8"), SQLSRV_SQLTYPE_NVARCHAR(iconv_strlen($ncharMatch)));
$testNVarcharMatch = "SELECT c_nvarchar FROM $myTable WHERE c_nvarchar LIKE ? COLLATE Latin1_General_BIN2";
$stmt = sqlsrv_prepare($conn, $testNVarcharMatch, array($param));
getResults($stmt);
// Expect:
// 㬚㔈♠既
// 㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
function getResults($stmt)
{
sqlsrv_execute($stmt);
while ($res = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
print_r($res[0]);
echo "\n";
}
}
?>
PDO_SQLSRV
<?php
// Specify Azure Key Vault credentials using the KeyStoreAuthentication, KeyStorePrincipalId, and KeyStoreSecret keywords
// Otherwise, the local Windows Certificate Store will be used
$options = "sqlsrv:server=$myServer;database=$myDatabase;driver={ODBC Driver 18 for SQL Server};";
$options .= "ColumnEncryption=VBS-HGS,http://myattestationserver.mydomain/Attestation",
$conn = new PDO($options, $myUsername, $myPassword);
// Create the table and insert the test data
$stmt = $conn->query($createTable);
foreach ($testValues as $values) {
$stmt = $conn->prepare($insertData);
$stmt->execute($values);
}
// Encrypt the table in place
$stmt = $conn->query($encryptQuery);
// Test comparison and pattern matching on the encrypted table
echo "Test comparisons:\n";
$intThreshold = 0;
$testGreater = "SELECT c_integer FROM $myTable WHERE c_integer > ?";
$stmt = $conn->prepare($testGreater);
$stmt->bindParam(1, $intThreshold, PDO::PARAM_INT);
getResults($stmt);
// Expect:
// 1
// 100
$datetimeThreshold = "3000-01-01 00:00:00.0";
$testLess = "SELECT c_datetime2 FROM $myTable WHERE c_datetime2 < ?";
$stmt = $conn->prepare($testLess);
$stmt->bindParam(1, $datetimeThreshold, PDO::PARAM_STR);
getResults($stmt);
// Expect:
// 2019-12-31 01:00:00.0000000
// 1753-01-31 14:25:25.2500000
// 2112-03-15 23:40:10.1594000
$charThreshold = "abcd";
$ncharThreshold = "㬚㔈♠既";
$testGreaterEqual = "SELECT c_char FROM $myTable WHERE c_char >= ?";
$stmt = $conn->prepare($testGreaterEqual);
$stmt->bindParam(1, $charThreshold, PDO::PARAM_STR);
getResults($stmt);
// Expect:
// abcd
// zyxwv
$testLessEqual = "SELECT c_nchar FROM $myTable WHERE c_nchar <= ?";
$stmt = $conn->prepare($testLessEqual);
$stmt->bindParam(1, $ncharThreshold, PDO::PARAM_STR);
getResults($stmt);
// Expect:
// 㬚㔈♠既
// ઔܛ᎓Ե⅜
// 㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
$testNotGreater = "SELECT c_varchar FROM $myTable WHERE c_varchar !> ?";
$stmt = $conn->prepare($testNotGreater);
$stmt->bindParam(1, $charThreshold, PDO::PARAM_STR);
getResults($stmt);
// Expect:
// abcd
// #e@?q&zy+
// 7t
$testNotLess = "SELECT c_nvarchar FROM $myTable WHERE c_nvarchar !< ?";
$stmt = $conn->prepare($testNotLess);
$stmt->bindParam(1, $ncharThreshold, PDO::PARAM_STR);
getResults($stmt);
// Expect:
// 㬚㔈♠既
// 㶋㘚ᐋꗡ
echo "\nTest pattern matching:\n";
$charMatch = "%zy%";
$ncharMatch = "%㔈♠既%";
$testCharMatch = "SELECT c_char FROM $myTable WHERE c_char LIKE ? COLLATE Latin1_General_BIN2";
$stmt = $conn->prepare($testCharMatch);
$stmt->bindParam(1, $charMatch, PDO::PARAM_STR);
getResults($stmt);
// Expect:
// #e@?q&zy+
// zyxwv
$testNCharMatch = "SELECT c_nchar FROM $myTable WHERE c_nchar LIKE ? COLLATE Latin1_General_BIN2";
$stmt = $conn->prepare($testNCharMatch);
$stmt->bindParam(1, $ncharMatch, PDO::PARAM_STR,null,PDO::SQLSRV_ENCODING_UTF8);
getResults($stmt);
// Expect:
// 㬚㔈♠既
// 㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
$testVarcharMatch = "SELECT c_varchar FROM $myTable WHERE c_varchar LIKE ? COLLATE Latin1_General_BIN2";
$stmt = $conn->prepare($testVarcharMatch);
$stmt->bindParam(1, $charMatch, PDO::PARAM_STR);
getResults($stmt);
// Expect:
// #e@?q&zy+
// zyxwv
$testNVarcharMatch = "SELECT c_nvarchar FROM $myTable WHERE c_nvarchar LIKE ? COLLATE Latin1_General_BIN2";
$stmt = $conn->prepare($testNVarcharMatch);
$stmt->bindParam(1, $ncharMatch, PDO::PARAM_STR,null,PDO::SQLSRV_ENCODING_UTF8);
getResults($stmt);
// Expect:
// 㬚㔈♠既
// 㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
function getResults($stmt)
{
$stmt->execute();
while($res = $stmt->fetch(PDO::FETCH_NUM)) {
print_r($res[0]);
echo "\n";
}
}
?>
출력
Test comparisons:
1
100
2019-12-31 01:00:00.0000000
1753-01-31 14:25:25.2500000
2112-03-15 23:40:10.1594000
abcd
zyxwv
㬚㔈♠既
ઔܛ᎓Ե⅜
㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
abcd
#e@?q&zy+
7t
㬚㔈♠既
㶋㘚ᐋꗡ
Test pattern matching:
#e@?q&zy+
zyxwv
㬚㔈♠既
㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
#e@?q&zy+
zyxwv
㬚㔈♠既
㛜ꆶ㕸㔈♠既ꁺꖁ㓫ޘ갧ᛄ
참고 항목
PHP SQL 드라이버 프로그래밍 가이드
SQLSRV 드라이버 API 참조
PDO_SQLSRV 드라이버 API 참조
SQL Server용 PHP 드라이버와 함께 Always Encrypted 사용