How to display specific database entries into a te

问题:

UPDATE: Thanks everyone, the code was not the issue, although the information regarding SQL injection was useful, my issue was that I was using an older version of my database which did not have the corresponding product ID so it was instead using the first product that it could find. Feel very stupid now but thanks for the suggestions.

I currently have the following code :

SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0 AttachDbFilename=C:\Users\h8005267\Desktop\Practical Project\Build\System4\System\StockControl.mdf;Integrated Security=True;Connect Timeout=30");
connection.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Product WHERE ProductID='" + textBox3.Text + "'", connection); 
SqlDataReader re = cmd.ExecuteReader();

if (re.Read())
{
  textBox4.Text = re["ProductTitle"].ToString(); // only fills using first product in table
  textBox5.Text = re["ProductPublisherArtist"].ToString();
  comboBox1.Text = re["ProductType"].ToString();
  textBox6.Text = re["Price"].ToString();
}
else
{
  MessageBox.Show("Please enter a valid item barcode");
}
re.Close();
connection.Close();

The issue I am currently having is although the text boxes display the information on button click, the information displayed is only the first row of data in the database, NOT the row corresponding to the textbox3 in the sql statement

回答1:

Try this instead. Avoid building SQL statement dynamically the way you are doing it. You are opening your database to risks of SQL Injection. Used parameters insead.

using (var connection = new SqlConnection("connection string"))
{
    connection.Open();
    using (var cmd = new SqlCommand("SELECT * FROM Product WHERE ProductID=@MYVALUE", connection))
    {
        cmd.Parameters.Add("@MYVALUE", SqlDbType.VarChar).Value = textBox3.Text;
        SqlDataReader re = cmd.ExecuteReader();

        if (re.Read())
        {
            textBox4.Text = re["ProductTitle"].ToString(); // only fills using first product in table
            textBox5.Text = re["ProductPublisherArtist"].ToString();
            comboBox1.Text = re["ProductType"].ToString();
            textBox6.Text = re["Price"].ToString();
        }
        else
        {
            MessageBox.Show("Please enter a valid item barcode");
        }
    }
}

回答2:

put a breakpoint on that line

SqlDataReader re = cmd.ExecuteReader();

and enter the following into textBox3

'; DROP TABLE Product; SELECT '

the ' are to be entered in your textbox. now execute your method and carefully read the resulting sql command... welcome to sql injection ;)

@M Patel: thx for your comment and you are perfectly right

The result would be the following SQL

SELECT * FROM Product WHERE ProductID=''; DROP TABLE Product; SELECT ''

And this would allow a malicious user to destroy your database.

To prevent that you should work with prepared Statements like M Patel suggested in his answer

回答3:

you have SQL Injection problem with '" + textBox3.Text + "'"

and you don't have to name your controls like that, you have to use a meaningful names

you can use this code

using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0 AttachDbFilename=C:\Users\h8005267\Desktop\Practical Project\Build\System4\System\StockControl.mdf;Integrated Security=True;Connect Timeout=30"))
{
    connection.Open();
    SqlCommand cmd = new SqlCommand("SELECT * FROM Product WHERE ProductID=@ProductID", connection);
    cmd.Parameters.AddWithValue("@ProductID", textBox3.Text);
    SqlDataReader re = cmd.ExecuteReader();
    if (re.Read())
    {
        textBox4.Text = re.GetString(re.GetOrdinal("ProductTitle")); // only fills using first product in table
        textBox5.Text = re.GetString(re.GetOrdinal("ProductPublisherArtist"));
        comboBox1.Text = re.GetString(re.GetOrdinal("ProductType"));
        textBox6.Text = re.GetString(re.GetOrdinal("Price"));
    }
    else
    {
        MessageBox.Show("Please enter a valid item barcode");
    }
    re.Close();
}