Freakish ASP quote handling

[jakyra]

I've got this part of my site where people enter information into a textbox.

I learned long ago (and far away ) that with ASP you need to replace ' with '' and " with "" or it choaks because it thinks it's a string terminater.

I've got a function that does this (and other things) for me...

Code: Select all

function switchQuotes(text)
	while instr(text, "  ") > 0
		text = Replace(text, "  ", " ")
	wend
	text =  Replace( _
		Replace( _
		Replace( _
		Replace( _
		Replace( _
		Replace( Trim(text), "'", "''"), _
				    "\n", " "), _
				    vbcrlf, " "), _
				    "|", ""), _
				    "%", ""), _
				    chr(34), """")
'	response.write "switchme!"
	switchQuotes = text		
end function

My problem is that *sometimes* this backfires. It only seems to happen during the editing of the text. But this review works fine:
This book is the follow-up to Surely You're Joking, Mr. Feynman: Adventures of a Curious Character and I recommend that it should be read only after reading that volume. The book is organized as a series of loosely related chapters that illustrate Feynman's unique perspective and his ways of interacting with the world. It is less personal than the preceding book in that some of the chapters are more about science-related topics and less about Feynman. Some of the chapters fill in details that were introduced in "Surely You're Joking"

but this one doesn't:
The comments of "A Reader" below obviously mean that the book is truthful and spot on. Otherwise, why would he get so upset! I enjoyed this book and find it scary that so many people get their news from these liars. It's not possible for everyone in America to confirm facts that the media spit out. America should demand truth in journalism. Whether you are right or left, at least you want to know the truth, dont you? Thank you Al Franken.

If I edit the second review by adding "Testing 1,2" to the end, that part is not added and It's looks like It''s.

When I display the SQL to the page, it's correct:
UPDATE TblReviews SET Title='Lies and the Lying Liars Who Tell Them: A Fair and Balanced Look at the Right', Author='Al Franken', Illustrator='', LevelID=5, Genre='Biography', Culture=134, Review='The comments of "A Reader" below obviously mean that the book is truthful and spot on. Otherwise, why would he get so upset! I enjoyed this book and find it scary that so many people get their news from these liars. It''s not possible for everyone in America to confirm facts that the media spit out. America should demand truth in journalism. Whether you are right or left, at least you want to know the truth, dont you? Thank you Al Franken. Testing 1,2' WHERE ISBN='0-525-94764-7' AND Reviewer=1852

But what I get on my display page is wrong:
The comments of "A Reader" below obviously mean that the book is truthful and spot on. Otherwise, why would he get so upset! I enjoyed this book and find it scary that so many people get their news from these liars. It''s not possible for everyone in America to confirm facts that the media spit out. America should demand truth in journalism. Whether you are right or left, at least you want to know the truth, dont you? Thank you Al Franken.

AAAAAAAAAAAAAAAAAAAAAAAARG!

This seems to be a bug sort of thing, but I have no idea how to work around this..

Does anyone know?

Thanks
jakyra

[Rob]

SQL is fine with double quotes...

You only need to double up apostrophe's

[Nick]

I think there are two issues that you are getting mixed up with here between single quotes and double quotes.

If you are building up a query to a database, then the only replace you do is single quotes. It deals with double quotes just fine.

However if you are building up a string in ASP, then you only need to replace double quotes.

So for example.

MyString = "Hey ""Nick"", you're here!"

MyString now contains the text;

Hey "Nick", you're here!

Note that you don't have to double up the single quotes.

But if you then want to put this in the database, you need to double up the single quotes.

Database.Execute "INSERT INTO dasf (asdf) values (" & Replace(MyString, "'", "''") & ")"

But you don't have to do a replace on the double quotes.

[Rob]

You could simplify that function a lot. All it has to do is take a string and make it SQL safe!!

Try this:-

Code: Select all

FUNCTION sqlsafe(s) 
pos = InStr(s, "'")
      While pos > 0
           s = Mid(s, 1, pos) & "'" & Mid(s, pos + 1)
           pos = InStr(pos + 2, s, "'")
      Wend
sqlsafe=s
END FUNCTION

or better still, this:-:-

Code: Select all

FUNCTION sqlsafe(s) 
   sqlsafe=replace(s,"'","''")
END FUNCTION

[Nick]

p.s. just noticed something, you've got the following;

Replace(...., chr(34), """")

As a replace. note that """" is infact only one double-quote. Remember in ASP, you have to put two "" to signify the character, not a string delimiter. So 4 x " means one to open up the string, two to signify a double quote, then one to close the quote. So what you need is;

Replace(...., """", """""")

Or to make things simpler;

Replace(...., chr(34), chr(34) & chr(34))


Note that I don't know if this is at all related to your problem, but I thought I'd point it out.

[Nick]

<blockquote id="quote"><font size="1" face="geneva, verdana, arial" id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Rob

You could simplify that function a lot. All it has to do is take a string and make it SQL safe!!

Try this:-

Code: Select all

FUNCTION sqlsafe(s) 
pos = InStr(s, "'")
      While pos > 0
           s = Mid(s, 1, pos) & "'" & Mid(s, pos + 1)
           pos = InStr(pos + 2, s, "'")
      Wend
sqlsafe=s
END FUNCTION

<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">

Or infact just MyString = Replace(MyString, "'", "''")
I think you're look at old code

However his function does more than that - it removes double spacing, trims it, removes various invalid characters etc.

[Rob]

see the addition to the above post Nick... I edited the topic while you were posting.

[jakyra]

I got it to work!

Thanks much for everyone's comments. Although I'm leaving my function complicated

I did take out the " --> "" since it's unecessary.

Thanks everyone!

jakyra