C语言实现ODBC+SQL插入AES加密数据 sql server

一、ODBC往Sql Server 插入数据的常规操作

代码如下：

void insertData(int i)
{
    SQLRETURN ret; 
    SQLHENV henv;//SQLHANDLE henv 
    SQLHDBC hdbc;//SQLHANDLE hdbc 
    SQLHSTMT hstmt;//SQLHANDLE hstmt 
   
    ret=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//申请环境句柄 
    ret=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//设置环境属性 
    ret=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);//申请数据库连接句柄 
    ret=SQLConnect(hdbc,(SQLCHAR*)"phonesql",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"123456",SQL_NTS);//连接数据库 
    if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){ 
       ret=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);//申请SQL语句句柄 
      SQLCHAR sql[]="INSERT INTO worker VALUES (?,?,?,?,?,?,?,?,?,?);";
      SQLINTEGER P = SQL_NTS;
      ret=SQLPrepare(hstmt,sql,SQL_NTS);
       ret=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&gong[i].num,0,&P);//绑定参数 
       ret=SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,20,0,gong[i].name,20,&P);//绑定参数
       ret=SQLBindParameter(hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,10,0,gong[i].sex,10,&P);//绑定参数
       ret=SQLBindParameter(hstmt,4,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&gong[i].age,0,&P);//绑定参数 
       ret=SQLBindParameter(hstmt,5,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,20,0,gong[i].shenfennum,20,&P);
       ret=SQLBindParameter(hstmt,6,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,10,0,gong[i].xueli,10,&P);
       ret=SQLBindParameter(hstmt,7,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,10,0,gong[i].mianmao,10,&P);
       ret=SQLBindParameter(hstmt,8,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,20,0,gong[i].mima,20,&P);
       ret=SQLBindParameter(hstmt,9,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,10,0,gong[i].quanxian,10,&P);
       ret=SQLBindParameter(hstmt,10,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,20,0,gong[i].born,20,&P);
       ret=SQLExecute(hstmt);//直接执行SQL语句 
       if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){         
            
           /*****************测试结构体数组********************/
         printf("添加第%d条信息成功！\n",i+1); 
   
         SQLFreeHandle(SQL_HANDLE_STMT,hstmt);//释放语句句柄 
       }else printf("添加数据库操作失败！\n"); 
               
         SQLDisconnect(hdbc);//断开与数据库的连接 
    } 
    else printf("连接数据库失败!\n");    
    SQLFreeHandle(SQL_HANDLE_DBC,hdbc);//释放连接句柄 
    SQLFreeHandle(SQL_HANDLE_ENV,henv);//释放环境句柄
 
}

SQLCHAR sql[]="INSERT INTO worker VALUES (?,?,?,?,?,?,?,?,?,?);"; 这个语句中的’？’表示要添加的数据。（添加多少个数据，就写多少个问号），

SQLBindParameter函数说明如下：

SQLRETURN SQLBindParameter(
      SQLHSTMT        StatementHandle,    // statement句柄
      SQLUSMALLINT    ParameterNumber,    // 参数位于语句中的序号，最小为1
      SQLSMALLINT     InputOutputType,    // 入参/出参类型标识[1]
      SQLSMALLINT     ValueType,          // 对应的C数据类型标识[2]
      SQLSMALLINT     ParameterType,      // 对应的SQL数据类型标识[2]
      SQLULEN         ColumnSize,         // 对应字段长度
      SQLSMALLINT     DecimalDigits,      // 如果是浮点数，则对应字段精度
      SQLPOINTER      ParameterValuePtr,  // 参数缓存
      SQLLEN          BufferLength,       // 参数缓存字节数
      SQLLEN *        StrLen_or_IndPtr);  // 用于表示字符串长度或NULL值的标识[3]

具体可至微软MSDN 查询 https://docs.microsoft.com/zh-cn/sql/relational-databases/native-client-odbc-api/sqlbindparameter?view=sql-server-2014&redirectedfrom=MSDN

二、插入的数据进行AES加密（数据库自带AES加密函数）

插入数据的sql如下：

OPEN SYMMETRIC KEY SymmetricByPW decryption by password='Symmetric_ByPW111';
insert into 表名(字段1,字段2,字段3,加密字段 ) values ('mmm','ttt','sss',EncryptByKey(Key_GUID('SymmetricByPW'), convert(varchar(20),'test')))

没找到SQLBindParameter函数绑定EncryptByKey函数的方法。直接绑定把整个函数直接填了进去，而不是函数执行的结果，所有才用的拼接sql的方法，如下：

1、打开秘钥

        char *pOpenKeySql ="OPEN SYMMETRIC KEY SymmetricByPW decryption by password='Symmetric_ByPW111'";
        try
        {
            TOdbcDBQuery Query(&m_ODBCBase);
            Query.SetSQL(pOpenKeySql);
            if (!Query.Execute())
            {
                Query.Rollback();
                LOG(ELOG_ERROR, "OPEN SYMMETRIC KEY  failed.");
                return (int)EC_DB_ERROR;
            }
            //Query.Commit();
            //m_ODBCBase.Commit();
        }
        catch(TOdbcDBException &oe)
        {
            LOG(ERROR,"OPEN SYMMETRIC KEY failed, TDBException:%s",oe.GetErrMsg());
            LOG(ERROR,"OPEN SYMMETRIC KEY failed, QuerySQL:%s",oe.GetErrSql());
            return EC_DB_ERROR;
            //iRet =  EC_DB_ERROR;
        }
        catch(...)
        {
            LOG(ERROR,"OPEN SYMMETRIC KEY failed, Unknow Exception ");
            return EC_DB_ERROR;
        }

2、插入数据

string strSQL = " insert into htmsgGet (字段1,字段2,字段3,加密字段 ) values ";
 try 
{ 
    TOdbcDBQuery Query(&m_ODBCBase); 
    string strInsertSql = strSQL; 
    //拼接sql 
    while (iSz&& !m_queueTmpGet.empty()) 
    { 
      TClientPack tTmp = m_queueTmpGet.front(); 
     if(tTmp.CheckSqlLen()==false) 
     {
       m_queueTmpGet.pop();
       continue;
    } 
     string strPhone = "*******";// 手机号用****代替 
     strPhone.append( tTmp.strClientPhone.substr(tTmp.strClientPhone.length() -4, tTmp.strClientPhone.length())); 
     string strPhoneEncrypt = "EncryptByKey(Key_GUID('SymmetricByPW'), convert(varchar(20),'" + tTmp.strClientPhone+"'))"; 
     string strValue; 
     strValue = "('"+tTmp.字段1+"','"+tTmp.字段2+"','"+tTmp.字段3 +"',"+ strPhoneEncrypt+")," ; strInsertSql += strValue; m_queueTmpGet.pop(); iSz--; } 
    //执行sql 
    strInsertSql.erase(strInsertSql.end()-1);
    Query.SetSQL(strInsertSql.c_str()); 
    if (!Query.Execute()) 
    { 
      Query.Rollback(); 
      LOG(ELOG_ERROR, "Call P_INSERT_MATCH_PRODUCT_INFO failed.");
      return (int)EC_DB_ERROR; 
    } 
    return true; 
}
 catch(TOdbcDBException &oe)
 {
    LOG(ERROR,"sp_update_self_select failed, TDBException:%s",oe.GetErrMsg()); LOG(ERROR,"sp_update_self_select failed, QuerySQL:%s",oe.GetErrSql());
 //iRet = EC_DB_ERROR; 
} 
catch(...)
{ 
}