ASP Insert to SQL fails

[PlyFour]

I am new to programming and .net - Trying to setup a simple page to submit email, name and comments to a sql database into a table called "GuestList". Input comming from three text boxes. No error until I try to submit the info. Here is the code on the submit button. I am trying to do this w/o a datagrid. Any suggestions welcome - THANKS!

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ConnString As String = "server='snapghiis01'; user id='exit3'; password='exit3user'; Database='Exit3'"
Dim sqlConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConnString)
Dim sqlString As StringBuilder = New StringBuilder(400)
Dim sqlCommand As System.Data.SqlClient.SqlCommand
sqlConn.Open()
sqlString.Append("INSERT INTO GuestList(Email,Name,Comments)VALUES")
sqlString.Append("?" & TBEmail.Text & "?,")
sqlString.Append("?" & TBName.Text & "?,")
sqlString.Append("?" & TBComments.Text & "?)")
sqlCommand = New System.Data.SqlClient.SqlCommand(sqlString.ToString(), sqlConn)
sqlCommand.ExecuteNonQuery()
sqlConn.Close()
End Sub

 

[riley]

sqlString.Append("INSERT INTO GuestList(Email,Name,Comments)VALUES")
sqlString.Append("?" & TBEmail.Text & "?,")
sqlString.Append("?" & TBName.Text & "?,")
sqlString.Append("?" & TBComments.Text & "?)")

I think you are missing a "(" at the beginning of the Values clause. It should be:
sqlString.Append("(?" & TBEmail.Text & "?,")

I'm not sure this makes any difference, but you might want to put a space before "Values", as in:
sqlString.Append("INSERT INTO GuestList(Email,Name,Comments) VALUES")

riley

 

[PlyFour]

Thank you - I made your suggested changes but still get an error Line 1: Incorrect Syntax near '"
It is quite a challange to determine what syntax to use.

 

[riley]

Thank you - I made your suggested changes but still get an error Line 1: Incorrect Syntax near '"
It is quite a challange to determine what syntax to use.

I think the error is referring to your connection string code. Try removing the single quotes and the spaces you have in the connection string:

Dim ConnString As String = "server=snapghiis01;user id=exit3;password=exit3user;Database=Exit3"

riley

 

[Mart]

An SQL Injection is possible with that code. If someone put '1;DROP TABLE xxx' in one of the textboxes they could delete all your data!

To counter that use Stored procedures with parameters, that will also make your coding a lot cleaner.

 

[Logan]

An SQL Injection is possible with that code. If someone put '1;DROP TABLE xxx' in one of the textboxes they could delete all your data!

To counter that use Stored procedures with parameters, that will also make your coding a lot cleaner.

I'm not sure that's true. I don't use stored procedures and I tried entering drop table and all I got was a record with 'drop table' as the text. But then I also convert all quotes before storing in the database, so maybe this is the key?

Can you elaborate further? I'd like to be sure that I am protecting my data.

 

[Mart]

I'm not sure that's true. I don't use stored procedures and I tried entering drop table and all I got was a record with 'drop table' as the text. But then I also convert all quotes before storing in the database, so maybe this is the key?

Can you elaborate further? I'd like to be sure that I am protecting my data.

Converting quotes won't help. You need to put ";DROP TABLE xxx" as the text (i.e. with a semicolon). If you use stored procedures it takes the input text literally i.e. it will not execute it. If you start injecting text into strings SQL Server thinks that when you put a semicolon you are trying to start a new query and will execute it. For more info search for 'SQL Injections'. There is a MSDN article about this aswell, it recommends using stored procedures.

 

[Logan]

Converting quotes won't help. You need to put ";DROP TABLE xxx" as the text (i.e. with a semicolon). If you use stored procedures it takes the input text literally i.e. it will not execute it. If you start injecting text into strings SQL Server thinks that when you put a semicolon you are trying to start a new query and will execute it. For more info search for 'SQL Injections'. There is a MSDN article about this aswell, it recommends using stored procedures.

In my test, the ; was still within quotes as part of my SQL statement though, so it was interpretted as data and not a command. SQL didn't seem to simply drop the previous command and execute the drop command.

I will check out the article. Thanks.

 

[riley]

In my test, the ; was still within quotes as part of my SQL statement though, so it was interpretted as data and not a command. SQL didn't seem to simply drop the previous command and execute the drop command.

I will check out the article. Thanks.

Mart is correct. The right series of characters would trick sql server into thinking there are multiple commands to execute. It is unlikely that someone would know the name of the table to drop and know enough about the structure of the sql statement to accurately fool sql, but it is entirely possible. Also, it is amazing how much patients malicious people have; enough trial and error and someone might just make it work.

Stored procedures are the best solution to this problem. Short of that, you should at least write some code to validate the contents of the textboxes to ensure they don't contain such characters. Although sql injection is not as big an issue for MS Access, because Access does not allow multiple sql statements, there are still some problems that can occur. Note that a single quote within the contents of the comments textbox would not by itself cause an sql injection, but it would make your Insert fail, regardless which database you use.

Generally, one must be very carefull about accepting raw data from textboxes, particularly if you are going to turn it around and display it on a page. Before you display such data, you should encode it, thereby avoiding the execution of scripts that someone might type into the textboxes. This is know as script injection.

riley