473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How do update data more convient?

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);
Nov 17 '05 #1
9 1373
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.

Nov 17 '05 #2
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

*** Sent via Developersdex http://www.developersdex.com ***
Nov 17 '05 #3
Thanks Bruce!
It helps!

"Bruce Wood" <br*******@canada.com>
???????:11**********************@g47g2000cwa.googl egroups.com...
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.

Nov 17 '05 #4
Hi
Why not use Stored procedures.Its a simple SQL statement. on fly should
be used when your query is really really on fly , for instance a
complicated search screen.
-------
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Nov 17 '05 #5
Thank Shiv!
I'll try the Stored Procedure method later on. I'm still working on it :)

<sh**********@yahoo.com>
???????:11*********************@g14g2000cwa.google groups.com...
Hi
Why not use Stored procedures.Its a simple SQL statement. on fly should
be used when your query is really really on fly , for instance a
complicated search screen.
-------
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Nov 17 '05 #6
Is it possible to do a SQL injection attack even in using SqpParameter
method?

SqlParameter("@Address", SqlDbType.NVarChar, 90);
"Bruce Wood" <br*******@canada.com>
???????:11**********************@g47g2000cwa.googl egroups.com...
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.

Nov 17 '05 #7
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.

Nov 17 '05 #8
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?
"Bruce Wood" <br*******@canada.com>
???????:11*********************@g44g2000cwa.google groups.com...
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.

Nov 17 '05 #9
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...?

Nov 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
3
by: Jack | last post by:
I am currently trying to work on a program and I found it more convient to have a tempory object which would hold a copy of data loaded from a file so I wouldn't have to work with the overhead of...
4
by: Jonathan Upright | last post by:
Greetings to anyone who can help: I'm using WebMatrix to make ASP.NET pages, and I chose the "Editable DataGrid" at the project selector screen. As you may know, it defaults to the Microsoft...
6
by: Brett | last post by:
Not sure what the problem is here... Trying to update from a datagrid to an access database using vb.net... Its not updating the database but Im not getting any errors... Here is my code... ...
8
by: Zorpiedoman | last post by:
I keep getting a concurrency exception the second time I make a change and attempt to update a dataadapter. It appears this is by design, so there must be something I can do to avoid it. ...
5
by: Stephen Plotnick | last post by:
I'm very new to VB.NET 2003 Here is what I have accomplished: MainSelectForm - Selects an item In a public class I pass a DataViewRow to ItemInformation1 Form ItemInformation2 Form
2
by: Miro | last post by:
I will ask the question first then fumble thru trying to explain myself so i dont waste too much of your time. Question / Statement - Every mdb table needs a PrimaryKey ( or maybe an index - i...
2
by: sirdavethebrave | last post by:
Hi guys - I have written a form, and a stored procedure to update the said form. It really is as simple as that. A user can go into the form, update some fields and hit the update button to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.