Error when using an apostrophe in text box 2

[apex82]

I have a text box on my page that calls a record field from an SQL database like so:

<textarea name="EditText" cols="60" rows="5" id="EditText"><% Response.Write obj_RS1("Notes") %></textarea>

The field Notes data type is varchar(8000)

When I add an ' (apostrophe) into the text box and submit I get the following error:

Incorrect syntax near '

Otherwise the record updates when I submit.

Can anyone help?

Thanks.

 

[foxbox]

classic.
you need to replace ' by ''

like this:

dim cEditText
cEdittext = replace(request.form("edittext"),"'","''")

' and use cEditText in your SQL

 

[wvdba]

foxbox,
if cEditText = this is john's note
and you replace it with: this john''s note
sql command text will still error out as soon as it encounters the first apostrophe.
i think it the apostrophe should be replaced by ` which is on the same key as ~
have you tested this?

 

[foxbox]

his is the "Fix-the_Quotes" UDF i normally include:

function FQ(c)   
 if isempty(c) or isnull(c) then
  FQ=" "
 else
  FQ = replace(c,"'","''")
 end if
end function

and yes that works. In fact you need to do this for every text field too (not only textarea fields).

It is also a first barrier for SQL injection.

 

[apex82]

Thanks, the original post by foxbox worked for me but only if the field already contained a value. Otherwise it produced an error.

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Foxbox where do you call the function FQ?

Thanks.

 

[vicvirk]

1. Replace ALL the single quotes in your DB with "'"
2. Replace ALL single quotes from requested variables with "'" before making the call to the db

' translates to a single quote on the screen, and as another member mentioned is a start to save you from SQL injection attacks.

I personally don't use the classic ASP request object by itself - implementing the following method is A LOT easier when you start from scratch, but if you have time, it can be implemented afterwards as well.

This is a drilled down version of my entire object, you can add to it as you see fit, but once you understand how it works, it's easy to add to it to handle all requests (cookies, querystrings, forms). Place it in a separate file and referece it through an SSI.

<%
	class requestObject
		public function form(strInput) 
			dim theValue
			theValue = server.HTMLEncode(request.form(strInput))
			form = encode(theValue)
		end function
		
		' since not ALL the values are encoded using HTMLEncode, do additional ones here
		private function encode(strInput)
			dim strReturn
			strReturn = strInput
			encode = replace(strReturn,"'","'")			
		end function
	end class
%>

initiate the object in your global header file, kill it in the global footer file.

<%
	set req = new requestObject
		myVar = req.form("myTBX")
	set req = nothing
%>

Now, whenever ANYTHING is submitted through one of your forms, the above class will ensure any HTML special characters will be replaced with their HEX equivilant. So people can't even put HTML tags in the db or use malicious code to infect your db/server.




--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[vicvirk]

^ sorry, the preview showed differently, but you can see how the translation works - here it is again, probably easier to read:

1. Replace ALL the single quotes in your DB with "'"
2. Replace ALL single quotes from requested variables with "'" [u]before[/u] making the call to the db

' translates to a single quote on the screen, and as another member mentioned is a start to save you from SQL injection attacks.

--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[apex82]

Many thanks guys!