Parameterized SQL statement

There are two ways to avoid SQL injection:
First, all SQL statements are stored in the stored procedure. This not only avoids SQL injection, but also improves performance. In addition, the stored procedure can be managed by a dedicated database administrator (DBA) writing and centralized management. However, this method sometimes queries the same tables with different conditions, and SQL statements may be different. In this way, a large number of stored procedures will be compiled, so someone proposed the second solution: parameterized SQL statements. For example, if we search for all female users in the userinfo table created in this article, our SQL statement may be as follows:

 
Select * From userinfo where sex = 0
 

In the parameterized SQL statement, we provide the values in the form of parameterization. For the preceding query, we use the parameterized SQL statement as follows:

 
Select * From userinfo where sex = @ sex
 

ThenCodeAssign values to the parameters in this SQL statement. If we want to find all male users older than 30 in the userinfo table, this parameterized SQL statement can be written as follows:

 
Select * From userinfo where sex = @ sex and age> @ age
 

The following code returns the query result set in datatable mode:

// Instantiate the connection object sqlconnection connection = new sqlconnection ("Server = localhost; database = pubs; uid = sa; Pwd = ''"); // instantiate the command object sqlcommand command = new sqlcommand ("select * From userinfo where sex = @ sex and age> @ age", connection ); // example command for adding query parameters. parameters. addwithvalue ("@ sex", true); // example of sqlparameter parameter = new sqlparameter ("@ age", sqldbtype. INT); // note that the age field in the userinfo table is of the int type parameter. value = 30; command. parameters. add (parameter); // Add parameters // instantiate dataadapter sqldataadapter adapter = new sqldataadapter (command); datatable DATA = new datatable ();
 

The above code is the code used to access the SQL Server database. If the data mentioned in this article is in access, MySQL, and Oracle databases, the corresponding parameterized SQL statements and parameters are as follows:

database	access	mysql	Oracle
SQL statement	select * From userinfo where sex =? And age>?	select * From userinfo where sex =? Sex and age>? Age	select * From userinfo where sex =: Sex and age>: age
parameters	oledbparameter	mysqlparameter	oracleparameter
instantiation parameters	oledbparameter P = new oledbparameter ("?", Oledbtype. Boolean);	mysqlparameter P = new mysqlparameter ("? Sex ", mysqldbtype. Bit);	oracleparameter P = new oracleparameter (": sex", oracletype. byte);
value assignment	P. value = true;	P. value = 1;	P. value = 1;

Through the instance code above, we can see that although SQL statements are similar in general, the characteristics of different databases may vary with parameterized SQL statements, for example, in access, the parameterized SQL statement directly uses "?" As the parameter name, in SQL Server, the parameter has the "@" prefix, and in MySQL, the parameter has "?" Prefix. in Oracle, the parameter is prefixed.
Note:Because the parameter names in access are all "?", Therefore, you must assign values to parameters in the column order. Otherwise, an error may occur.

 

Test the parameter passing efficiency of the command object

In. NET platform, the general insert statement can be written in two ways, without the insert into test (C1, C2) values (var1, var2) and insert into test (C1, C2) parameters) values (: C1,: C2), how is their execution efficiency?
The Code is as follows: (the database is Oracle)

Public partial class webform1: system. web. UI. page {protected void page_load (Object sender, eventargs e) {// test1 (); Test2 () ;}private void test1 () {oracleconnection con = new oracleconnection (); con. connectionstring = "Data Source = L; user id = xxx; Password = xxx; persist Security info = true;"; system. random r = new random (INT) system. datetime. now. ticks); string strcommand = "insert into test (C1, C2) values ({0 },{ 1})"; oraclecommand COM = new oraclecommand (); COM. connection = con; con. open (); datetime dt = datetime. now; label1.text = "do not pass parameter:" + datetime. now. tolongtimestring (); For (INT I = 0; I <50000; I ++) {COM. commandtext = string. format (strcommand, R. next (), R. next (); COM. executenonquery ();} COM. commandtext = "truncate table test"; COM. executenonquery (); con. close (); label2.text = datetime. now. tolongtimestring ();} private void Test2 () {oracleconnection con = new oracleconnection (); con. connectionstring = "Data Source = bocodb; user id = hljyd; Password = hljyd; persist Security info = true;"; system. random r = new random (INT) system. datetime. now. ticks); string strcommand = "insert into test (C1, C2) values (: C1,: C2)"; oraclecommand COM = new oraclecommand (); COM. parameters. add (": C1", oracletype. number); COM. parameters. add (": C2", oracletype. number); COM. commandtext = strcommand; COM. connection = con; con. open (); label1.text = "parameter:" + datetime. now. tolongtimestring (); For (INT I = 0; I <50000; I ++) {COM. parameters [": C1"]. value = R. next (); COM. parameters [": C2"]. value = R. next (); COM. executenonquery ();} COM. parameters. clear (); COM. commandtext = "truncate table test"; COM. executenonquery (); con. close (); label2.text = datetime. now. tolongtimestring ();}}

Execution result:
10000 records:
Do not pass parameters? 5:46:19 15:46:34 15 seconds
Pass Parameter :? 5:50:51 15:51:01 10 seconds

50000 records:
If the parameter is not set, 16:09:03, 81 seconds
Parameter: 16: 15: 43 16:16:36 53 seconds
This is only the case of two parameters. If there are many parameters, will it have a greater impact?

10000 records, 7 parameters:
Do not pass the parameter: 17: 11: 01 17:11:18 17 seconds
Parameter: 17: 13: 46 17:13:59 13 seconds
50000 record: 7 parameters:
Do not pass the parameter: 17: 19: 02 17:20:25 1 minute 23 seconds
Parameter: 17: 15: 09 17:16:10 1 minute 1 second

The difference is not big, but passing parameters to the command object can avoid SQL Injection problems and improve performance;