一般情况下,SQL查询是相对固定的,一条语句变化的可能只是条件值,比如之前要求查询二年级学生信息,而后面需要查询三年级的信息,这样的查询一般查询的列不变,后面的条件只有值在变化,针对这种查询可以使用参数化查询的方式来提高效率,也可以时SQL操作更加安全,从根本上杜绝SQL注入的问题。
前面说了这么多参数化查询的好处,那么到底怎么使用它呢? 在Java等语言中内置了数据库操作,而对于C/C++来说,它并没有提供这方方面的标准。不同的平台有自己独特的一套机制,但是从总体来说,思想是共通的,只是语法上的不同,这里主要是说明OLEDB中的使用方式。
select count(*) from user where username = ? and password = ?
typedef struct tagDBPROPIDSET {
DBPROPID * rgPropertyIDs;
ULONG cPropertyIDs;
GUID guidPropertySet;
} DBPROPIDSET;
DBPARAMS结构的定义如下:
typedef struct tagDBPARAMS
{
void *pData;
DB_UPARAMS cParamSets;
HACCESSOR hAccessor;
} DBPARAMS;
下面是一个使用的例子:
BOOL QueryData(LPOLESTR pQueryStr, IOpenRowset* pIOpenRowset, IRowset* &pIRowset)
{
IAccessor *pParamAccessor = NULL; //与参数化查询相关的访问器接口
LPOLESTR pSql = _T("Select * From aa26 Where Left(aac031,2) = ?"); //参数化查询语句
BOOL bRet = FALSE;
DB_UPARAMS uParams = 0;
DBPARAMINFO* rgParamInfo = NULL;
LPOLESTR pParamBuffer = NULL;
DWORD dwOffset = 0;
DBBINDING *rgParamBinding = NULL;
HACCESSOR hAccessor = NULL;
DBPARAMS dbParams = {0};
DBBINDSTATUS *pdbBindStatus = NULL;
//设置SQL
hRes = pICommandText->SetCommandText(DBGUID_DEFAULT, pSql);
//预处理SQL命令
pICommandPrepare->Prepare(0);
hRes = pICommandText->QueryInterface(IID_ICommandPrepare, (void**)&pICommandPrepare);
//获取参数信息
hRes = pICommandText->QueryInterface(IID_ICommandWithParameters, (void**)&pICommandWithParameters);
COM_SUCCESS(hRes, _T("查询接口ICommandWithParameters失败,错误码为:%08x\n"), hRes);
hRes = pICommandWithParameters->GetParameterInfo(&uParams, &rgParamInfo, &pParamBuffer);
COM_SUCCESS(hRes, _T("获取参数信息失败,错误码为:%08x\n"), hRes);
rgParamBinding = (DBBINDING*)MALLOC(sizeof(DBBINDING) * uParams);
ZeroMemory(rgParamBinding, sizeof(DBBINDING) * uParams);
//绑定参数信息
for (int i = 0; i < uParams; i++)
{
rgParamBinding[i].bPrecision = rgParamInfo[i].bPrecision;
rgParamBinding[i].bScale = rgParamInfo[i].bScale;
rgParamBinding[i].cbMaxLen = 7 * sizeof(WCHAR); //行政区编号最大长度为6
rgParamBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgParamBinding[i].dwPart = DBPART_LENGTH | DBPART_VALUE;
rgParamBinding[i].eParamIO = DBPARAMIO_INPUT;
rgParamBinding[i].iOrdinal = rgParamInfo[i].iOrdinal;
rgParamBinding[i].obLength = dwOffset;
rgParamBinding[i].obStatus = 0;
rgParamBinding[i].obValue = dwOffset + sizeof(ULONG);
rgParamBinding[i].wType = DBTYPE_WSTR;
dwOffset = dwOffset + sizeof(ULONG) + rgParamBinding[i].cbMaxLen;
dwOffset = UPROUND(dwOffset);
}
//获取访问器
pdbBindStatus = (DBBINDSTATUS*)MALLOC(uParams * sizeof(DBBINDSTATUS));
ZeroMemory(pdbBindStatus, uParams * sizeof(DBBINDSTATUS))
pParamAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA, uParams, rgParamBinding, dwOffset, &hAccessor, pdbBindStatus);
COM_SUCCESS(hRes, _T("获取参数访问器失败,错误码为:%08x\n"), hRes);
//准备参数
dbParams.pData = MALLOC(dwOffset);
ZeroMemory(dbParams.pData, dwOffset);
dbParams.cParamSets = uParams;
dbParams.hAccessor = hAccessor;
for (int i = 0; i < uParams; i++)
{
*(ULONG*)((BYTE*)dbParams.pData + rgParamBinding[i].obLength) = _tcslen(pQueryStr) * sizeof(WCHAR);
StringCchCopy((LPTSTR)((BYTE*)dbParams.pData + rgParamBinding[i].obValue), _tcslen(pQueryStr) + 1, pQueryStr);
}
//执行SQL
hRes = pICommandText->Execute(NULL, IID_IRowset, &dbParams, NULL, (IUnknown**)&pIRowset);
return bRet;
}