SQL参数化查询详解

2011.01SQL1SQL。，SQL，“SE-LECTcolumn1,column2,…FROMtable1WHEREparam_col-umn1=value1ANDparam_column2=‘value2’”。param_column1、param_column2value1、value2，table1column1、col-umn2。param_column1，param_col-umn2。，value1value2，SQL。SQL，“SELECTcolumn1,column2,…FROMtable1WHEREparam_column1=”+int_to_str(int_value)+“ANDparam_column2=‘”+str_value+“’”。C，“SELECTcolumn1,column2,…FROMtable1WHEREparam_column1=%dANDparam_column2=‘%s’”，%，SQL。SQL，。，SQL。SQL，，。，，param_column1param_column2，SQL，。，SQL，SQL。SQL，param_column2“’OR‘’=‘”（），SQL“SELECTcolumn1,column2,…FROMtable1WHEREparam_column1=value1ANDparam_col-umn2=‘’OR‘’=‘’”，WHERE，table1。，param_column2“’;DELETEFROMtable1WHERE‘’=‘”（），SQL“SELECTcolumn1,column2,…FROMtable1WHEREparam_col-umn1=0ANDparam_column2=‘’;DELETEFROMtable1WHERE‘’=‘’”，，table1。。SQL，“SELECTcolumn1,col-umn2,…FROMtable1WHEREparam_column1=?ANDparam_column2=?”。（？）。，，“SELECTcolumn1,column2,…FROMtable1WHEREparam_column1=@param1ANDparam_column2=@param2”。，。，，SQL，。，。，。，，SQL，，。，SQL。，。，SQL、。。。、、。，。。ADO、ADO.NET、ODBC3。2ADOADO，Connection，CommandSQL。SQL，Recordset，：SQL，ADO、ADO.NET、ODBC。：SQL；；；；ADO；ADO.NET；ODBC422011.01Recordset，（Recordset）。，Command，ConnectionRecordset。，Command。，SQL（），SQL，Command。Parameter。Parameter，Name，Value，Direction、，Type，。CommandParameters，Parameters，SQL（）。Parameters，。CommandCreateParameterParameter，ParametersAppend。CommandPrepared，True，。ADO，。，Access。d:\db.mdb，table1，column1，column2。“”、“”、“”3，，，“”。DimcnnAsConnection'AccessDimcmd1AsCommand'SQLDimcmd2AsCommand'SQLDimrsAsRecordset'Dimparam1AsParameter'Dimparam2AsParameter'Setcnn=NewConnectioncnn.OpenProvider=Microsoft.Jet.OLEDB.4.0;DataSource=d:\db1.mdbcnn.ExecuteDELETEFROMtable1,,adExecuteNoRecords'3Setcmd1=NewCommandcmd1.ActiveConnection=cnncmd1.CommandType=adCmdText'SQLcmd1.CommandText=INSERTINTOtable1VALUES(?,?)cmd1.Prepared=True'Setparam1=cmd1.CreateParameter(column1,adInteger)cmd1.Parameters.Appendparam1Setparam2=cmd1.CreateParameter(column2,adVarChar,,100)cmd1.Parameters.Appendparam2'1,param1.Value=1param2.Value=cmd1.Execute,,adExecuteNoRecords'2,param1.Value=2param2.Value=cmd1.Execute,,adExecuteNoRecords'3,param1.Value=3param2.Value=cmd1.Execute,,adExecuteNoRecords'2Setcmd2=NewCommandcmd2.ActiveConnection=cnncmd2.CommandType=adCmdText'SQLcmd2.CommandText=SELECTcolumn2FROMtable1WHEREcolumn1=?cmd2.Prepared=True'cmd2.Parameters.Appendcmd2.CreateParameter(column1,adInteger,,,2)'Setrs=cmd2.ExecuteIfNotrs.EOFThenMsgBoxrs(0)EndIfVB。，ASP。VC：//，msado15.dll//#importmsado15.dllno_namespacerename(EOF,End-OfFile)//COMCoInitialize(NULL);_ConnectionPtrspCnn;_CommandPtrspCmd1;_CommandPtrspCmd2;_RecordsetPtrspRs;_ParameterPtrspParam1;_ParameterPtrspParam2;spCnn.CreateInstance(__uuidof(Connection));spCnn-Open(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=d:\\db1.mdb,,,-1);spCnn-Execute(DELETEFROMtable1,NULL,adExe-cuteNoRecords);//spCmd1.CreateInstance(__uuidof(Command));spCmd1-ActiveConnection=spCnn;432011.01spCmd1-CommandType=adCmdText;//SQLspCmd1-CommandText=INSERTINTOtable1VALUES(?,?);spCmd1-Prepared=VARIANT_TRUE;//spParam1=spCmd1-CreateParameter(column1,adInte-ger,adParamInput,0);spCmd1-Parameters-Append(spParam1);spParam2=spCmd1-CreateParameter(column2,adVar-Char,adParamInput,100);spCmd1-Parameters-Append(spParam2);//1,spParam1-Value=1;spParam2-Value=;spCmd1-Execute(NULL,NULL,adExecuteNoRecords);//2,spParam1-Value=2;spParam2-Value=;spCmd1-Execute(NULL,NULL,adExecuteNoRecords);//3,spParam1-Value=3;spParam2-Value=;spCmd1-Execute(NULL,NULL,adExecuteNoRecords);//2spCmd2.CreateInstance(__uuidof(Command));spCmd2-ActiveConnection=spCnn;spCmd2-CommandType=adCmdText;//SQLspCmd2-CommandText=SELECTcolumn2FROMtable1WHEREcolumn1=?;spCmd2-Prepared=VARIANT_TRUE;//spCmd2-Parameters-Append(spCmd2-CreateParame-ter(column1,adInteger,adParamInput,0,2));//spRs=spCmd2-Execute(NULL,NULL,0);if(!spRs-EndOfFile){::MessageBox(NULL,(_bstr_t)spRs-Collect[0L],NULL,MB_ICONINFORMATION);}，。，OLEDB，CreateParameter，。，MSSQLServerpubs，ByRoyalty。，（MSDN）。DimcnnAsConnection'SQLServerpubsDimcmdByRoyaltyAsCommand'ByRoyaltyDimrsByRoyaltyAsRecordset'ByRoyaltyDimintRoyaltyAsInteger'ByRoyaltySetcnn=NewConnection'cnn.OpenProvider=SQLOLEDB;DataSource=(local);InitialCatalog=pubs;UserId=sa;Password='SetcmdByRoyalty=NewCommandcmdByRoyalty.ActiveConnection=cnncmdByRoyalty.CommandType=adCmdStoredProccmdByRoyalty.CommandText=ByRoyaltycmdByRoyalty.Prepared=True'intRoyalty=Trim(InputBox(:))cmdByRoyalty.Parameters(Royalty).Value=intRoyaltySetrsByRoyalty=cmdByRoyalty.Execute'ADO。VisualBasic、DelphiADO，。SQL，。ADOCommandVCLTADOCommand，SQL。SQL“SELECTcolumn1,column2,…FROMtable1WHEREparam_column1=?ANDparam_column2=?”VCL“SELECTcolumn1,column2,…FROMtable1WHEREparam_column1=:Param_column1ANDparam_column2=:Param_column2”。，VCL。VCLSQL（），。。，ADO，VCL。VisualBasic。，VisualBa-sic。，，，。，cmd1cmd2，SQLINSERTINTOtable1VALUES(?,?)SELECTcolumn2FROMtable1WHEREcolumn1=?。，F2，，cmd1cmd2，cmd1，cmd2442011.01，SQL。，RecordsetrsCmd2，cmd2，rsCmd2。3ADO.NETADO.NET，IDbConnec-tion，，IDb-CommandSQL。SQL，。，，SQL（），SQL，。IDbDataParameter。，ParameterName，