syntax error in replace statement

[middletree]

What's wrong with this code?

strLongDesc =
Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
rLf,"<br>"),"<",&lt,"<",&gt

Background:
This field is a textarea, and I needed to account for apostrophes, which I
had already done, and replaced line breaks with html line breaks on my page
which displays this stuff. That works fine. But then a user entered this
line, pasted from a log file:
SQL Statement: <SELECT * FROM etc., etc.

Which resulted in an actual dropdown box being displayed, and all the rest
of the description after that point was not displayed. So I tried to put in
code to replace the < and > with a &lt; and &gt; and the code I get when the
page loads is:

Microsoft VBScript compilation (0x800A03EA)
Syntax error
/AddToTicket.asp, line 75, column 106
strLongDesc =
Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
rLf,"<br>"),"<",&lt,"<",&gt

 

[middletree]

Well, I found the problem with the syntax, but now it simply doesn't work.

Here is my code:

strLongDesc =
Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
rLf,"<br>"),"<","&lt;"),">","&gt;")

and of course, I insert strLongDesc into a field in SQL Server, and when I
open it up in SQL Server, it still shows what I typed into the textarea,
which is <select>, whereas I should see &lt;select&gt;

What am I doing wrong?

 

[Aaron Bertrand [MVP]]

(a) you need double quotes around "&lt;" and "&gt;"

(b) how about:

strLongDesc = trim(server.HTMLEncode(Request.Form("LongDesc")))
strLongDesc = replace(replace(strLongDesc,"'","''"),VBCrLf,"<br>")

 

[Aaron Bertrand [MVP]]

when I open it up in SQL Server,

Where in SQL Server? Don't use Enterprise Manager for viewing data (e.g.
Return all rows). It is liable to do all sorts of funky things in order to
present the data to you in a "friendly" way (for some other issues see
http://www.aspfaq.com/2455). Run a SELECT query in Query Analyzer. Also,
response.write(sql) to make sure the replacements were done.

Another piece of friendly advice: store the statement as is, and use
Server.HTMLEncode when you *retrieve* and *display* it. HTML formatting has
little use/place inside the database.

 

[middletree]

OK, I've not gotten familiar with HTMLEncode. That will take care of the <
and other characters, then?

I'll try it out. Thanks, very much.

I also never knew that that you said about Enterprise Mgr vs. Query analyzer
in the other post. thanks

 

[middletree]

Well, I tried it exactly as you have it in (b) below, and it didn't work.
Also tried it with double quotes around the &lt, and it still stored my text
of <select> as <select>, which displayed as a dropdown.

 

[middletree]

Where in SQL Server? Don't use Enterprise Manager for viewing data (e.g.
Return all rows). It is liable to do all sorts of funky things in order to
present the data to you in a "friendly" way (for some other issues see
http://www.aspfaq.com/2455). Run a SELECT query in Query Analyzer. Also,
response.write(sql) to make sure the replacements were done.

As it turned out, the Query A vs. Ent Mgr were both displying correctly, but
I will make sure i view the data correctly from now on. But the problem is
that the replace function is not working. I verified this per your
suggestion with the response.write statement. It does just fine with the

 

[Aaron Bertrand [MVP]]

Then my guess is there are no < or > characters for replacement? Compare
this to the completed SQL statement:

Response.write(request.form("whatever_the_variable_was"))

 

[middletree]

Well, had typed:

<select>

into the textarea, and verified that this is what went in, both by
response.write, and looking into SQL Server.