在ADO.NET中经常需要跟各种数据库打交道，在不实用存储过程的情况下，使用参数化SQL语句一定程度上可以防止SQL注入，同时对一些较难赋值的字段（如在SQL Server中Image字段，在Oracle中Clob字段等）使用参数化SQL语句很容易就能赋值，所以本人经常在ADO.NET中使用参数化SQL语句，近几年来陆续跟SQL Server/Oracle/ MySQL/Access打交道，积累了一些心得，现在整理出来供大家参考。
我们假设数据可的结构如下图（设置的数据库为Oracle10g）：
它在 SQL Server 中的创建语句是：
create
table
S_Admin (
UserName varchar ( 60 ) not null ,
Password varchar ( 60 ) not null ,
Remark varchar ( 50 ) null ,
Mail varchar ( 120 ) not null ,
AddDate datetime null default GETDATE (),
LoginDate datetime null default GETDATE (),
LoginIP varchar ( 50 ) null ,
Active smallint null default 1 ,
LoginCount int null default 1 ,
Power int null default 0 ,
Departid int null default 0 ,
constraint PK_S_ADMIN primary key nonclustered (UserId)
)
go
UserName varchar ( 60 ) not null ,
Password varchar ( 60 ) not null ,
Remark varchar ( 50 ) null ,
Mail varchar ( 120 ) not null ,
AddDate datetime null default GETDATE (),
LoginDate datetime null default GETDATE (),
LoginIP varchar ( 50 ) null ,
Active smallint null default 1 ,
LoginCount int null default 1 ,
Power int null default 0 ,
Departid int null default 0 ,
constraint PK_S_ADMIN primary key nonclustered (UserId)
)
go
下面假设数据库的主键都采用了数据库的本地化技术解决了（例如在Access、SQL Server和MySQL中采用自增字段，在Oracle中使用了sequence结合触发器），假如在Oracle中向表中插入一记录的代码如下：
using
System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
/// <summary>
/// 在Oracle中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址：http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class OracleUtil
{
public OracleUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(:UserName,:Password,:Remark,:Mail,:DepartId,:Power) " ;
OracleConnection connection = new OracleConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
OracleCommand command = new OracleCommand(sql, connection);
command.Parameters.Add( " :UserName " , OracleType.NVarChar, 60 ).Value = userName;
command.Parameters.Add( " :Password " , OracleType.NVarChar, 60 ).Value = password;
command.Parameters.Add( " :Remark " , OracleType.NVarChar, 60 ).Value = remark;
command.Parameters.Add( " :Mail " , OracleType.NVarChar, 60 ).Value = mail;
command.Parameters.Add( " :DepartId " , OracleType.Int32, 4 ).Value = departId;
command.Parameters.Add( " :Power " , OracleType.Int32, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
/// <summary>
/// 在Oracle中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址：http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class OracleUtil
{
public OracleUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(:UserName,:Password,:Remark,:Mail,:DepartId,:Power) " ;
OracleConnection connection = new OracleConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
OracleCommand command = new OracleCommand(sql, connection);
command.Parameters.Add( " :UserName " , OracleType.NVarChar, 60 ).Value = userName;
command.Parameters.Add( " :Password " , OracleType.NVarChar, 60 ).Value = password;
command.Parameters.Add( " :Remark " , OracleType.NVarChar, 60 ).Value = remark;
command.Parameters.Add( " :Mail " , OracleType.NVarChar, 60 ).Value = mail;
command.Parameters.Add( " :DepartId " , OracleType.Int32, 4 ).Value = departId;
command.Parameters.Add( " :Power " , OracleType.Int32, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
在MySQL中增加同样一条记录的代码如下（需要到MySQL官方网站下载.net驱动程序）：
using
System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data;
using MySql.Data.MySqlClient;
/// <summary>
/// 在MySQL中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址： http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class MySqlUtil
{
public MySqlUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?UserName,?Password,?Remark,?Mail,?DepartId,?Power) " ;
MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
MySqlCommand command = new MySqlCommand(sql, connection);
command.Parameters.Add( "? UserName " , MySqlDbType.VarChar, 60 ).Value = userName;
command.Parameters.Add( "? Password " , MySqlDbType.VarChar, 60 ).Value = password;
command.Parameters.Add( "? Remark " , MySqlDbType.VarChar, 60 ).Value = remark;
command.Parameters.Add( "? Mail " , MySqlDbType.VarChar, 60 ).Value = mail;
command.Parameters.Add( "? DepartId " , MySqlDbType.Int32, 4 ).Value = departId;
command.Parameters.Add( "? Power " , MySqlDbType.Int32, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data;
using MySql.Data.MySqlClient;
/// <summary>
/// 在MySQL中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址： http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class MySqlUtil
{
public MySqlUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?UserName,?Password,?Remark,?Mail,?DepartId,?Power) " ;
MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
MySqlCommand command = new MySqlCommand(sql, connection);
command.Parameters.Add( "? UserName " , MySqlDbType.VarChar, 60 ).Value = userName;
command.Parameters.Add( "? Password " , MySqlDbType.VarChar, 60 ).Value = password;
command.Parameters.Add( "? Remark " , MySqlDbType.VarChar, 60 ).Value = remark;
command.Parameters.Add( "? Mail " , MySqlDbType.VarChar, 60 ).Value = mail;
command.Parameters.Add( "? DepartId " , MySqlDbType.Int32, 4 ).Value = departId;
command.Parameters.Add( "? Power " , MySqlDbType.Int32, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
在SQL Server中增加同样一条记录的代码如下：
using
System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// 在SQL Server中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址：http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class SqlUtil
{
public SqlUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(@UserName,@Password,@Remark,@Mail,@DepartId,@Power) " ;
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add( " @UserName " ,SqlDbType.NVarChar, 60 ).Value = userName;
command.Parameters.Add( " @Password " , SqlDbType.NVarChar, 60 ).Value = password;
command.Parameters.Add( " @Remark " , SqlDbType.NVarChar, 60 ).Value = remark;
command.Parameters.Add( " @Mail " , SqlDbType.NVarChar, 60 ).Value = mail;
command.Parameters.Add( " @DepartId " , SqlDbType.Int, 4 ).Value = departId;
command.Parameters.Add( " @Power " , SqlDbType.Int, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// 在SQL Server中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址：http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class SqlUtil
{
public SqlUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(@UserName,@Password,@Remark,@Mail,@DepartId,@Power) " ;
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add( " @UserName " ,SqlDbType.NVarChar, 60 ).Value = userName;
command.Parameters.Add( " @Password " , SqlDbType.NVarChar, 60 ).Value = password;
command.Parameters.Add( " @Remark " , SqlDbType.NVarChar, 60 ).Value = remark;
command.Parameters.Add( " @Mail " , SqlDbType.NVarChar, 60 ).Value = mail;
command.Parameters.Add( " @DepartId " , SqlDbType.Int, 4 ).Value = departId;
command.Parameters.Add( " @Power " , SqlDbType.Int, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
在Access中增加同样一条记录的代码如下：
using
System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
/// <summary>
/// 在Access中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址： http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class AccessUtil
{
public AccessUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?,?,?,?,?,?) " ;
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
// 注意下面参数的顺序一定要按照sql语句中的插入的列的顺序赋值，否则一定会报异常
OleDbCommand command = new OleDbCommand(sql, connection);
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = userName;
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = password;
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = remark;
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = mail;
command.Parameters.Add( " ? " , OleDbType.Integer, 4 ).Value = departId;
command.Parameters.Add( " ? " , OleDbType.Integer, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
/// <summary>
/// 在Access中使用参数化SQL的例子
/// 代码编写：周公
/// 日期：2008-3-19
/// 发表网址： http://blog.csdn.net/zhoufoxcn/archive/2008/03/19/2195618.aspx
/// </summary>
public class AccessUtil
{
public AccessUtil()
{
}
public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power)
{
string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?,?,?,?,?,?) " ;
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = "" ; // 此处设置链接字符串
// 注意下面参数的顺序一定要按照sql语句中的插入的列的顺序赋值，否则一定会报异常
OleDbCommand command = new OleDbCommand(sql, connection);
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = userName;
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = password;
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = remark;
command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = mail;
command.Parameters.Add( " ? " , OleDbType.Integer, 4 ).Value = departId;
command.Parameters.Add( " ? " , OleDbType.Integer, 4 ).Value = power;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0 ;
}
}
需要说明的是，除了Access之外，操作其它数据库可以不必要按照参数在SQL语句中出现的顺序添加进去一样可以正确执行，但是在Access中一定按照插入的列的顺序添加参数，因为“OLE DB.NET Framework 数据提供程序使用标有问号 (?) 的定位参数，而不使用命名参数（MSDN）”,所以给添加参数和赋值一定要按照列的顺序。
通过上面的例子，基本上可以总结出一个规律：在参数化
SQL
中参数名的格式跟其在存储过程中生命存储过程参数一致，例如在
Oracle
中存储过程参数一律以
”:”
开头，在
MS SQL Server
中存储过程参数一律以
”@”
开头，而在
MySQL
中存储过程（
MySQL
从
5.0
以后版本支持存储过程）参数一律以“？”开头，所以在参数化
SQL
语句中参数名有些不一样（记得在
csdn
上有朋友提到过不知道为什么
MySQL
中参数化
SQL
语句中要用“？
”
而不是和
SQL Server
一样使用
”@”
），如果那位朋友看过本文，我想他就会解开这个疑虑了。
本文转自周金桥51CTO博客，原文链接： http://blog.51cto.com/zhoufoxcn/167031，如需转载请自行联系原作者
