Best data type for storing user input

[tabbytab]

I know this may sound a very simple and obvious question but I have had an error whilst updating a table that has caused me to question the obvious.

What is the best data type for storing textural/ numerical input from a user (a bit like a memo field).

The error I got was

Microsoft OLE DB Provider for SQL Server error '80040e14' 
Line 1: Incorrect syntax near 't'. 

/questionnaire.asp, line 356

The code arounb line 356 is

	Set altConnection = CreateObject("ADODB.Connection")
	altConnection.Open strConn
	
	For xx = 1 to 7
		If Len(Request.Form("txtField" & cStr(xx)) & "") <> 0 Then
		strSQL = "UPDATE tblResultsDataWrittenComments SET "
		strSql=strSql+"txtWrittenComment = '" & Request.Form("txtField" & cStr(xx)) & "' " 
		strSQL=strSQL+" WHERE(((intDelegate_obj_no)='" &Request.Form ("intDelegate_obj_no") &" ') AND ((intPerson_obj_no)=' " & (intPerson_obj_no) & " ') AND ((intWC_obj_no)=' " & cStr(xx) &" '))"
       
	   altConnection.Execute (strSQL)
End If
	Next    
	    altConnection.Close
        set altConnection = Nothing

Anything obvious?

any help much appreciated

TabbyTab

 

[tabbytab]

Having played around with the simulating user input I have been able to reproduce the error

If I type

Hello world '!'

When this is handled by sql I get error

Microsoft OLE DB Provider for SQL Server error '80040e14' 
Line 1: Incorrect syntax near '!'. 

/questionnaire.asp, line 356

I am new to this type of thing!!! (excuse the pun of !)

What do I need to test for / handle differently before storing user input to a table as data type ntext

Thanks in adavnce

TabbyTab

 

[gmmastros]

I'm more of a VB kinda guy, but I've seen this error many times. It usually involves a user entering information with an apostrophe. For example, a name like "Patrick O'Toole"

The apostrophe will cause problems unless you handle it appropriately.

I have a couple STRONG suggestions for your.

1. Do a goole search on SQL Injection Attack. Read a couple of the pages that goole finds for you.

2. Start using the ADO command object. It will handle the apostrophe problem for you and protect you from SQL Injection Attacks.

The bad news is that you have a lot of work cut out for you. The good news is that your website will be able to accomodate apostrophes and will be more secure.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom