How do update data more convient?

  • Thread starter Thread starter Jason Huang
  • Start date Start date
J

Jason Huang

Hi,

I need to update my data based on the textboxes input on my windows form.
But I feel the following sql requery is to long and cumbersome.
Is there some more convient way to do the Update thing?
Thanks for help.

Jason

Here are some of my codes:
public void UpdateData(string strUpdateQry)
{
mConn=new SqlConnection(gstrConn);//new connection need to be here first
mConn.Open();
mComm=new SqlCommand(strUpdateQry,mConn);
mTrans = mConn.BeginTransaction();
mComm.Transaction=mTrans;
try
{
mComm.ExecuteReader();
mTrans.Commit();
}
catch(SqlException e)
{
mTrans.Rollback();
}
mConn.Close();
}//UpdateData

string strUpdate="INSERT INTO Contact (CustNo,Serial,Contact,";
strUpdate += "ContactDept, ContactTitle, ContactPhone,";
strUpdate += "ContactPhoneExt, ContactCellPhone, ContactFax,";
strUpdate += "Email) VALUES (";
strUpdate += "'" +m_iCustNo+ "', ";
strUpdate += "'" +txtSerial.Text+ "', ";
strUpdate += "'" +txtContact.Text+ "', ";
strUpdate += "'" +txtContactDept.Text+ "', ";
strUpdate += "'" +txtContactTitle.Text+ "', ";
strUpdate += "'" +txtContactPhone.Text+ "', ";
strUpdate += "'" +txtContactPhoneExt.Text+ "', ";
strUpdate += "'" +txtContactCellPhone.Text+ "', ";
strUpdate += "'" +txtContactFax.Text+ "', ";
strUpdate += "'" +txtEmail.Text+ "') ";

this.UpdateData(strUpdate);
 
Not really. You could use SQL parameters, and use a DataAdapter, but
you end up writing basically the same UPDATE statement in the end.

At least, though, you should use a StringBuilder, or maybe
String.Format instead of all of those concatenates.

However, I should point out that the way you've written your SQL,
you've left yourself wide open for a classic SQL injection attack. What
if a user types this into the txtContactDept text box:

' ); DROP TABLE Contact --

? You'll insert it right into the middle of your UPDATE and Boom! No
more contacts table. In fact, a user can do anything at all to your
database, right from your Contacts form.

You need to write yourself a little method:

public static string EscapeSQLText(string text)
{
StringBuilder sb = new StringBuilder();
foreach (char c in text)
{
if (c == '\'') { sb.Append('\''); }
sb.Append(c);
}
return sb.ToString();
}

Then change those direct insertions into something like this:

strUpdate += "'" + EscapeSQLText(txtContactDept.Text) + "', ";

This will insert two single quotes for each single quote in an input
string, and render SQL injection attacks from your form impossible.

Alternatively, if you use SqlParameters instead of a straight text
string SQL command, you'll also render SQL injection attacks impossible.
 
At a minimum, if you decide to do SQL on the fly, you need to double
single
quotes. Search on SQL Injection attacks.

Regards,
Jeff
 
Is it possible to do a SQL injection attack even in using SqpParameter
method?

SqlParameter("@Address", SqlDbType.NVarChar, 90);
 
No, because the values entered by the user, the ones assigned as
arguments to the SqlParameters, don't form part of the SQL query
string, and so their content won't be interpreted as SQL commands.

SQL injection attacks occur when you insert user input directly into
SQL queries, and so what the user types has the opportunity to be
interpreted as SQL command text.
 
Thanks Bruce!
So, if just for security reasons, I don't need to specify the SqlDbType in
the SqlParameter("@Address", SqlDbType.NVarChar, 90), am I right?
 
To be honest, I'm not sure. I know that you can specify the
SqlParameter without indicating the type or the maximum length, but I
don't know in which situations it's appropriate to do so and what the
consquences are of omitting that information. Maybe someone else can
answer that one...?
 
Back
Top