?
Solved

using the replace function

Posted on 2003-03-11
13
Medium Priority
?
249 Views
Last Modified: 2008-02-26
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>


0
Comment
Question by:kjhwwu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 2

Expert Comment

by:ActiveMedia
ID: 8115801

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
 
LVL 4

Expert Comment

by:whammy
ID: 8115871
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
 
LVL 4

Expert Comment

by:whammy
ID: 8115922
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kjhwwu
ID: 8116636
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
 
LVL 4

Expert Comment

by:Wakie
ID: 8117011
<%      
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
 

Author Comment

by:kjhwwu
ID: 8117272
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
 
LVL 4

Expert Comment

by:Wakie
ID: 8117312
That Replace statement is fine.

How does it "effect" your page?
0
 

Author Comment

by:kjhwwu
ID: 8117503
One example would be if I enter <B> in the search box the output will be in bold.
0
 
LVL 4

Expert Comment

by:Wakie
ID: 8117645
Please paste the code below you are currently using.

It's hard to tell from all of the variations already posted.
0
 
LVL 4

Expert Comment

by:Wakie
ID: 8117650
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
 
LVL 2

Accepted Solution

by:
ActiveMedia earned 80 total points
ID: 8118036

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
 
LVL 58

Expert Comment

by:Gary
ID: 9352443
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
 
LVL 4

Expert Comment

by:whammy
ID: 9417339
Thanks for following my suggestion.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question