解决sql注入问题（丛林战争项目）

一、正常的代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MySqlOperation
{
    class Program
    {
        static void Main(string[] args)
        {
            //Database指定数据库，Data Source指定本机ip，port指定端口号，user id指定用户名，password 指定密码，各属性之间用“；”进行分隔
            String connStr = "Database=test_connection;Data Source=127.0.0.1;port=3306;User Id=root;Password=123456";
            //建立连接，参数为连接的字符串，即ip地址、端口号、账号、密码
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();
            String userName = "test";
            String passWord = "test";

            MySqlCommand cmd = new MySqlCommand("insert into user set userName='"+userName+"',passWord='"+passWord+"'",conn);
            cmd.ExecuteNonQuery();
            //关闭流
            conn.Close();                                                                   //关闭连接
            Console.ReadKey();                                                              //让程序暂停

        }
    }
}

正常的运行结果：

在表的最后增加了一个userName和passWord都为test的用户

二、异常代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MySqlOperation
{
    class Program
    {
        static void Main(string[] args)
        {
            //Database指定数据库，Data Source指定本机ip，port指定端口号，user id指定用户名，password 指定密码，各属性之间用“；”进行分隔
            String connStr = "Database=test_connection;Data Source=127.0.0.1;port=3306;User Id=root;Password=123456";
            //建立连接，参数为连接的字符串，即ip地址、端口号、账号、密码
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();


            String userName = "test";
            String passWord = "test'delete from user";

            MySqlCommand cmd = new MySqlCommand("insert into user set userName='"+userName+"',passWord='"+passWord+"'",conn);
            cmd.ExecuteNonQuery();
            //关闭流
            conn.Close();                                                                   //关闭连接
            Console.ReadKey();                                                              //让程序暂停

        }
    }
}

运行结果：

passWord中增加了sql命令，user表中的内容全部被删除掉了

三、解决办法：

将代码由

  String userName = "test";
  String passWord = "test';delete from user;";

  MySqlCommand cmd = new MySqlCommand("insert into user set userName='"+userName+"',passWord='"+passWord+"'",conn);

改为：

 String userName = "test";
 String passWord = "test';delete from user;";


 MySqlCommand cmd = new MySqlCommand("insert into user set userName=@un,passWord=@pwd", conn);
 cmd.Parameters.AddWithValue("un",userName);
 cmd.Parameters.AddWithValue("pwd", passWord);

其中un和pwd都是自己定义的，AddWithValue会将这两个参数仅仅作为参数传递

完整的代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MySqlOperation
{
    class Program
    {
        static void Main(string[] args)
        {
            //Database指定数据库，Data Source指定本机ip，port指定端口号，user id指定用户名，password 指定密码，各属性之间用“；”进行分隔
            String connStr = "Database=test_connection;Data Source=127.0.0.1;port=3306;User Id=root;Password=123456";
            //建立连接，参数为连接的字符串，即ip地址、端口号、账号、密码
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();

            String userName = "test";
            String passWord = "test';delete from user;";

            //MySqlCommand cmd = new MySqlCommand("insert into user set userName='"+userName+"',passWord='"+passWord+"'",conn);

            MySqlCommand cmd = new MySqlCommand("insert into user set userName=@un,passWord=@pwd", conn);
            cmd.Parameters.AddWithValue("un",userName);
            cmd.Parameters.AddWithValue("pwd", passWord);

            cmd.ExecuteNonQuery();
            //关闭流
            conn.Close();                                                                   //关闭连接
            Console.ReadKey();                                                              //让程序暂停

        }
    }
}

运行后的结果：