在ADO.NET中使用参数化SQL语句的大同小异

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:


在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
 
下面假设数据库的主键都采用了数据库的本地化技术解决了(例如在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 ;
    }
}
在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 ;
    }
}
在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 ;
    }
}
在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 ;
    }
}
需要说明的是,除了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,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
SQL 数据库 C#
C# .NET面试系列十一:数据库SQL查询(附建表语句)
#### 第1题 用一条 SQL 语句 查询出每门课都大于80 分的学生姓名 建表语句: ```sql create table tableA ( name varchar(10), kecheng varchar(10), fenshu int(11) ) DEFAULT CHARSET = 'utf8'; ``` 插入数据 ```sql insert into tableA values ('张三', '语文', 81); insert into tableA values ('张三', '数学', 75); insert into tableA values ('李四',
61 2
C# .NET面试系列十一:数据库SQL查询(附建表语句)
|
SQL XML Oracle
Mybatis动态SQL语句查询,实现一个参数 可查询多个字段。
Mybatis动态SQL语句查询,实现一个参数 可查询多个字段。
369 0
Mybatis动态SQL语句查询,实现一个参数 可查询多个字段。
|
2月前
|
SQL 开发框架 .NET
ASP.NET Web——GridView完整增删改查示例(全篇幅包含sql脚本)大二结业考试必备技能
ASP.NET Web——GridView完整增删改查示例(全篇幅包含sql脚本)大二结业考试必备技能
32 0
|
8月前
|
SQL 缓存 自然语言处理
SQL参数化查询为什么能够防止SQL注入
SQL参数化查询为什么能够防止SQL注入
100 0
|
4月前
|
SQL 数据库连接 数据库
VB.NET 中使用SqlConnection类连接到Microsoft SQL Server数据库的详细步骤
VB.NET 中使用SqlConnection类连接到Microsoft SQL Server数据库的详细步骤
87 0
|
9月前
|
SQL 开发框架 前端开发
ASP.NET Core+Element+SQL Server开发校园图书管理系统(完)
ASP.NET Core+Element+SQL Server开发校园图书管理系统(完)
92 0
|
9月前
|
开发框架 JavaScript .NET
ASP.NET Core+Element+SQL Server开发校园图书管理系统(四)(下)
ASP.NET Core+Element+SQL Server开发校园图书管理系统(四)(下)
68 0
|
9月前
|
开发框架 前端开发 JavaScript
ASP.NET Core+Element+SQL Server开发校园图书管理系统(四)(上)
ASP.NET Core+Element+SQL Server开发校园图书管理系统(四)(上)
73 0
|
9月前
|
开发框架 JavaScript .NET
ASP.NET Core+Element+SQL Server开发校园图书管理系统(三)(下)
ASP.NET Core+Element+SQL Server开发校园图书管理系统(三)(下)
52 0
ASP.NET Core+Element+SQL Server开发校园图书管理系统(三)(下)
|
9月前
|
SQL 存储 缓存
SQl Server——ADO.NET五大对象
在某些编程中总是会需要用到数据库,那如何让应用程序和数据库之间建立联系呢?ADO.NET就提供了应用程序连接数据库的桥梁