正文
参数化查询防止Sql注入
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
拼接sql语句会造成sql注入,注入演示
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
FillData(dataGridView1);
} private void FillData(DataGridView dataGrid)
{
string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Employees where EmployeeID=\'" + textBox1.Text + "\'";
using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
{
using (SqlDataAdapter sqlData = new SqlDataAdapter(sqlCommand))
{
DataTable dataTable = new DataTable();
sqlData.Fill(dataTable);
dataGrid.DataSource = dataTable;
}
}
}
}
}
}
正常生成的Sql语句应该为
select * from Employees where EmployeeID=''
输入sql实际生成的Sql语句为
select * from Employees where EmployeeID='' or 1=1 --'
所有的数据都查询出来了
防止注入漏洞应该用SqlParameter做参数化查询
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
FillData(dataGridView1);
} private void FillData(DataGridView dataGrid)
{
string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Employees where EmployeeID=@EmployeeID";
using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
{
SqlParameter[] sqlParameter = { new SqlParameter("@EmployeeID", textBox1.Text) };
sqlCommand.Parameters.AddRange(sqlParameter);
using (SqlDataAdapter sqlData = new SqlDataAdapter(sqlCommand))
{
DataTable dataTable = new DataTable();
sqlData.Fill(dataTable);
dataGrid.DataSource = dataTable;
}
}
}
}
}
}
再输入sql注入会报错
如果用在登录或者未经授权的查询时很有用
重新整理代码
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration; namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
string sql = "select * from Employees where EmployeeID=@EmployeeID";
SqlParameter[] sqlParameter = { new SqlParameter("@EmployeeID", textBox1.Text) };
FillGridView(sql, dataGridView1, sqlParameter);
} private void FillGridView(string sql, DataGridView dataGrid, SqlParameter[] sqlParameter = null)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
{
if (sqlParameter != null)
{
sqlCommand.Parameters.AddRange(sqlParameter);
}
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
dataGrid.DataSource = dataTable;
}
}
}
}
}
}
