The use of SqlParameter

The use 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 quotes and truncated strings, the "or 1=1" of the permanent occurrence of the table is exposed to some information, if the SQL statement is a SELECT * from, you may be the entire table of information will be read, more serious, if the malicious use of using the drop command, Then maybe your entire database will crash across the line.

Of course, the focus now is not on the harm of SQL injection, but on how to avoid injecting problems to the fullest extent.

SQL injection exists in the biggest harm, is 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, so to avoid SQL injection, You must separate the query statement from the control statement.

SqlParameter gives us a good class, with it, we can not join the string, we can no longer worry about the tragedy of single quotes, because all this will be done for us.

Simply give an example

The SQL of a traditional query statement might be
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 and 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 guarantee that the external parameters can be converted correctly, the dangerous characters of single quotes will be escaped, and the library will no longer be a threat.

Of course, this is only an example, in the real situation, you may also want to request.querystring["u_id"] to do the necessary testing and analysis, so that the security

Therefore, it is a good practice to use parameterized SQL statements.

Dim SQL as StringBuilder = New StringBuilder ()
Sql. Append ("")
Sql. Append ("SELECT * FROM Test")
Sql. Append ("WHERE a= @p1")

Dim command as SqlCommand = DAC. CreateCommand (SQL. ToString ()) ' DAC class written for itself
Dim param as SqlParameter = New SqlParameter ()
Param. ParameterName = "@p1"
Param. SqlDbType = SqlDbType.NVarChar
Param. Value = B ' B is the parameter of the function (ByVal B as String)
command. Parameters.Add (param)
Dim reader as SqlDataReader = command. ExecuteReader ()

SqlParameter constructor function

SqlParameter Constructor (string, SqlDbType, Int32, ParameterDirection, Byte, Byte, String, DataRowVersion, Boolean, Object, S Tring, 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, the precision, the number of decimal digits, the name of the source column, one of the DataRowVersion values, The Boolean value used for the source column mapping, the value of the SqlParameter, the name of the database where the schema collection for this XML instance resides, the relational schema where the schema collection for this XML instance resides, and the name of the schema collection for this parameter.
Namespace: System.Data.SqlClient
Assembly: System.Data (in system.data.dll)

C#
Public SqlParameter (
String parametername,
SqlDbType DbType,
int size,
ParameterDirection direction,
BYTE Precision,
BYTE scale,
String SourceColumn,
DataRowVersion SourceVersion,
BOOL Sourcecolumnnullmapping,
Object value,
String XmlSchemaCollectionDatabase,
String XmlSchemaCollectionOwningSchema,
String XmlSchemaCollectionName
)

Parameters
ParameterName
The name of the parameter to map.

DbType
One of the SqlDbType values.

Size
The length of the parameter.

Direction
One of the ParameterDirection values.

Precision
The total number of digits to the left and right sides of the decimal to resolve Value to.

Scale
The total number of decimal digits to resolve Value to.

SourceColumn
The name of the source column.

SourceVersion
One of the 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
The relational schema that contains 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.

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 __gc __sealed class Sqlparameter:public
MarshalByRefObject, IDbDataParameter, Idataparameter, ICloneable
[JScript]
public class SqlParameter extends MarshalByRefObject implements
IDbDataParameter, 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, 80). Value = "Toasters"
MYDATAADAPTER.SELECTCOMMAND.PARAMETERS.ADD ("@SerialNum", SqlDbType.Int). Value = 239

Mydataadapter.fill (myDataSet)
End Sub ' Addsqlparameters

[C #]
public void Addsqlparameters ()
{
// ...
Create myDataSet and MyDataAdapter
// ...

MYDATAADAPTER.SELECTCOMMAND.PARAMETERS.ADD ("@CategoryName", SqlDbType.VarChar, 80). Value = "toasters";
MYDATAADAPTER.SELECTCOMMAND.PARAMETERS.ADD ("@SerialNum", SqlDbType.Int). Value = 239;
Mydataadapter.fill (myDataSet);

}

[C + +]
Public
void Addsqlparameters ()
{
// ...
Create myDataSet and MyDataAdapter
// ...

Mydataadapter->selectcommand->parameters->add (S "@CategoryName", Sqldbtype::varchar,)->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 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);
Parm. 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 int Iarticle.insert (Articleinfo article)
{
If an object exists
if (article.id! =-1)
return-1;
Else
Article.id = Tablehelper.getsequence (sqlhelper.connectionstring, "article", "ID"); Count the number of successful executions
int successcount = 0;             string sql_this = Sql_insert_article;             Sqlparameter[] paras = Getparas (); Paras[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[10]. Value = article. CategoryID;
PARAS[11]. Value = article. Summary;
PARAS[12]. Value = article. Isbysummary;
PARAS[13]. Value = article. dateupdated;
PARAS[14]. Value = article. Titleurl;
PARAS[15]. Value = article. Feedbackcount;
PARAS[16]. Value = article. Postconfig;
PARAS[17]. Value = article. EntryName;
PARAS[18]. Value = article.             KeyWord; SqlConnection conn = new SqlConnection (sqlhelper.connectionstring);
Successcount = Sqlhelper.executenonquery (conn, CommandType.Text, Sql_this, paras);

return successcount;
}

The use of SqlParameter