using the replace function

I have created a search box, and I want to be able to use the replace function so that I can enter apostrophes and if a user enters a < character like in html it will turn into a blank space. I think it should look something like this.
        replace(search_string, "'", "''")
        replace(search_string,"<", "&nbsp")

I don't know if this is entirely correct and also I don't know where to put this in my code could some one help me out. This is the code for that page.
<!-- #include file="datconnect.asp"-->
<!--#include file="../database/adovbs.inc"-->
<!--#include file="ListAuthors.asp"-->
<html>
<head>
<title>Let'sRead.com</title>
<link rel="stylesheet" href="Bookstore.css" type="text/css">
</head>
<body>
<div align="center">
  <center>

<table border="0" cellpadding="0" cellspacing="0" width="800">
    <tr>
     <td>
   <a href="default.asp">
         <!-- #include file="header.asp"--></a>
            <!-- #include file="search.asp"-->
            <!-- #include file="browse.asp"-->

<%      
     dim objRS, strSQL, search_string, irecordCount

        search_string = Request("search_string")

If search_string > "" then
   strSQL = "SELECT DISTINCTROW book_description.title, book_description.description, book_description.ISBN "&_

"FROM book_authors INNER JOIN (book_category INNER JOIN book_description ON book_category.ISBN = book_description.ISBN) ON book_authors.isbn = book_category.ISBN "&_

"WHERE (((book_authors.Lname) Like '%"&search_string&"%') OR ((book_category.Category) Like '%"&search_string&"%') OR ([book_description]![title] Like '%"&search_string&"%') OR ((book_description.description) Like '%"&search_string&"%'));"
 
else
 
strSQL = "SELECT DISTINCT book_category.Category, book_description.ISBN, book_description.title, book_description.description " &_

"FROM book_authors INNER JOIN (book_description INNER JOIN book_category ON book_description.ISBN = book_category.ISBN) ON book_authors.isbn = book_category.ISBN " &_

"WHERE (((book_category.Category)='" & request("category") & "'));"
 
 
end if

 'Create a recordset object and execute the SQL statement    
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open strSQL, objConn, 1
%>

<td  valign="top"><font face="Comic Sans MS" color="#9966FF"><%response.write "There were " & objRS.RecordCount %>&nbsp
<%=request("search_string")%> books matching your request</font><br><br>

<%Do While Not objRS.EOF %>
<a href="product.asp?isbn=<%=objrs("isbn")%>">
<img height="97" width="69" hspace="7" vspace="3" src="http://www.cbe.wwu.edu/sandvig/images/<%=objRS("ISBN")&".01.20TLZZZZ.gif"%>" align="left" border="0">
</a>

<font face="Comic Sans MS" color="#9999FF">

<%response.write objrs("title")%></font><br><font size=-1><%subListAuthors objRS("ISBN")%></font>
<p>

<font face="Comic Sans MS" color="#CC6699">
<% response.write Left(objRS("description"),300) %>
</font></p>

<%
  objRS.MoveNext
    Loop
%>


</td>
    </tr>
   </table>

  </center>
</div>
 <!-- #include file="footer.asp"-->
</body>
</html>


kjhwwuAsked:
Who is Participating?
 
ActiveMediaConnect With a Mentor Commented:

kjhwwu you wrote:
search_string = replace(search_string,"<", "&nbsp")

This is NOT exactly correct; it should be "&nbsp;" instead of "&nbsp"   (note the semi-colon ; ).


Also, when I previously said "You should also consider using HTMLEncode and URLEncode functions" I should have made claear that I meant to get a fuller understanding of the need/methods of formatting strings in this overall context not specifically to deal with apostrophe.

HTH
0
 
ActiveMediaCommented:

If the input item is going to be used as a key for searching your database using SQL, it is important that the single apostrophe is replaced with two single apostrophes so that it is understood that you really mean an apostrophe as a text character (e.g. the surname you are looking for might be O'Grady).  

You can do this with the replace function:
e.g. strSurname=replace(strSurname,"'", "''")

And you usually do this just before using it as part of the SQL expression.

If you did not do this replacement, the SQL parser would misinterpret the single apostrophe to mean the end of the string:
e.g:  "SELECT * FROM myTable where surname='o'grady'"
(THIS WOULD FAIL.)

You might also need to prevent the input of an HTML tag in a search field and you can do this by replacing "<" with some other character (or none):
e.g. strSurname=replace(strSurname,"<","---")

This helps to prevent malicious codes being input which might distort the output. e.g. if someone entered <style="font-size:300; color:red;"> in a search field together with some foul language it could be mess up your results display or worse.

You should also consider using HTMLEncode and URLEncode functions to ensure that your string fields are correctly sent/received.

HTH
0
 
whammyCommented:
Yes, there are worse things that can be done if you don't comment your single quotes with another that ActiveMedia is not mentioning, and I will not mention either (for reasons I won't mention if someone doesn't already know them).

Basically you need to do what ActiveMedia said here:

strSurname=replace(strSurname,"'", "''")

However, I usually use a simple function to do it (this is a good habit since you can make sure you use it on all fields that query a database):

<%
Function SQLSafe(byVal str)
   If IsNull(str) Then str = ""
   SQLSafe = Replace(str,"'","''")
End Function
%>

myname = "O'Grady"
"SELECT * FROM myTable where surname='" & SQLSafe(myname) & "'"

Of course, it looks like you also don't want your HTML to be broken by people inserting HTML characters. As ActiveMedia _also_ noted, there is a built-in function in ASP (and ASP.NET, FYI) that prevents this, called Server.HTMLEncode().

Very few ASP developers know how to use this from my experience apparently, which I find a bit surprising, since it is a developer's best friend.

If I were you I would look at the Server.HTMLEncode() function VERY closely (google it!), since if you use it correctly it will keep your HTML and forms from being broken.

However, you should only use this function when _displaying_ data, not when _requesting_ it, otherwise it will alter your data, inserting HTML equivalents of ASCII characters into your variable instead.

That's the most common mistake made by people unfamiliar with HTMLEncode, and they see that it messes up their data and give up on it, without understanding what it's used for.

P.S. If you use this stuff (which you should - always!), please give the points for this post to ActiveMedia - I'm just expanding upon what he already said, to reinforce hiw excellent advice - he deserves the "points". ;-)


0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
whammyCommented:
Actually after re-reading ActiveMedia's post, I think this is incorrect regarding Server.HTMLEncode():

[quote]
You might also need to prevent the input of an HTML tag in a search field and you can do this by replacing "<" with some other character (or none):
e.g. strSurname=replace(strSurname,"<","---")
[/quote]

That's what Server.HTMLEncode() is supposed to be used for, if you end up displaying the search criteria, at any rate. ;-)

There's no reason to limit searches to plain text, what if someone wants to search an HTML entry in a database? ;-)
0
 
kjhwwuAuthor Commented:
I am still a little confused, would it look something like this. If this is my SQL statement.
<%      
    dim objRS, strSQL, search_string, irecordCount

       search_string = Request("search_string")
       search_string = replace(search_string, "'", "''")
       search_string = replace(search_string,"<", "&nbsp")
 
If search_string > "" then
  strSQL = "SELECT DISTINCTROW book_description.title, book_description.description, book_description.ISBN "&_

"FROM book_authors INNER JOIN (book_category INNER JOIN book_description ON book_category.ISBN = book_description.ISBN) ON book_authors.isbn = book_category.ISBN "&_

"WHERE (((book_authors.Lname) Like '%"&search_string&"%') OR ((book_category.Category) Like '%"&search_string&"%') OR ([book_description]![title] Like '%"&search_string&"%') OR ((book_description.description) Like '%"&search_string&"%'));"

else

strSQL = "SELECT DISTINCT book_category.Category, book_description.ISBN, book_description.title, book_description.description " &_

"FROM book_authors INNER JOIN (book_description INNER JOIN book_category ON book_description.ISBN = book_category.ISBN) ON book_authors.isbn = book_category.ISBN " &_

"WHERE (((book_category.Category)='" & request("category") & "'));"
 

end if

'Create a recordset object and execute the SQL statement    
   Set objRS = Server.CreateObject("ADODB.Recordset")
   objRS.Open strSQL, objConn, 1
%>
0
 
WakieCommented:
<%      
dim objRS, strSQL, search_string, irecordCount

search_string = Request("search_string")
search_string = replace(search_string, "'", "''")
search_string = replace(search_string,"<", "&nbsp")

If search_string <> "" then
     strSQL = "SELECT DISTINCT book_description.title, book_description.description, book_description.ISBN FROM book_authors INNER JOIN (book_category INNER JOIN book_description ON book_category.ISBN = book_description.ISBN) ON book_authors.isbn = book_category.ISBN WHERE (((book_authors.Lname) Like '%"&search_string&"%') OR ((book_category.Category) Like '%"&search_string&"%') OR ([book_description]![title] Like '%"&search_string&"%') OR ((book_description.description) Like '%"&search_string&"%'));"
else
     strSQL = "SELECT DISTINCT book_category.Category, book_description.ISBN, book_description.title, book_description.description FROM book_authors INNER JOIN (book_description INNER JOIN book_category ON book_description.ISBN = book_category.ISBN) ON book_authors.isbn = book_category.ISBN WHERE (((book_category.Category)='" & request("category") & "'));"
end if

'Create a recordset object and execute the SQL statement    
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, 1
%>
0
 
kjhwwuAuthor Commented:
I have got the apostrophe to go through with out an error but when I enter a html tag in the search box it still effects the page. Is there something wrong with the replace statement for that part.

search_string = replace(search_string,"<", "&nbsp")
0
 
WakieCommented:
That Replace statement is fine.

How does it "effect" your page?
0
 
kjhwwuAuthor Commented:
One example would be if I enter <B> in the search box the output will be in bold.
0
 
WakieCommented:
Please paste the code below you are currently using.

It's hard to tell from all of the variations already posted.
0
 
WakieCommented:
Bear in mind, you have only given this question 20 points.

You will get a far better answer if you increase the point value.
0
 
GaryCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept Answer by ActiveMedia

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
GaryC123
EE Cleanup Volunteer
0
 
whammyCommented:
Thanks for following my suggestion.

Remember - Server.HTMLEncode() is your best friend against cross-site scripting (among other things)!
0
All Courses

From novice to tech pro — start learning today.