Usage and benefits of SqlParameter

About the basic concept of SQL injection, I believe it is not necessary to say, we all know that the classic injection statement is ' or 1=1--
single quotation marks and truncate the string, "or 1=1" the ever-true appearance of the table to expose some information, if the SQL statement is a SELECT * from, Perhaps your entire table of information will be read, and more seriously, if you use the drop command for malicious use, then your entire database may crash.
Of course, the focus now is not on the harm of SQL injection, but on how to avoid injecting the problem to the fullest.
SQL injection exists at maximum risk, is that the SQL execution statement is not separated from the control statement, we want to select something, but the user may spell ' or 1=1 even add delete/update/drop, and then belong to the control statement, Therefore, to avoid SQL injection, it is necessary to separate the query statements from the control statements. The
SqlParameter gives us a nice class, and with it, we can not stitch together strings, or we can no longer worry about the tragedy of single quotes, because all this will be done for us. The
Simple example
of a traditional query statement may have a SQL of
string sql= "select * from Users where user_id= '" +request.querystring["UID"]+ "";
Obviously, we're stitching strings here, which leaves the opportunity for SQL injection.
Now we're going to rewrite this statement, use SqlParameter to do
SqlCommand SQLCMD = new SqlCommand (sql, sqlconn);
SqlParameter _userid = new SqlParameter ("UID", SqlDbType.Int);
_userid. Value = request.querystring["u_id"];
SqlCmd.Parameters.Add (_userid);
In this way, we can ensure that the external parameters can be converted correctly, single quotes these dangerous characters will be escaped, no longer pose a threat to the library.
Of course, this is just an example, and in real situations, you might want to do the necessary testing and analysis of request.querystring["u_id" to be safe
so it's a good practice to use parameterized SQL statements

DimSql asStringBuilder =NewStringBuilder () SQL. Append ("") SQL. Append ("SELECT * FROM Test") SQL. Append ("WHERE a= @p1")           Dim Command  asSqlCommand = DAC. CreateCommand (SQL. ToString ())'The DAC writes its own classDimParam asSqlParameter =NewSqlParameter () param. ParameterName="@p1"param. SqlDbType=Sqldbtype.nvarcharparam. Value= b'B is the parameter of the function (ByVal B as String)Command . Parameters.Add (param)DimReader asSqlDataReader =Command. ExecuteReader ()

SqlParameter constructor function
SqlParameter constructor (String, SqlDbType, Int32, ParameterDirection, Byte, Byte, String, DataRowVersion, Boolean, Object, Stri Ng, String, String)
Initializes a new instance of the SqlParameter class that uses the parameter name, the type of the parameter, the length of the parameter, the direction, precision, scale, source column name, one of the DataRowVersion values, the Boolean value used for the source column mapping, the value of the SqlParameter, the XML The name of the database where the instance's schema collection resides, the relational schema where the schema collection for this XML instance resides, and the name of the schema collection for this parameter.
Namespaces: System.Data.SqlClient
Assembly: System.Data (in system.data.dll)

C#:

 PublicSqlParameter (stringparametername, SqlDbType DbType,intsize, ParameterDirection direction,bytePrecision,byteScale ,stringSourceColumn, DataRowVersion sourceversion, bool sourcecolumnnullmapping,Objectvalue,stringXmlSchemaCollectionDatabase,stringXmlSchemaCollectionOwningSchema,stringxmlschemacollectionname)

Parameter
ParameterName
The name of the parameter to map. One of the
DbType
SqlDbType values. The length of the
size
parameter. One of the
direction
ParameterDirection values.
Precision
The total number of digits on both sides of the decimal to which Value is to be parsed.
Scale
The total number of decimal digits to resolve Value to. The name of the
SourceColumn
Source column. One of the
SourceVersion
DataRowVersion values.
Sourcecolumnnullmapping
True if the source column can be null, or False if it is not nullable.
Value
An Object that is the value of the SqlParameter.
XmlSchemaCollectionDatabase
The name of the database that contains the schema collection for this XML instance.
XmlSchemaCollectionOwningSchema
contains the relational schema of the schema collection for this XML instance.
XmlSchemaCollectionName
The name of the schema collection for this parameter.
Note
If size and precision are not explicitly set in the size and precision parameters, they are inferred from the value of the DbType parameter. The
SqlParameter class
represents a SqlCommand parameter, or it can be a mapping to a DataSet column. This class cannot be inherited.
for a list of all members of this type, see SqlParameter members.
System.Object
System.MarshalByRefObject
System.Data.SqlClient.SqlParameter
[Visual Basic]

NotInheritable  Public Class SqlParameter Inherits MarshalByRefObject Implements IDbDataParameter, Idataparameter, ICloneable

[C #]

 Public Sealed class Sqlparameter:marshalbyrefobject,idbdataparameter, Idataparameter, ICloneable

[C + +]

 Public class  Public MarshalByRefObject, IDbDataParameter, Idataparameter, Icloneable[jscript]  Public class SqlParameter extends MarshalByRefObject implementsidbdataparameter, Idataparameter, ICloneable

Thread Safety
All public static (shared in Visual Basic) members of this type are thread-safe. There is no guarantee that any instance members are thread-safe.
Note
Parameter names are case insensitive.
Example
[Visual Basic, C #, C + +] The following example creates multiple instances of SqlParameter through the SqlParameterCollection collection in SqlDataAdapter. These parameters are used to select data from the data source and place the data in the dataset. This example assumes that datasets and SqlDataAdapter have been created with the appropriate schemas, commands, and connections.
[Visual Basic]

Public Sub addsqlparameters ()' ...'Create myDataSet and MyDataAdapter' ...MYDATAADAPTER.SELECTCOMMAND.PARAMETERS.ADD ("@CategoryName", SqlDbType.VarChar, the). Value ="Toasters"MyDataAdapter.SelectCommand.Parameters.Add ("@SerialNum", SqlDbType.Int). Value =239Mydataadapter.fill (myDataSet) End Sub'addsqlparameters

[C #]

 Public voidaddsqlparameters () {// ...//Create myDataSet and MyDataAdapter// ...MYDATAADAPTER.SELECTCOMMAND.PARAMETERS.ADD ("@CategoryName", SqlDbType.VarChar, the). Value ="Toasters"; MYDATAADAPTER.SELECTCOMMAND.PARAMETERS.ADD ("@SerialNum", SqlDbType.Int). Value =239; Mydataadapter.fill (myDataSet);}

[C + +]

 Public:voidaddsqlparameters () {// ...//Create myDataSet and MyDataAdapter// ...Mydataadapter->selectcommand->parameters->add (S"@CategoryName", Sqldbtype::varchar, the)->value = S"Toasters"; MyDataAdapter->selectcommand->parameters->add (S"@SerialNum", sqldbtype::int)->value = __box (239); MyDataAdapter-Fill (myDataSet);}

[JScript] does not have an example that can be used with JScript. To view Visual Basic, C #, or C + + examples, click the Language Filter button in the upper-left corner of the page.
Requirements
Namespaces: System.Data.SqlClient
Platform: Windows 98, Windows NT 4.0, Windows ME, Windows $, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 series,. NET Compact Framework
Assembly: System.Data (in System.Data.dll)
Using SqlParameter

Sqlparameter[] p ={Sqlhelper.makeinparam ("@EntryID", SqlDbType.Int,4, Ev. EntryID), Sqlhelper.makeinparam ("@BlogID", SqlDbType.Int,4, Ev. BlogID), Sqlhelper.makeinparam ("@URL", SqlDbType.NVarChar,255, Datahelper.checknull (EV. Referralurl)), Sqlhelper.makeinparam ("@IsWeb", Sqldbtype.bit,1, Ev. Pageviewtype)}; Sqlhelper.executenonquery (Conn,commandtype.storedprocedure,"Blog_trackentry", p);

Q: How to add a value after SqlParameter assignment
With SqlCommand I know with add
But what I want to ask is SqlParameter.
For example

SqlParameter  parm  =  new  SqlParameter (parm_order_id,  SqlDbType.Int);p arm. Value  =  orderId;

Can we change parm later? Add a value
Because I wrote a way to pass the parameters of the SqlParameter type.
But sometimes we have to make some judgments.
Like

SqlParameter  parm  =  new  SqlParameter (parm_order_id,  SqlDbType.Int); Parm. Value  =  orderId; if (...) {// Add a parameter }executereader (parm,.....);

What should I do?
Best Answer

intIarticle.insert (Articleinfo article) {//if an object existsif(Article.id! =-1)return-1;Elsearticle.id= Tablehelper.getsequence (sqlhelper.connectionstring,"article","ID");//Count the number of successful executionsintSuccesscount =0;stringSql_this =sql_insert_article; Sqlparameter[] Paras=Getparas ();p aras[0]. Value =article.id;paras[1]. Value =article. title;paras[2]. Value =article. dateadded;paras[3]. Value =article. text;paras[4]. Value =article. sourceurl;paras[5]. Value =article. posttype;paras[6]. Value =article. author;paras[7]. Value =article. email;paras[8]. Value =article. sourcename;paras[9]. Value =article. blogid;paras[Ten]. Value =article. categoryid;paras[ One]. Value =article. summary;paras[ A]. Value =article. isbysummary;paras[ -]. Value =article. dateupdated;paras[ -]. Value =article. titleurl;paras[ the]. Value =article. feedbackcount;paras[ -]. Value =article. postconfig;paras[ -]. Value =article. entryname;paras[ -]. Value =article. KeyWord; SqlConnection Conn=NewSqlConnection (sqlhelper.connectionstring); Successcount=sqlhelper.executenonquery (conn, CommandType.Text, Sql_this, paras);returnSuccesscount;}

Source: http://www.cnblogs.com/angelfeather/articles/1225902.html