VS. NET (C #) Database Interface: SqlCommand object SqlParameter defends against "SQL injection" attacks, vs.net

VS. NET (C #) Database Interface: SqlCommand object SqlParameter defends against "SQL injection" attacks, vs.net

When updating a able or DataSet, if SqlParameter is not used, the entered SQL statement is ambiguous. If a string contains single quotes, an error will occur, in addition, you can easily concatenate SQL statements to launch injection attacks.

SqlCommand objects can use parameters (SqlParameter) to pass values to SQL statements or stored procedures. Unlike command text, parameter input is considered a text value, rather than executable code.

SqlParameter also improves query execution performance because it helps database servers accurately match incoming commands with appropriate cache query plans.

Parameter objects for different data sources

Paramter Object Attributes

DbType: Get or set the Data Type of the parameter.

Direction: gets or sets a value indicating whether the parameter can be input, output, bidirectional, or stored procedure return value.

IsNullable: gets or sets a value that indicates whether the parameter can be blank.

ParamteterName: Get or set the DbParamter name.

Size: obtains or sets the maximum Size of data in a column.

Value: gets or sets the Value of this parameter.

Case code: modify customer information

1. architecture of the project organization file

1. The code file MshConnection. cs connects to the database and constructs the SqlConnection object.

2. The code file MshCommand. cs implements database CURD operations and constructs SqlCommand objects.

3. The code file MshBusinessLogic. cs is used to implement the business logic class required by the customer, such as splicing SQL statements and parameters and returning the call results.

4. The code file Program. cs mainly contains the mian () function and calls the business logic

2. The code for the four files is as follows:

MshConnection. cs file, the Code is as follows

Using System; using System. collections. generic; using System. linq; using System. text; using System. data; using System. data. sqlClient; namespace SqlCommandParameter {class MshConnection {private SqlConnection conn = null; // class variable: stores the SqlConnection object private bool flage = false; // indicates whether the object is successfully created: false/true private string runCLassInfo = null; // information about public MshConnection () during object running {// 1. method for constructing a connection string SqlConnectionStringBui Lder connStr = new SqlConnectionStringBuilder (); connStr. dataSource = "192.168.1.20"; connStr. initialCatalog = "mshDB_Debug"; connStr. userID = "developer"; connStr. password = "developer"; connStr. pooling = true; connStr. maxPoolSize = 1000; connStr. minPoolSize = 1; // 2. create the Connection object try {this. conn = new SqlConnection (connStr. toString (); if (conn! = Null) {this. flage = true; // if the Object is successfully created, the value true. this. runCLassInfo + = "\ n1.Create SqlConnection Object Success! ";}} Catch (Exception ex) {this. runCLassInfo + = "\ n1.Create SqlConnection Object Failure:" + ex. message; this. flage = false ;}/// construct the Connection object public bool Flage {get {return this. flage ;}} public string RunClassInfo {get {return this. runCLassInfo;} public SqlConnection Connection {get {return this. conn ;}}}}

MshCommand. cs file, the Code is as follows

Using System; using System. collections. generic; using System. linq; using System. text; using System. data; using System. data. sqlClient; namespace SqlCommandParameter {class MshCommand {private SqlConnection conn = null; private SqlCommand sqlcmd = null; private StringBuilder strSQLText = null; // SQL statement private SqlParameter [] paras = null; // The input parameter private string runClassInfo = null; // Save the private strin information during the class running G Error = null; public MshCommand () {MshConnection mshconn = new MshConnection (); // 1. generate connection object this. conn = mshconn. connection; this. runClassInfo + = "\ n1.Create SqlConnection Object Success! "; This. sqlcmd = new SqlCommand (); this. sqlcmd. connection = this. conn; // 2. connect the Connection object this to the Command object. runClassInfo + = "\ n2.Create SqlCommand Object Success! ";}~ MshCommand () {this. conn. dispose ();} public string RunClassInfo {get {return this. runClassInfo;} public StringBuilder StrSQLText // class public attributes: SQL statement {get {return this. strSQLText;} set {this. strSQLText = value; sqlcmd. commandText = this. strSQLText. toString (); // assign the SQL statement sqlcmd. commandType = CommandType. text ;}} public SqlParameter [] Paras // class public attributes: SQL statement parameters (arrays) {get {return this. paras;} set {This. paras = value; // traverse the foreach (var item in this. paras) {sqlcmd. parameters. add (item) ;}} public int ExecuteScalar () // execute the ExecuteScalar () method to obtain the single value {int result = 0; try {this. conn. open (); // Open the connection with the database result = (int) this. sqlcmd. executeScalar (); return result;} catch (Exception ex) {this. error = ex. message; return result;} finally {this. conn. close (); // display Close connection with database} public I Nt ExecuteNonQuery () {int result = 0; try {this. conn. Open (); this. runClassInfo + = "\ n3.Open SqlConnecte DB Success! "; Result = this. sqlcmd. executeNonQuery (); return result;} catch (Exception ex) {this. error = ex. message; return result;} finally {this. conn. close (); this. runClassInfo + = "\ n4.Closed SqlConnecte DB Success! ";}}}}

MshBusinessLogic. cs file, the Code is as follows:

Using System; using System. collections. generic; using System. linq; using System. text; using System. data; using System. data. sqlClient; namespace SqlCommandParameter {class MshBusinessLogic {// insert a line of record to the customer table public void InsertCustomer () {int rows = 0; // concatenate the SQL statement StringBuilder strSQL = new StringBuilder (); strSQL. append ("insert into tb_Customer"); strSQL. append ("values ("); strSQL. append ("@ Name, @ Sex, @ CustomerType, @ Phone, @ Email, @ ContactAddress, @ Lat, @ Lng, @ Postalcode, @ Remark"); strSQL. append (")"); // construct the Parameter object SqlParameter [] paras = new SqlParameter [] {new SqlParameter ("@ Name", SqlDbType. varChar, 20), new SqlParameter ("@ Sex", SqlDbType. char, 1), new SqlParameter ("@ CustomerType", SqlDbType. char, 1), new SqlParameter ("@ Phone", SqlDbType. varChar, 12), new SqlParameter ("@ Email", SqlDbType. varChar, 50), new SqlParameter ("@ ContactAddress", SqlDbType. varChar, 200), new SqlParameter ("@ Lat", SqlDbType. float, 10), new SqlParameter ("@ Lng", SqlDbType. float, 10), new SqlParameter ("@ Postalcode", SqlDbType. varChar, 10), new SqlParameter ("@ Remark", SqlDbType. varChar, 20)}; // assign paras [0] To the Parater object. value = "test user"; paras [1]. value = "0"; paras [2]. value = "0"; paras [3]. value = "138222233"; paras [4]. value = "liuhaorain@163.com"; paras [5]. value = "Baoan District, Shenzhen City, Guangdong Province"; paras [6]. value = 12234567890123.456789; paras [7]. value = 34.222234; paras [8]. value = "314200"; paras [9]. value = "Remarks"; // generate a SqlCommand object to execute the preceding SQL statement MshCommand cmd = new MshCommand (); cmd. strSQLText = strSQL; cmd. paras = paras; rows + = cmd. executeNonQuery (); rows + = cmd. executeNonQuery (); Console. writeLine ("Insert Success: {0} rows", rows);} // updates customer information. Familiar with SqlParameter object use public void UpdateCustomer () {// concatenate the SQL statement StringBuilder strSQL = new StringBuilder (); strSQL. append ("Update tb_Customer Set"); strSQL. append ("Phone = @ Phone,"); strSQL. append ("Email = @ Email,"); strSQL. append ("ContactAddress = @ Address"); strSQL. append ("where Name = @ Name"); // construct the Parameter object SqlParameter [] paras = new SqlParameter [] {new SqlParameter ("@ Phone", SqlDbType. varChar, 12), new SqlParameter ("@ Email", SqlDbType. varChar, 50), new SqlParameter ("@ Address", SqlDbType. varChar, 200), new SqlParameter ("@ Name", SqlDbType. varChar, 20)}; // assign paras [0] To the Parater object. value = "18665691100"; paras [1]. value = "test@163.com"; paras [2]. value = "Nanshan District, Shenzhen City, China"; paras [3]. value = "Kemi"; // generate a SqlCommand object to execute the preceding SQL statement MshCommand cmd = new MshCommand (); cmd. strSQLText = strSQL; cmd. paras = paras; int rows = cmd. executeNonQuery (); Console. writeLine ("Update Success: {0} rows", rows);} // obtain the ID of the inserted row. Use public void getInsertedID () for OUTPUT parameters () {// concatenate the SQL statement StringBuilder strSQL = new StringBuilder (); strSQL. append ("insert tb_Customer (Name)"); strSQL. append ("OUTPUT inserted. ID values (@ Name) "); // construct the Parameter object SqlParameter [] paras = new SqlParameter [] {new SqlParameter (" @ Name ", SqlDbType. varChar, 20)}; // assign paras [0] To the Parater object. value = "Kemi"; // generate a SqlCommand object to execute the preceding SQL statement MshCommand cmd = new MshCommand (); cmd. strSQLText = strSQL; cmd. paras = paras; int insertedID = cmd. executeScalar (); Console. writeLine ("Inserted ID: {0}", insertedID );}}}

Run the following command:

Summary

The SqlCommand object contains a set of Paramters. When executing a command, the SQL text, placeholders, and parameter sets are passed to the database.

For different data sources, Placeholders are different:

The SQLServer data source uses the @ parametername format to name parameters.

OleDb and Odbc data sources both use question marks () to identify the parameter location

Oracle uses Named parameters in the format of parmname.