OLE DB parameterized Query

In general, the SQL query is relatively fixed, a statement changes may only be conditional values, such as the previous request to query the second-year student information, and then need to query the third-grade information, such queries generally query the column unchanged, the following conditions only the value in the change, For this kind of query can use parameterized query way to improve efficiency, also can SQL operation more security, fundamentally eliminate the problem of SQL injection.

Advantages of parameterized Queries:

1. Improve efficiency: Previously said, the database in the process of executing SQL, each time through the SQL parsing, compiling, calling the corresponding database components, so that if the execution of the same type of SQL statements, parsing, compiling process is obviously a waste of resources, The parameterized query is to use the compiled process (that is, to tell the database what database components to call in advance), so that the parsing of SQL statements, the compilation process, improve the efficiency (this process I feel a bit like the C + + language compiler execution and scripting language interpretation of execution).
2. More secure: From a security programming standpoint, it is more efficient and easier to implement than keyword filtering for preventing SQL injection.

Popular SQL injection and security programming

• What is SQL injection:

SQL injection, by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually achieves a malicious SQL command that deceives the server. For example, when a user logs in, a user name password is entered, and the SQL statement can be executed in the background.
SQL select count(*) from user where username = ‘haha‘ and password = ‘123456‘
Only enter the user name and password to log in, but if the user input is not verified, when the user input some SQL statements, the latter directly splicing user input and execution, it will occur injection, such as the user input * * * * * * haha ' or 1 = 1--* * *, The SQL statement that executes in the background now becomes this:
SQL select count(*) from user where username = ‘haha‘ or 1 = 1 -- and password = ‘‘
This allows the user to log in directly using the user name without a password. And to prevent such attacks, the general use of keyword filtering, but keyword filtering does not eliminate such tools, when a momentary neglect to forget to filter a keyword will still produce such problems. and keyword filtering generally uses regular expressions, and regular expressions are not something that can be mastered by the average person. The simplest and easiest way to prevent SQL injection is to parameterize the query.

• Why parameterized queries can fundamentally address SQL injection

SQL injection occurs because the program executes the user input as part of the SQL statement, but the parameterized query simply takes the user input as a parameter, as a condition of the query, and from a database level, it does not correspond to a specific database component, it is just a set of data and will not be executed. Here can be simple to the traditional SQL stitching method to understand the C language of the macro, the macro can also have parameters, but it does not validate the parameters, but simply to replace, then I can use some instructions as parameters passed in, but the function is not the same, the function parameter is a specific type of variable or constant. So parameterized queries fundamentally solve the problem of SQL injection.

Use of parameterized queries

With all the benefits of parameterized queries in front of you, how do you use it?
Database operations are built into languages such as Java, and it does not provide this side of the standard for C + +. Different platforms have their own unique set of mechanisms, but in general, the idea is common, but only grammatical differences, here is the main explanation of how OLE DB is used.

1. Use "?" appears the conditional value constants in the SQL statement to form a new SQL statement, such as the query statement above that can be written in
   SQL select count(*) from user where username = ? and password = ?
2. Call ICommandText's setcommandtext to set the SQL statement.
3. Call Icommandparpare's Prepare method to contain "?" The statements are preprocessed
4. Call the GetParameterInfo method of the ICommandWithParameters method to get the DBPARAMINFO structure of the parameter details (similar to dbcolumninfo)
5. Allocate the corresponding size of the dbbinding buffer to hold the binding information for each parameter
6. Call IAccessor's CreateAccessor method to create the corresponding accessor
7. Allocate buffers for parameters, prepare dbparams structures after setting appropriate parameters
8. Call the Execute method of ICommandText and pass in a pointer to the DBPARAMS structure as a parameter.
9. The result set object returned by the operation

typedefstruct tagDBPROPIDSET {   DBPROPID *   rgPropertyIDs;   ULONG        cPropertyIDs;   GUID         guidPropertySet;} DBPROPIDSET;

The DBPARAMS structure is defined as follows:

typedefstruct tagDBPARAMS{    void *pData;    DB_UPARAMS cParamSets;    HACCESSOR hAccessor;}   DBPARAMS;

• Pdata is the buffer of preserving parameter information;
• Cparamsets: Indicates how many parameters
• Haccessor: Accessor handle to the binding structure previously obtained

Here is an example of use:

BOOL Querydata (lpolestr pquerystr, iopenrowset* piopenrowset, irowset* &pirowset) {IAccessor *pParamAccessor = NULL ;//accessor interfaces related to parameterized queriesLpolestr PSQL = _t ("SELECT * from Aa26 Where Left (aac031,2) =?");//Parameterized Query statementsBOOL 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;//Set up SQLhres = Picommandtext->setcommandtext (Dbguid_default, PSQL);//Preprocessing SQL commandsPicommandprepare->prepare (0); hres = Picommandtext->queryinterface (Iid_icommandprepare, (void* *) &picommandprepare);//Get parameter informationhres = Picommandtext->queryinterface (Iid_icommandwithparameters, (void* *) &picommandwithparameters); Com_success (hres, _t ("The query interface ICommandWithParameters failed with the error code:%08x\n"), hres);    hres = Picommandwithparameters->getparameterinfo (&uparams, &rgparaminfo, &pParamBuffer); Com_success (hres, _t ("failed to get parameter information, error code:%08x\n"), hres); rgparambinding = (dbbinding*) MALLOC (sizeof(dbbinding) * uparams); ZeroMemory (Rgparambinding,sizeof(dbbinding) * uparams);//binding parameter information     for(inti =0; i < Uparams;        i++) {rgparambinding[i].bprecision = rgparaminfo[i].bprecision;        Rgparambinding[i].bscale = Rgparaminfo[i].bscale; Rgparambinding[i].cbmaxlen =7*sizeof(WCHAR);//The maximum length of the administrative district number is 6Rgparambinding[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); }//Get accessorPdbbindstatus = (dbbindstatus*) MALLOC (Uparams *sizeof(Dbbindstatus)); ZeroMemory (Pdbbindstatus, Uparams *sizeof(Dbbindstatus)) Pparamaccessor->createaccessor (Dbaccessor_parameterdata, Uparams, rgparambinding, DwOffset, &hAccessor,    Pdbbindstatus); Com_success (hres, _t ("The get parameter accessor failed with the error code:%08x\n"), hres);//Preparation ParametersDbparams.pdata = MALLOC (DwOffset);    ZeroMemory (Dbparams.pdata, DwOffset);    Dbparams.cparamsets = Uparams; Dbparams.haccessor = Haccessor; for(inti =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); }//Execute SQLhres = Picommandtext->execute (null, iid_irowset, &dbparams, NULL, (iunknown**) &pirowset);returnBRet;}

Full code

OLE DB parameterized Query