面对Sql interjection错误 [英] Sql interjection error faced
问题描述
private void button1_Click(object sender, EventArgs e)
{
if (comboBoxstaff.Text == string.Empty)
{
MessageBox.Show("Please Select gaugeman"); // not to let thecombobox empty
return;
}
else if (comboBoxcompondrubber.Text == string.Empty)
{
MessageBox.Show("Please Select Compond Rubber");// not to let thecombobox empty
return;
}
else if (textBox1.Text == string.Empty)
{
MessageBox.Show("Please Key in W.S thickness");// not to let thetextbox empty
return;
}
else if (textBox2.Text == string.Empty)
{
MessageBox.Show("Please Key in G.S thickness");// not to let thecombobox empty
}
SQLiteConnection insertsess = new SQLiteConnection("Data Source=|DataDirectory|\\test1db");
string insert12 = "INSERT INTO thickness (GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes) VALUES ('" + comboBoxstaff.Text + "','" + label2.Text + "', '" + comboBoxcompondrubber.Text + "', '" + textBox2.Text + "', '" + textBox1.Text + "')"; //insert statment
SQLiteCommand ins1 = new SQLiteCommand(insert12, insertsess);
insertsess.Open();
ins1.ExecuteNonQuery();
MessageBox.Show("Data had been saved");// showed when the message is being saved
SQLiteConnection sesscheck = new SQLiteConnection("Data Source=|DataDirectory|\\test1db");
SQLiteCommand chk1;
chk1 = sesscheck.CreateCommand();
chk1.CommandText = "SELECT GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes FROM thickness WHERE CompondRubber = '" + comboBoxcompondrubber.Text.Trim() + "'";
sesscheck.Open();
DataTable thicknessTable = new DataTable();
//DataTable thicknessTable = new DataTable();
SQLiteDataReader reader = chk1.ExecuteReader();
thicknessTable.Load(reader);
//SQLiteDataReader reader1 = chk2.ExecuteReader();
//thicknessTable.Load(reader1);
sesscheck.Close();
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
我的尝试：
What I have tried:
I had tried using data adapter but it didn't work I tried using data reader but I am not sure on how to go about using it properly.
I want to get the value 12.0 to be saved and displayed into data grid view at the moment if I key 12.1 it also works only like 12.0 it will be entered and displayed as 12 at the data grid view
推荐答案
永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。
连接字符串时会导致问题，因为SQL会收到如下命令：
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'哪个SQL看作三个单独的命令：
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';完全有效的SELECT
A perfectly valid SELECT
DROP TABLE MyTable;完全有效的删除表格通讯和
A perfectly valid "delete the table" command
--'其他一切都是评论。
所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。
所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期进行备份，不是吗？
如果使用DataReader或DataAdapter并不重要 - 连接字符串时会开始SQL注入。所以这段代码是个问题：
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
It doesn't matter if you use a DataReader or a DataAdapter - SQL injection starts when you concatenate strings. So this code is a problem:
string insert12 = "INSERT INTO thickness (GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes) VALUES ('" + comboBoxstaff.Text + "','" + label2.Text + "', '" + comboBoxcompondrubber.Text + "', '" + textBox2.Text + "', '" + textBox1.Text + "')"; //insert statment
SQLiteCommand ins1 = new SQLiteCommand(insert12, insertsess);
insertsess.Open();
ins1.ExecuteNonQuery();将它（你应用程序的所有其余部分）转换为参数化查询：
Convert it (all the rest of your app) to a parameterised query:
string insert12 = "INSERT INTO thickness (GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes) VALUES (@GM, @DT, @CR, @GST, @WST)"
SQLiteCommand ins1 = new SQLiteCommand(insert12, insertsess);
ins1.Parameters.AddWithValue("@GM", comboBoxstaff.Text);
ins1.Parameters.AddWithValue("@DT", label2.Text);
ins1.Parameters.AddWithValue("@CR", comboBoxcompondrubber.Text);
ins1.Parameters.AddWithValue("@GST", textBox2.Text);
ins1.Parameters.AddWithValue("@WST", textBox1.Text);
insertsess.Open();
ins1.ExecuteNonQuery();
请注意，您负责关闭和处理所有数据库访问对象，我们使用它也是一个非常好的主意。 使用阻止连接，命令等等。
我也会把Connection.Open调用放在Connection结构附近，而不是混合深入到代码中。这样，当你修改或复制代码时，它不太可能引起问题。
Note that you are responsible for closing and disposing of all database access objects, us it's also a very good idea to use a using block for teh Connection, Command, and suchlike.
I would also put the Connection.Open call close to the Connection construction, rather than mixed into the code further down. That way it's less likely to cause problems when you modify or copy the code.
提到你提到的另一个问题
Picking up on the other problem you mention
我想要将值12.0保存并显示到数据网格视图中此时如果我键12.1它也只能像12.0那样输入并在数据网格视图中显示为12
I want to get the value 12.0 to be saved and displayed into data grid view at the moment if I key 12.1 it also works only like 12.0 it will be entered and displayed as 12 at the data grid view
显示与数字的存储方式或从数据库中获取数据的方式无关。你必须告诉dataGridView 如何来显示它。
例如，右键单击dataGridView1并设置相应列的defaultCellStyle（ s）到N2（数字）。
这假设您以数字非整数列类型存储数据库中的信息。如果没有，则需要更改表模式，以使列类型与要存储在其中的数据类型相匹配。
The display has nothing to do with the way the number is stored or how you are getting it back from the database. You have to tell the dataGridView how to display it.
For example, right-click dataGridView1 and set the defaultCellStyle of the appropriate column(s) to "N2" (Numeric).
This assumes that you are storing the information on the database in a numeric, non-integer column type. If not then you need to change your table schema so that the column types match the type of data you want to store in them.
这篇关于面对Sql interjection错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
