防止SQL注入攻击,数据库操作类

如果不规避,在黑窗口里面输入内容时利用拼接语句可以对数据进行攻击

如:输入Code值

p001' union select * from Info where '1'='1 //这样可以查询到所有数据

SQL数据库字符串注入攻击:
需要使用cmd.Parameters这个集合
占位符: @key 代表这个位置用这个占位符占住了

Parameters这个集合中将此占位符所代表的数据补全

cmd.Parameters.Clear(); --添加占位符数据之前,要清空此集合
cmd.Parameters.Add("@pwd", Pwd); --占位符内容填充
cmd.Parameters.Add("@nname",Nname);
cmd.Parameters.Add("@sex", (Sex=="男")?"1":"0");
cmd.Parameters.Add("@bir", Birthday);
cmd.Parameters.Add("@nat", Nation);
cmd.Parameters.Add("@uname", Uname);

1.实体类:封装
封装一个类,类名与数据库表名一致
成员变量名与列名一致,多一个下划线
成员变量封装完的属性,就会与数据表中的列名一致

每一行数据都可以存成一个对象,操作这个对象,就相当于对某一行数据进行整体操作

2.数据访问类:

就是将对数据库的一些操作,单独写到一个类中,封成一些方法,等待调用

结构看起来会非常清晰。

实体类:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; namespace 实体类_和数据访问类.App_Code
{
public class Users
{
private int _ids;
/// <summary>
/// ids
/// </summary>
public int Ids
{
get { return _ids; }
set { _ids = value; }
}
private string _username;
/// <summary>
/// 用户名
/// </summary>
public string Username
{
get { return _username; }
set { _username = value; }
}
private int _password;
/// <summary>
/// 密码
/// </summary>
public int Password
{
get { return _password; }
set { _password = value; }
}
private string _nikename;
/// <summary>
/// 昵称
/// </summary>
public string Nikename
{
get { return _nikename; }
set { _nikename = value; }
}
/// <summary>
/// 性别
/// </summary>
private bool _sex; public bool Sex
{
get { return _sex; }
set { _sex = value; }
}
private DateTime _birthday;
/// <summary>
/// 生日
/// </summary>
public DateTime Birthday
{
get { return _birthday; }
set { _birthday = value; }
}
private string nation;
/// <summary>
/// 民族
/// </summary>
public string Nation
{
get { return nation; }
set { nation = value; }
}
private string _class;
/// <summary>
/// 班级
/// </summary>
public string _class1
{
get { return _class; }
set { _class = value; }
} private string _NationName;
/// <summary>
/// 民族1
/// </summary>
public string NationName
{
get { return _NationName; }
set { _NationName = value; }
} private string _ClassName;
/// <summary>
/// 班级1
/// </summary>
public string ClassName
{
get { return _ClassName; }
set { _ClassName = value; }
} }
}

创建一个类,把整个数据库表单的数据都封装一下

数据访问类:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient; namespace 实体类_和数据访问类.App_Code
{
public class UsersData
{
SqlConnection conn = null;
SqlCommand cmd = null; public UsersData()
{
conn = new SqlConnection("server=.;database=data0928;user=sa;pwd=123");
cmd = conn.CreateCommand();
}
/// <summary>
/// 将数据添加到Users表中,返回true说明添加成功
/// </summary>
/// <param name="u">要添加到数据表中的Users对象</param>
/// <returns></returns>
public bool Insert(Users u)
{
bool ok = false;
int count = ;
cmd.CommandText = "INSERT INTO Users VALUES(@a,@b,@c,@d,@e,@f,@g)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a", u.Username);
cmd.Parameters.AddWithValue("@b", u.Password);
cmd.Parameters.AddWithValue("@c", u.Nikename);
cmd.Parameters.AddWithValue("@d", u.Sex);
cmd.Parameters.AddWithValue("@e", u.Birthday);
cmd.Parameters.AddWithValue("@f", u.Nation);
cmd.Parameters.AddWithValue("@g", u._class1);
try
{
conn.Open();
count = cmd.ExecuteNonQuery();
}
catch
{
ok = false;
}
finally
{
conn.Close();
}
if (count > )
ok = true;
return ok;
}
/// <summary>
/// 通过泛型集合对数据进行查询
/// </summary>
/// <returns>返回一个集合</returns>
public List<Users> select()
{
List<Users> list = new List<Users>();
cmd.CommandText = "select ids,UserName,[PassWord],NickName,Sex,Birthday,NationName,ClassName from users join Nation on nation.NationCode=Users.Nation JOIN dbo on Users.Class=dbo.ClassCode ";
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
int p = ;
while(p<dr.FieldCount)
{
if(dr[p] is Boolean)
{
Console.Write((Boolean)dr[p]?"男":"女"+"\t");
}
else if(dr[p] is DateTime)
{
Console.Write(" "+(((DateTime)dr[p]).ToShortDateString()) + "\t" + " ");
}
else
Console.Write(dr[p]+"\t");
p++;
}
Console.WriteLine();
}
}
conn.Close();
return list;
}
/// <summary>
/// 查询是否有此用户
/// </summary>
/// <param name="usname"></param>
/// <returns>有返回true</returns>
public bool select(string usname)
{
bool has=false;
cmd.CommandText = "SELECT *FROM Users where username=@a";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a",usname);
conn.Open();
SqlDataReader dr= cmd.ExecuteReader();
if (dr.HasRows)
has = true;
conn.Close();
return has;
}
/// <summary>
/// 删除方法
/// </summary>
/// <param name="usname"></param>
public void delect(string usname)
{
cmd.CommandText = "DELETE FROM Users WHERE UserName=@a";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a", usname);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
} public bool Update(string uname,string uname1,string uname2)
{
bool ok = false;
int count = ;
cmd.CommandText = "update Users set "+uname1+"=@b where UserName = @a";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a", uname);
cmd.Parameters.AddWithValue("@b", uname2);
try
{
conn.Open();
count = cmd.ExecuteNonQuery();
}
catch { ok = false; }
finally
{
conn.Close();
} if (count > )
ok = true; return ok;
} }
}

注:

1、一般约定,在项目里新建一个叫做App_xx的文件夹,将实体类和数据访问类统一放在其中

2、一般实体类使用想要进行操作的数据库中的表名来命名,数据库访问类用此表的表名后面加上Data来命名

3、数据访问类开头格式:

class userdata
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;         public userdata()
        {
            conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123");
            cmd = conn.CreateCommand();
        }

4、匿名方法

List<Users> ulist = new UsersData().Select();

直接使用UserData类中的Select方法(查询表中所有数据),并赋值给叫做ulist的泛型集合,从而比较简便的获取到所有数据,节省代码

有些方法也可以不用赋值直接进行调用

 if (new userdata().select(uname))
namespace 泛型集合
{
class Program
{
static void Main(string[] args)
{
//创建泛型集合对象
List<int> list=new List<int>(); //添加原素1,2,3
list.Add(1);
list.Add(2);
list.Add(3); //添加数组{1,2,3,4,5,6}
list.AddRange(new int[] {1,2,3,4,5,6}); //添加本身(泛型集合)
list.AddRange(list); //清空 list.Clear();
//移除某个原素 list.Remove(1);
//移除一定范围的元素 list.RemoveRange(0,2);
//移除指定索引位置的原素 list.RemoveAt(3);
//原素反转 list.Reverse();
//原素升序排序 list.Sort(); //list泛型集合可以和数组互相转换
int[] nums = list.ToArray();
List<int> listint = nums.ToList(); //遍历集合
for (int i = 0; i < list.Count;i++ )
{
Console.WriteLine(list[i]);
}
Console.ReadKey();
}
}泛型集合
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using 实体类_和数据访问类.App_Code; namespace 实体类_和数据访问类
{
class Program
{
static void Main(string[] args)
{
Console.Write("编号" + "\t" + "用户名" + "\t" + "密码" + "\t" + "昵称" + "\t" + "性别" + "\t" + "出生日期" + "\t" + "民族" + "\t" + "班级" + "\n");
//List<Users> ulist = new UsersData().select();
List<Users> wewe = new List<Users>();
UsersData udd=new UsersData();
wewe=udd.select(); foreach (Users uu in wewe)
{
Console.WriteLine(uu.Ids + "" + uu.Username + "" + uu.Password + "" + uu.Nikename + "" + uu.Sex + "" + uu.Birthday + "" + uu.NationName + "" + uu.ClassName);
}
for (; ; )
{
Console.WriteLine("1.添加,2.删除,3.修改,4,查看");
Console.Write("请输入你要操作的序号:");
string aa = Console.ReadLine();
if (aa == "")
{
Users user = new Users();
Console.Write("请输入要添加的用户名");
user.Username = Console.ReadLine();
Console.Write("请输入要添加的密码");
user.Password = int.Parse(Console.ReadLine());
Console.Write("请输入要添加的昵称");
user.Nikename = Console.ReadLine();
Console.Write("请输入要添加的性别");
user.Sex = bool.Parse(Console.ReadLine());
Console.Write("请输入要添加的生日");
user.Birthday = DateTime.Parse(Console.ReadLine());
Console.Write("请输入要添加的民族");
user.Nation = Console.ReadLine();
Console.Write("请输入要添加的班级");
user._class1 = Console.ReadLine(); UsersData ud = new UsersData();
bool isok = ud.Insert(user);
if (isok)
{
Console.WriteLine("添加成功");
}
else
Console.WriteLine("添加失败");
}
else if(aa=="")
{
Console.Write("请输入要删除的用户名:");
string usname = Console.ReadLine();
if(new UsersData().select(usname))
{
Console.WriteLine("已查到此用户,是否删除(Y,N)");
string cc = Console.ReadLine();
if(cc.ToUpper()=="Y")
{
new UsersData().delect(usname);
Console.WriteLine("删除成功");
}
else if(cc.ToUpper()=="N")
{
Console.WriteLine("取消了删除操作");
}
else if(cc=="")
{
Console.WriteLine("请按提示操作");
}
else
Console.WriteLine("请按提示操作");
}
else
{
Console.WriteLine("没有查到要删除的数据");
} }
else if(aa=="")
{
SqlConnection conn = new SqlConnection("server=;database=data0928;user=sa;pwd=123");
SqlCommand cmd = conn.CreateCommand();
Console.Write("请输入想要修改的用户名:");
string z = Console.ReadLine();
cmd.CommandText = "select ids,UserName,[PassWord],NickName,Sex,Birthday,NationName,ClassName from users join Nation on nation.NationCode=Users.Nation JOIN dbo on Users.Class=dbo.ClassCode where username='" + z + "'";
conn.Open();
SqlDataReader dw = cmd.ExecuteReader();
if (dw.HasRows)
{
while (dw.Read())//循环每一行 当超出时返回false
{
Console.WriteLine(dw["ids"] + "\t" + dw["UserName"] + "\t" + dw["PassWord"] + "\t" + dw["NickName"] + "\t" + (Convert.ToBoolean(dw["Sex"]) ? "男" : "女") + "\t" + Convert.ToDateTime(dw["Birthday"]).ToString("yyyy年MM月dd日") + "\t" + dw["NationName"] + "\t" + dw["ClassName"]);
}
conn.Close();
Console.Write("是否确定修改此条数据?(Y/N)");
string yn = Console.ReadLine();
if (yn.ToUpper() == "Y")
{
string uname = Console.ReadLine();
Console.Write("请输入密码:");
string pwd = Console.ReadLine();
Console.Write("请输入昵称:");
string nick = Console.ReadLine();
Console.Write("请输入性别:");
string sex = Console.ReadLine();
Console.Write("请输入生日:");
string bir = Console.ReadLine();
Console.Write("请输入民族:");
string nation = Console.ReadLine();
Console.Write("请输入班级:");
string cla = Console.ReadLine();
cmd.CommandText = "UPDATE Users set PassWord = '" + pwd + "',NickName = '" + nick +
"',Sex='" + sex + "',Birthday='" + bir + "',Nation='" + nation + "',Class='" + cla +
"' where UserName = '" + z + "'";
conn.Open();
int ui = cmd.ExecuteNonQuery();
conn.Close();
if (ui > )
{
Console.WriteLine("修改成功");
}
else
{
Console.WriteLine("修改失败");
}
}
else if (yn.ToUpper() == "N")
{
Console.WriteLine("取消了修改操作");
}
else
{
Console.WriteLine("请按提示操作");
}
}
else
{
Console.WriteLine("没有此条数据");
}
}
else if(aa=="")
{
Console.Write("编号" + "\t" + "用户名" + "\t" + "密码" + "\t" + "昵称" + "\t" + "性别" + "\t" + "出生日期" + "\t" + "民族" + "\t" + "班级" + "\n");
List<Users> asas = new List<Users>();
UsersData wdd = new UsersData();
asas = wdd.select(); foreach (Users uu in asas)
{
Console.WriteLine(uu.Ids + "\t" + uu.Username + "\t " + uu.Password + "\t" + uu.Nikename + "\t" + uu.Sex + "\t" + uu.Birthday + "\t" + uu.NationName + "\t" + uu.ClassName);
}
}
else if (aa == "")
{
Console.WriteLine("请按提示操作");
}
else
{
Console.WriteLine("请按提示操作");
} } }
}
}
上一篇:mysql in查询 结果乱序 引发的思考


下一篇:空类的默认函数—— SAP电面(2)/FEI