C#/SQL Question
-
I have a C#/ASP.NET application that goes and creates a connection to a SQL Server 2000 database. Within the application itself, I have a textbox in which the user enters a part number. After entering the part number, they click a command button that returns data in a datagrid depending on the part number. //Declaration of PartNumber string protected string strPartNumberInput; //more code //Set PartNumber variable to what user entered strPartNumberInput += txtPartNumber.Text; //Now I want to run SQL to get cost data. Basically want 'Select * from Costs where costs.PartID = Parts.ID' string SQLString = "Select * FROM Costs"; //What else to put here?? How do I programatically say, 'strPartNumberInput is Parts.ID' Thanks! Let me know if this doesn't make sense.
-
I have a C#/ASP.NET application that goes and creates a connection to a SQL Server 2000 database. Within the application itself, I have a textbox in which the user enters a part number. After entering the part number, they click a command button that returns data in a datagrid depending on the part number. //Declaration of PartNumber string protected string strPartNumberInput; //more code //Set PartNumber variable to what user entered strPartNumberInput += txtPartNumber.Text; //Now I want to run SQL to get cost data. Basically want 'Select * from Costs where costs.PartID = Parts.ID' string SQLString = "Select * FROM Costs"; //What else to put here?? How do I programatically say, 'strPartNumberInput is Parts.ID' Thanks! Let me know if this doesn't make sense.
Well, looking at your code, the cmd: strPartNumberInput += txtPartNumber.Text; does not set a part number, it adds just more numbers. it should be: strPartNumberInput = txtPartNumber.Text; And how that I see it... you just need this SQL: "Select * from Costs where Costs.PartID = Parts.ID And Costs.PartID = " + strPartNumberInput; should work... Good-Luck... NaNg.
-
I have a C#/ASP.NET application that goes and creates a connection to a SQL Server 2000 database. Within the application itself, I have a textbox in which the user enters a part number. After entering the part number, they click a command button that returns data in a datagrid depending on the part number. //Declaration of PartNumber string protected string strPartNumberInput; //more code //Set PartNumber variable to what user entered strPartNumberInput += txtPartNumber.Text; //Now I want to run SQL to get cost data. Basically want 'Select * from Costs where costs.PartID = Parts.ID' string SQLString = "Select * FROM Costs"; //What else to put here?? How do I programatically say, 'strPartNumberInput is Parts.ID' Thanks! Let me know if this doesn't make sense.
Unless I'm missing something...
string SQLString = "Select * FROM Costs WHERE costs.PartID = " + strPartNumberInput;
-
I have a C#/ASP.NET application that goes and creates a connection to a SQL Server 2000 database. Within the application itself, I have a textbox in which the user enters a part number. After entering the part number, they click a command button that returns data in a datagrid depending on the part number. //Declaration of PartNumber string protected string strPartNumberInput; //more code //Set PartNumber variable to what user entered strPartNumberInput += txtPartNumber.Text; //Now I want to run SQL to get cost data. Basically want 'Select * from Costs where costs.PartID = Parts.ID' string SQLString = "Select * FROM Costs"; //What else to put here?? How do I programatically say, 'strPartNumberInput is Parts.ID' Thanks! Let me know if this doesn't make sense.
As i have understood from your description, you need this SQL query String SQLString="SELECT * FROM Costs WHERE PartID= "+ "(SELECT ID FROM Parts "+ "WHERE PartNumber="+txtPartNumber.Text+")"; This query is right if your part number is not the part id which stored seprately in parts table along with part id. But if Part no. and part id are the same then you could use following query: String SQLString="SELECT * FROM Costs WHERE PartID= "+txtPartNumber.Text; I think it should work.... Wasif Ehsan
-
I have a C#/ASP.NET application that goes and creates a connection to a SQL Server 2000 database. Within the application itself, I have a textbox in which the user enters a part number. After entering the part number, they click a command button that returns data in a datagrid depending on the part number. //Declaration of PartNumber string protected string strPartNumberInput; //more code //Set PartNumber variable to what user entered strPartNumberInput += txtPartNumber.Text; //Now I want to run SQL to get cost data. Basically want 'Select * from Costs where costs.PartID = Parts.ID' string SQLString = "Select * FROM Costs"; //What else to put here?? How do I programatically say, 'strPartNumberInput is Parts.ID' Thanks! Let me know if this doesn't make sense.
All of your replies, disappointingly, contain a major security flaw. You should never inject values into a SQL string when you can use a parameter instead. For more information see SQL Injection Attacks and Tips on How to Prevent Them[^] You may want to re-write your code to resemble this:
string SQLString = "SELECT * FROM Costs WHERE Costs.PartID = @PartID";
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = SQLString;
cmd.Parameters.Add("@PartID", strPartNumberInput);If Costs.PartID is an int column then you'll have to convert the strPartNumberInput into an integer first:
Convert.ToInt32(strPaetNumberInput)Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Well, looking at your code, the cmd: strPartNumberInput += txtPartNumber.Text; does not set a part number, it adds just more numbers. it should be: strPartNumberInput = txtPartNumber.Text; And how that I see it... you just need this SQL: "Select * from Costs where Costs.PartID = Parts.ID And Costs.PartID = " + strPartNumberInput; should work... Good-Luck... NaNg.
NaNg15241 wrote:
"Select * from Costs where Costs.PartID = Parts.ID And Costs.PartID = " + strPartNumberInput;
Your solution is susceptable to a SQL Injection Attack. Please learn how to defend against these attacks as they could compromise your systems. For more information see SQL Injection Attacks and Tips on How to Prevent Them[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Unless I'm missing something...
string SQLString = "Select * FROM Costs WHERE costs.PartID = " + strPartNumberInput;
Gerald Schwab wrote:
string SQLString = "Select * FROM Costs WHERE costs.PartID = " + strPartNumberInput;
Your solution is susceptable to a SQL Injection Attack. Please learn how to defend against these attacks as they could compromise your systems. For more information see SQL Injection Attacks and Tips on How to Prevent Them[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
As i have understood from your description, you need this SQL query String SQLString="SELECT * FROM Costs WHERE PartID= "+ "(SELECT ID FROM Parts "+ "WHERE PartNumber="+txtPartNumber.Text+")"; This query is right if your part number is not the part id which stored seprately in parts table along with part id. But if Part no. and part id are the same then you could use following query: String SQLString="SELECT * FROM Costs WHERE PartID= "+txtPartNumber.Text; I think it should work.... Wasif Ehsan
Wasif Ehsan wrote:
String SQLString="SELECT * FROM Costs WHERE PartID= "+txtPartNumber.Text;
Your solution is susceptable to a SQL Injection Attack. Please learn how to defend against these attacks as they could compromise your systems. For more information see SQL Injection Attacks and Tips on How to Prevent Them[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
All of your replies, disappointingly, contain a major security flaw. You should never inject values into a SQL string when you can use a parameter instead. For more information see SQL Injection Attacks and Tips on How to Prevent Them[^] You may want to re-write your code to resemble this:
string SQLString = "SELECT * FROM Costs WHERE Costs.PartID = @PartID";
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = SQLString;
cmd.Parameters.Add("@PartID", strPartNumberInput);If Costs.PartID is an int column then you'll have to convert the strPartNumberInput into an integer first:
Convert.ToInt32(strPaetNumberInput)Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
I have not had a chance to read the article you referenced, but I am wondering why injecting values into the string is considered a security risk? ..big thanks to all who have replied to my question!
Because if you inject strings into the SQL, especially ones that come straight from the user interface, then an attacker can produce malformed SQL and gain access to your system. (Where do you live? I can come and do one of my SQL Injection Attack presentations in your town if you want a real live demonstration where I compromise a SQL Server into divulging the inner most secrets of the server it is running on. And I mean the whole server, not just the SQL Server process.*) Lets say you have a simple bit of SQL like this:
cmd.CommandText = "SELECT * FROM Products where Name = '"+txtSearch.Text+"'";
What happens if the user types in the following?
'; DELETE FROM Products; --
The whole string becomes:
SELECT * FROM Products where Name = ''; DELETE FROM Products; --
That will return a dataset back to the application, which is what it expects, and then deletes all the products from the database. When the next customer comes to the website what is it going to show when there are no products in the database? Okay - there may be some constraints on the table (foreign key constraints) that don't permit the rows to be deleted. How about something equally damaging to the company. Let's set their entire inventory to a penny! The mallicious user then types:
'; UPDATE Products SET Price = 0.01; --
The word will quickly spread around the internet and the company will soon be out of business or have a huge number of very pissed off customers. If you don't secure your system the possibilities for attack are endless. * The demonstration is done on a server box that I own. Performing a SQL Injection Attack on a system without the permission of the system owner is a breach of the 1990 Misue of Computers Act and can carry a penalty of 5 years in jail.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Because if you inject strings into the SQL, especially ones that come straight from the user interface, then an attacker can produce malformed SQL and gain access to your system. (Where do you live? I can come and do one of my SQL Injection Attack presentations in your town if you want a real live demonstration where I compromise a SQL Server into divulging the inner most secrets of the server it is running on. And I mean the whole server, not just the SQL Server process.*) Lets say you have a simple bit of SQL like this:
cmd.CommandText = "SELECT * FROM Products where Name = '"+txtSearch.Text+"'";
What happens if the user types in the following?
'; DELETE FROM Products; --
The whole string becomes:
SELECT * FROM Products where Name = ''; DELETE FROM Products; --
That will return a dataset back to the application, which is what it expects, and then deletes all the products from the database. When the next customer comes to the website what is it going to show when there are no products in the database? Okay - there may be some constraints on the table (foreign key constraints) that don't permit the rows to be deleted. How about something equally damaging to the company. Let's set their entire inventory to a penny! The mallicious user then types:
'; UPDATE Products SET Price = 0.01; --
The word will quickly spread around the internet and the company will soon be out of business or have a huge number of very pissed off customers. If you don't secure your system the possibilities for attack are endless. * The demonstration is done on a server box that I own. Performing a SQL Injection Attack on a system without the permission of the system owner is a breach of the 1990 Misue of Computers Act and can carry a penalty of 5 years in jail.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Unfortunately I live in the boring state of South Dakota in the United States which would be a bit far for you to travel....:-) This is for an intranet site that only other programmers will be accessing. Apparently the company does not trust other users to update product information. Therefore security right now is not a concern to my boss but I'd like to get the security set in anticipation that non-programmers could use the application. I skimmed the article and read your post. Very interesting stuff. I hope everyone takes the time to read it. Thanks again!
-
All of your replies, disappointingly, contain a major security flaw. You should never inject values into a SQL string when you can use a parameter instead. For more information see SQL Injection Attacks and Tips on How to Prevent Them[^] You may want to re-write your code to resemble this:
string SQLString = "SELECT * FROM Costs WHERE Costs.PartID = @PartID";
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = SQLString;
cmd.Parameters.Add("@PartID", strPartNumberInput);If Costs.PartID is an int column then you'll have to convert the strPartNumberInput into an integer first:
Convert.ToInt32(strPaetNumberInput)Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Colin--I entered the code you provided... private void btnSearchPartNumber_Click(object sender, System.EventArgs e) { strPartNumberInput = txtPartNumber.Text; Convert.ToInt32(strPartNumberInput); string SQLString = "Select * FROM Costs where Costs.PartID = @PartID"; SqlCommand cmd = new SqlCommand(); cmd.Connection = myConnection; cmd.CommandText = SQLString; cmd.Parameters.Add = ("@PartID", strPartNumberInput); //Call and build grid BindGrid(strConnectSQL, SQLString, DataGrid1); } However, I am getting a compiling error where I bolded the strPartNumberInput. I'm getting 'expected ;' What am I missing here?
-
Colin--I entered the code you provided... private void btnSearchPartNumber_Click(object sender, System.EventArgs e) { strPartNumberInput = txtPartNumber.Text; Convert.ToInt32(strPartNumberInput); string SQLString = "Select * FROM Costs where Costs.PartID = @PartID"; SqlCommand cmd = new SqlCommand(); cmd.Connection = myConnection; cmd.CommandText = SQLString; cmd.Parameters.Add = ("@PartID", strPartNumberInput); //Call and build grid BindGrid(strConnectSQL, SQLString, DataGrid1); } However, I am getting a compiling error where I bolded the strPartNumberInput. I'm getting 'expected ;' What am I missing here?
Add is a method, not a property. Use this instead... cmd.Parameters.Add("@PartID", strPartNumberInput);
-
Colin--I entered the code you provided... private void btnSearchPartNumber_Click(object sender, System.EventArgs e) { strPartNumberInput = txtPartNumber.Text; Convert.ToInt32(strPartNumberInput); string SQLString = "Select * FROM Costs where Costs.PartID = @PartID"; SqlCommand cmd = new SqlCommand(); cmd.Connection = myConnection; cmd.CommandText = SQLString; cmd.Parameters.Add = ("@PartID", strPartNumberInput); //Call and build grid BindGrid(strConnectSQL, SQLString, DataGrid1); } However, I am getting a compiling error where I bolded the strPartNumberInput. I'm getting 'expected ;' What am I missing here?
Two changes: the line:
Convert.ToInt32(strPartNumberInput);becomesint partNumber = Convert.ToInt32(strPartNumberInput);And the linecmd.Parameters.Add = ("@PartID", strPartNumberInput);becomescmd.Parameters.Add("@PartID", partNumber);leckey wrote:
BindGrid(strConnectSQL, SQLString, DataGrid1);
I'm not sure what that does (I'm guessing it is a method you created), but you need to send the command, not the string (remember the string now contains a parameter and the SQL Server needs to know what that means, which is what the
cmd.Parameters.Add(...)does) And the method will have to be updated to use the command rather than the string. I'm also guessing thatstrConnectSQLis your connection string - If so then you'll have to replace my reference tomyConnectionwith your connection string.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Two changes: the line:
Convert.ToInt32(strPartNumberInput);becomesint partNumber = Convert.ToInt32(strPartNumberInput);And the linecmd.Parameters.Add = ("@PartID", strPartNumberInput);becomescmd.Parameters.Add("@PartID", partNumber);leckey wrote:
BindGrid(strConnectSQL, SQLString, DataGrid1);
I'm not sure what that does (I'm guessing it is a method you created), but you need to send the command, not the string (remember the string now contains a parameter and the SQL Server needs to know what that means, which is what the
cmd.Parameters.Add(...)does) And the method will have to be updated to use the command rather than the string. I'm also guessing thatstrConnectSQLis your connection string - If so then you'll have to replace my reference tomyConnectionwith your connection string.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Okay...here is all the code... using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Configuration; namespace WebBasedPartsDB { /// /// Summary description for WebForm1. /// public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.Label lblPartNumber; protected System.Web.UI.WebControls.Button btnSearchPartNumber; protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Web.UI.WebControls.TextBox txtPartNumber; protected string strPartNumberInput; //Get the SQL connection string from the web.config file public String strConnectSQL = (ConfigurationSettings.AppSettings["ConnectionString"]); private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.btnSearchPartNumber.Click += new System.EventHandler(this.btnSearchPartNumber_Click); this.Load += new System.EventHandler(this.Page_Load); } #endregion private void btnSearchPartNumber_Click(object sender, System.EventArgs e) { strPartNumberInput = txtPartNumber.Text; int partNumber = Convert.ToInt32(strPartNumberInput); string SQLString = "Select * FROM Costs where Costs.PartID = @PartID"; SqlCommand cmd = new SqlCommand(); cmd.Connection = strConnectSQL; cmd.CommandText = SQLString; cmd.Parameters.Add ("@PartID", partNumber); //Pass the command, not the string BindGrid (strConnectSQL, SQLString, DataGrid1 ); //BindGrid(strConnectSQL, cmd, DataGrid1); } //********************************************************** //BindData() //********************************************************** private void BindGrid (string DBconnectString, string sqlCommand, System.Web.UI.WebControls.DataGrid DGrid) { // create data connection SqlConnection conn
-
Okay...here is all the code... using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Configuration; namespace WebBasedPartsDB { /// /// Summary description for WebForm1. /// public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.Label lblPartNumber; protected System.Web.UI.WebControls.Button btnSearchPartNumber; protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Web.UI.WebControls.TextBox txtPartNumber; protected string strPartNumberInput; //Get the SQL connection string from the web.config file public String strConnectSQL = (ConfigurationSettings.AppSettings["ConnectionString"]); private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.btnSearchPartNumber.Click += new System.EventHandler(this.btnSearchPartNumber_Click); this.Load += new System.EventHandler(this.Page_Load); } #endregion private void btnSearchPartNumber_Click(object sender, System.EventArgs e) { strPartNumberInput = txtPartNumber.Text; int partNumber = Convert.ToInt32(strPartNumberInput); string SQLString = "Select * FROM Costs where Costs.PartID = @PartID"; SqlCommand cmd = new SqlCommand(); cmd.Connection = strConnectSQL; cmd.CommandText = SQLString; cmd.Parameters.Add ("@PartID", partNumber); //Pass the command, not the string BindGrid (strConnectSQL, SQLString, DataGrid1 ); //BindGrid(strConnectSQL, cmd, DataGrid1); } //********************************************************** //BindData() //********************************************************** private void BindGrid (string DBconnectString, string sqlCommand, System.Web.UI.WebControls.DataGrid DGrid) { // create data connection SqlConnection conn
Connection expects to be assigned a SqlConnection object, not a string. Use this instead: cmd.Connection = new SqlConnection( strConnectSQL ); Also, you should be sure to call Dispose on cmd at the end of the method, which releases the resources used by that command object. Josh
-
Connection expects to be assigned a SqlConnection object, not a string. Use this instead: cmd.Connection = new SqlConnection( strConnectSQL ); Also, you should be sure to call Dispose on cmd at the end of the method, which releases the resources used by that command object. Josh
-
As far as I know they do the same thing. Usually if a class implements both a Dispose and Close method, one of them just calls the other. Josh -- modified at 15:40 Wednesday 7th June, 2006 Oops, SqlCommand does not have a Close method. SqlConnection has a Close method. You should call Dispose on the SqlCommand.
-
Connection expects to be assigned a SqlConnection object, not a string. Use this instead: cmd.Connection = new SqlConnection( strConnectSQL ); Also, you should be sure to call Dispose on cmd at the end of the method, which releases the resources used by that command object. Josh
Okay, so the part number the user enters is an alpha-numeric id that never really gets used again. The SQL table Parts has two fields: Parts.PartNumber (which is what the user enters) and Parts.Id (which is the actual field that I have to match up with in Costs.PartID). Do I basically have to repeat everything to get that field before joining with costs? What I mean is, if I do Colin's reference:
SqlCommand cmd = new SqlCommand(); cmd.Connection = new SqlConnection(strConnectSQL); cmd.CommandText = SQLString; cmd.Parameters.Add ("@PartID", partNumber);And I want to do another @TruePartId (or something like that) do I have to go through the SqlCommand cmd = new SqlCommand again? I'm so sorry...my head is swimming...thank you guys so much for explaining this. -
Okay, so the part number the user enters is an alpha-numeric id that never really gets used again. The SQL table Parts has two fields: Parts.PartNumber (which is what the user enters) and Parts.Id (which is the actual field that I have to match up with in Costs.PartID). Do I basically have to repeat everything to get that field before joining with costs? What I mean is, if I do Colin's reference:
SqlCommand cmd = new SqlCommand(); cmd.Connection = new SqlConnection(strConnectSQL); cmd.CommandText = SQLString; cmd.Parameters.Add ("@PartID", partNumber);And I want to do another @TruePartId (or something like that) do I have to go through the SqlCommand cmd = new SqlCommand again? I'm so sorry...my head is swimming...thank you guys so much for explaining this.Maybe it's me, but I'm not clear on what you're asking. You might want to elaborate a little bit on the situation.
leckey wrote:
do I have to go through the SqlCommand cmd = new SqlCommand again?
:laugh: Copy-and-paste helps alleviate the pain. Josh