标题 ##MS-SQL - ODBC - INSERT 在应用于"始终加密"的 VARCHAR(MAX( 列时失败
以下程序尝试使用 ODBC 将文本文件加载到 VARCHAR(max( 列中。 VARCHAR(MAX( 列是加密的 - 使用"始终加密"功能。
create table clobTable
(
id integer identity(1,1) primary key not null,
--clobCol varchar(max)
clobCol [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);
INSERT 语句在执行时失败，同时通知以下错误消息：
Native error = 206
Message text = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'AEdemo') collation_name = 'Compatibility_136_8200_0' is incompatible with varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1',
SQLState = 37000
Native error = 8180
Message text = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
笔记：
- 当将该程序应用于其中 VARCHAR(MAX( 列的表 未加密 - 它工作得很好。
- 以下是通过 MS-SQL 服务器管理工作室发布的，它工作正常：
declare @v varchar(max) = '111111111111111111222222222222222222222223333333333333333333333334444444444444444'
insert into clobTable (clobCol) values (@v)
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <stdio.h>
#include <stdlib.h>
/*
MS-SQL - ODBC - INSERT fails when applied on an "Always Encrypted" VARCHAR(MAX) column
The following program attempts to load a text file into a VARCHAR(max) column - using ODBC.
The VARCHAR(MAX) column is encrypted - using 'Always Encrypted' feature.
create table clobTable
(
id integer identity(1,1) primary key not null,
--clobCol varchar(max)
clobCol [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);
The INSERT statement fails upon execution while informing the following error message:
SQLState = 22005
Native error = 206
Message text = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'AEdemo') collation_name = 'Compatibility_136_8200_0' is incompatible with varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1',
SQLState = 37000
Native error = 8180
Message text = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
Notes:
1. When applying this program on a table in which VARCHAR(MAX) column is not encrypted - it works perfectly fine.
2. Following is issued via MS-SQL server management studio and it works fine:
declare @v varchar(max) = '111111111111111111222222222222222222222223333333333333333333333334444444444444444'
insert into clobTable (clobCol) values (@v)
*/
#define __CON_STR__ "DRIVER={ODBC Driver 17 for SQL Server};Server=sql16-w16.qa.int\mssqlserver2016;Database=AEdemo;UID=xx;PWD=yyyyyyyy;ColumnEncryption=Enabled;"
#define __THE_FILE__ "..\DevProjects\mssql-load-file-to-varchar-max\big-text-file.txt"
static int SQL_OK(SQLRETURN result);
static int printErrors(SQLHENV envHandle, SQLHDBC conHandle, SQLHSTMT stmtHandle);
static char * loadedFile(char *pszTheFile);
int main(int argc, char * argv[])
{
char* pszBigString = NULL;
const char* pszSQL = "INSERT INTO clobTable (clobCol) VALUES (?)";
HSTMT hStmt = NULL;
SQLHENV hEnv = NULL;
SQLRETURN iError = SQLAllocEnv(&hEnv);
SQLLEN * len;
HDBC hDbc = NULL;
SQLAllocConnect(hEnv, &hDbc);
const char* pszConnStr = __CON_STR__;
UCHAR szConnectOut[SQL_MAX_MESSAGE_LENGTH];
SWORD iConnectOutLen = 0;
iError = SQLDriverConnectA(hDbc, NULL, (unsigned char*)pszConnStr,
SQL_NTS, szConnectOut,
(SQL_MAX_MESSAGE_LENGTH - 1), &iConnectOutLen,
SQL_DRIVER_COMPLETE);
if (!SQL_OK(iError))
{
printErrors(SQL_NULL_HENV, hDbc, SQL_NULL_HSTMT);
exit(-1);
}
iError = SQLAllocStmt(hDbc, &hStmt);
iError = SQLPrepareA(hStmt, (SQLCHAR*)pszSQL, SQL_NTS);
if (!SQL_OK(iError))
{
printErrors(SQL_NULL_HENV, SQL_NULL_HDBC, hStmt);
exit(-1);
}
pszBigString = loadedFile(__THE_FILE__); // __THE_FILE__;
len = strlen(pszBigString);
iError = SQLSetParam(hStmt, 1, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (SQLPOINTER)pszBigString, NULL);
iError = SQLExecute(hStmt);
if (!SQL_OK(iError))
{
printErrors(SQL_NULL_HENV, SQL_NULL_HDBC, hStmt);
exit(-1);
}
iError = SQLDisconnect(hDbc);
if (!SQL_OK(iError))
{
printErrors(SQL_NULL_HENV, hDbc, SQL_NULL_HSTMT);
exit(-4);
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}
static /*bool*/ int SQL_OK(SQLRETURN result)
{
if (result == SQL_SUCCESS || result == SQL_SUCCESS_WITH_INFO)
return(TRUE);
else
return(FALSE);
}
static /*bool*/ int printErrors(SQLHENV envHandle,
SQLHDBC conHandle,
SQLHSTMT stmtHandle)
{
SQLRETURN result;
SQLWCHAR sqlState[6];
SQLINTEGER nativeError;
SQLSMALLINT requiredLength;
SQLWCHAR messageText[1024 + 1];
do
{
result = SQLError(envHandle,
conHandle,
stmtHandle,
sqlState,
&nativeError,
messageText,
sizeof(messageText),
&requiredLength);
if (SQL_OK(result))
{
printf("SQLState = %Sn", sqlState);
printf("Native error = %dn", nativeError);
printf("Message text = %Sn", messageText);
}
} while (SQL_OK(result));
return 0;
}
#include <sys/stat.h>
#include <sys/types.h>
static char * loadedFile(char *pszTheFile)
{
struct stat st;
int statRes;
_off_t fileSize = 0;
char *pszFileContents;
FILE *fp;
memset(&st, 0, sizeof(st));
statRes = stat(pszTheFile, &st);
if (statRes == 0)
fileSize = st.st_size;
else
return NULL;
pszFileContents = calloc(1, fileSize);
fp = fopen(pszTheFile, "r");
fread(pszFileContents, 1, fileSize, fp);
fclose(fp);
return pszFileContents;
}
> 2020-02-13
引用的 DOC 没有多大帮助。
然而 - 在引用的 URL "使用 Always Encrypted with the ODBC Driver for SQL Server"之后，显示了一些奇怪的东西。
连接字符串标记"列加密"值设置为"已启用"。
这将允许通过该 ODBC 连接进行加密和解密活动。
现在 - 在 SQLDriverConnect(( 之后，发出以下内容：
iError = SQLGetConnectAttr(hDbc, SQL_COPT_SS_COLUMN_ENCRYPTION, (SQLPOINTER)&uIntVal, (SQLINTEGER) sizeof(uIntVal), NULL);
if (!SQL_OK(iError))
{
printErrors(SQL_NULL_HENV, hDbc, SQL_NULL_HSTMT);
exit(-1);
}
else
{
printf("nConnection attribute SQL_COPT_SS_COLUMN_ENCRYPTION value is : %d", uIntVal);
}
这输出：
Connection attribute SQL_COPT_SS_COLUMN_ENCRYPTION value is : 1
根据给定的 DOC - 这代表 SQL_CE_RESULTSETONLY - 它允许"仅解密"。
我重新安装了最新的 ODBC 版本 - 徒劳无功。发出相同的错误。
我错过了什么。
谢谢
希勒尔。