当前位置:Gxlcms > 数据库问题 > 防止SQL注入攻击,数据库操作类

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

时间:2021-07-01 10:21:17 帮助过:15人阅读

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 = 0;
            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 > 0)
                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 = 0;
                    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 = 0;
            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 > 0)
                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 == "1")
                {
                    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=="2")
                {
                    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=="3")
                {
                    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 > 0)
                            {
                                Console.WriteLine("修改成功");
                            }
                            else
                            {
                                Console.WriteLine("修改失败");
                            }
                        }
                        else if (yn.ToUpper() == "N")
                        {
                            Console.WriteLine("取消了修改操作");
                        }
                        else
                        {
                            Console.WriteLine("请按提示操作");
                        }
                    }
                    else
                    {
                        Console.WriteLine("没有此条数据");
                    }
                }
                else if(aa=="4")
                {
                    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("请按提示操作");
                }
                
            }




        }
    }
}

 

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

标签:pre   调用   字符   over   数据库访问   bsp   操作   ram   als   

人气教程排行