开发者

Please help figure out what's wrong with this database update statement from text boxes in C#

comm.CommandText =
        "UPDATE studbiodata([Registration Number], Surname, Othernames, Email,"
      + " Phone, Address, Sex, [Birth Date], Religion, [Marital Status],"
      + " [No. of Children], [Next of Kin], Relationship, [Next of Kin Address])"
      + "SET ([Registration Number] ='" + txtRegNo.Text
      + "', Surname = '" + txtSurname.Text
      + "', Othernames = '" + txtOthernames.Text
      + "', Email = '" + txtEmail.Text
      + "', Phone = '" + txtPhone.Text
      + "', Address = '" + txtMainAdd.Text
      + "', Sex = '" + txtSex.Text
      + "', [Birth Date]= '" + txtBirthDate.Text
      + "', Religion = '" + txtReligion.Text
      + "', [Marital Status]= '" + txtMarital.Text
      + "', [No.of Children] = '" + txtChildren.Text
      + "', [Next of Kin]= '" + txtKin.Text
      + "', Relationship ='" + txtRelationship.Text
      + "', [Next of Kin Address]= '" + txtKinAdd.Text
      + "' WHERE [Registration Number]= '" + txtRegNo.Text + "')";

The error message I get after trying to update from a web-form is:

 System.Data.开发者_如何学GoSqlClient.SqlException (0x80131904): Incorrect syntax near '('. Incorrect syntax near the keyword 'SET'.
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
 at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 at Transcript.studentprofileedit.btnUpdate_Click(Object sender, EventArgs e)
 in C:\Users\Administrator.femzy-PC\Documents\Visual Studio 2010\Projects\Transcript Reloaded\Transcript\studentprofileedit.aspx.cs:line 119


The first thing I see wrong is that it's a classic example of how to make your site vulnerable to SQL injection. This is exactly how NOT to code a sql statement using user input.

Probably what's happening is that the user is entering something with a single quote (apostrophe) and it's messing with the statement.

https://www.owasp.org/index.php/SQL_injection

http://en.csharp-online.net/ASP.NET_Security_Hacks%E2%80%94Avoiding_SQL_Injection

Use parameterized queries or stored procedures with parameters as your absolute first step. That will eliminate erros caused by unexpected user input ("O'Connor" as a last name, for example, would cause an error in a statement coded this way). Then if there are still problems, go back and look at the statement. You can use user input (almost every program does) - you just have to do it safely.

(last edit - one more good article)

http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html


The syntax is wrong, you don't need to specify the column list first.

UPDATE studbiodatarital SET [Registration Number] ='" + txtRegNo.Text + "', Surname = '" + txtSurname.Text + "', Othernames = '" + txtOthernames.Text + "', Email = '" + txtEmail.Text + "', Phone = '" + txtPhone.Text + "', Address = '" + txtMainAdd.Text + "', Sex = '" + txtSex.Text + "', [Birth Date]= '" + txtBirthDate.Text + "', Religion = '" + txtReligion.Text + "', [Marital Status]= '" + txtMarital.Text + "', [No.of Children] = '" + txtChildren.Text + "', [Next of Kin]= '" + txtKin.Text + "', Relationship ='" + txtRelationship.Text + "', [Next of Kin Address]= '" + txtKinAdd.Text + "' WHERE [Registration Number]= '" + txtRegNo.Text + "')

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜