The Tick " ' " Mark

[mrholt]

I have a problem with tick marks

When a user comes to one of my pages and fills out a form with (I’m working hard) the tick mark crashes my insert SQL statement and the info doesn’t get into my database.

I’ve written a function (see below) to try to solve this problem and it gets the data into the database but when I pull it out I have two tick marks.

function trimreplace(info)
info = trim(info)
info = Replace(info, "'", "''")
trimreplace = info
end function

xfname = trimreplace(request("fname"))

Any help would be appreciated

Matt

[Roy Sinclair]

Look to make sure you aren't running your function twice. If you are using a Stored Procedure to make the update make sure it isn't doubling the tick marks and also examine the view you're looking at the data through, it may be preconditioning the string for insertion into another database field instead of for display.

[mrholt]

NO the funtion is working like it is soppost to it replaces any ' with two ' . the data in the database looks like

I''m working hard

the select statements runs fine they pull the data out just like it is in the database.

so the only problems are the insert and update statements.

how do other sites handle this problem?

[Roy Sinclair]

Other sites handle it in pretty much the same fashion, the other alternative is to remove the tick marks entirely but I don't like that alternative because it does bad things to some text.

What you've shown is correct so far, if your database ends up with doubled tick marks then some code you haven't shown is also trying to solve the same problem with tick marks and you're getting doubled tick marks in the database.

ie.

Original string: I don't understand

After trimreplace: I don''t understand

What's being written to the database: I don''''t understand

Somewhere between where you run trimreplace and when you write to the database another bit of code is doing the same thing as trimreplace.

[mrholt]

no the funtion only runs once

Original string: I don't understand

After trimreplace: I don''t understand

What's being written to the database: I don''t understand

do you think i should just write a funtion that does the reverse of the trimreplace : replaces two ticks with one tick for the output to html?

Also: thanks for your help

[whammy]

This is a very simple problem to solve, I work with it every day.

All you should do is replace the single quotes in your variables with another one, since SQL "comments" single quotes this way... i.e.

Function RSQ(str)
If IsNull(str) Then str = ""
RSQ = Replace(str,"'","''")
End Function

"INSERT INTO tablename (myname) VALUES('" & RSQ(myname) & "')"

:)

This shouldn't be a problem as long as you ONLY use it in SQL - since that's SQL's way of commenting single quotes out so they don't throw an error. If you look in the db, whatever you entered with two single quotes will magically be transformed into one. :)

You don't want to do anything to the data otherwise (i.e. displaying it to the web page, or before you write it to the database) as it will do as you said... make "John O'Reilly" look like "John O''Reilly".

This is a slightly different topic, but the thing to use when displaying data to a page if you have any concerns about funny characters is Server.HTMLEncode(myString).

Here's a WORKING example - where "SQLSafe" is the same as the function I named "RSQ" above:


Sub WriteToDatabase() ''''''''''''''''''''''''''''
INSERTQUERY = "INSERT INTO scripts (cid,subid,author,authoremail,authorwebsite,title,script,comments,downloadlink,active,posttime) VALUES (" & cid & "," & subid & ",'" & SQLSafe(author) & "','" & SQLSafe(authoremail) & "','" & SQLSafe(authorwebsite) & "','" & SQLSafe(title) & "','" & SQLSafe(script) & "','" & SQLSafe(comments) & "','" & SQLSafe(downloadlink) & "'," & active & ",'" & posttime & "')"
Conn.Execute(INSERTQUERY)
End Sub ''''''''''''''''''''''''''''''''''''''''''


Of course, you could always just use a replace statement around each variable when dealing with SQL:

'" & Replace(variablename,"'","''") & "'

as well.

[Morgoth]

I made this along time go..

I didn't read any other posts, so... I am just posting this code for anyones use..

I put this code in my global.asa so I can use it on ANY ANY ANY ANY ANY ANY ANY!!!! page I see fit.. I just call the fuction with the the added, commented code..


Function Replace(ByVal String1, ByVal String2, ByVal String3)
Dim intPos
Dim LeftString, RightString

Do Until InStr(1, LCase(String1), LCase(String2)) = 0
intPos = InStr(1, LCase(String1), LCase(String2))
LeftString = Left(String1, intPos - 1)
RightString = Right(String1, Len(String1) - Len(String2) - Len(LeftString))
String1 = LeftString & String3 & RightString
Replace = String1
Loop
End Function
'Response.Write Replace("One Two 3", "3", "Three")
'So.... In the string "One Two 3" I want to change "3" into "Three"
'Replace(YOUR STRING, REPLACE THIS IN YOUR STRING, WITH THIS)


Enjoy... :)

Did I do good whammy?

[glenngv]

There is already a built-in function that does exactly that!
It's also called Replace.
Maybe when you call Replace function in your pages, the function that is called is the built-in one not yours :)

[raf]

Morgoth,

Like your appoarch, buth i fear that is was a waisted efford for the quotes part. Still, it shows that you know how that function should work.

Me, I'm just a guy who learned ASP in a three day coarse, and i learned and used it exactly the same way as whammy does. Never had any problems with it. (+ Server.HTMLEncode() also deals with a lot of other caracters).

[Roy Sinclair]

Originally posted by mrholt
no the funtion only runs once

Original string: I don't understand

After trimreplace: I don''t understand

What's being written to the database: I don''t understand

do you think i should just write a funtion that does the reverse of the trimreplace : replaces two ticks with one tick for the output to html?

Also: thanks for your help

When you write the string to the database, are you quoting it using the single quote character (') or the double quote ("). You should be using the single quote ('):


update mytable
set myfield = 'I don''t understand'


will result in I don't understand being written to the database but if you're using double quotes


update mytable
set myfield = "I don''t understand"


you'll get I don''t understand in the database.

[Morgoth]

Originally posted by glenngv
There is already a built-in function that does exactly that!
It's also called Replace.
Maybe when you call Replace function in your pages, the function that is called is the built-in one not yours :)

If there is, it doesn't work for me...
If I could call a replace function in ASP, not vb, then I would of never made this function. I made this function for that reason.

It works, and work effectivly!

[whammy]

Hmm... Replace(string,"whatever","WHATEVER") works perfectly for me with no custom made functions. ;)

I only use RSQ() function (which is two lines of code) because:

'" & RSQ(variable) & "'

is easier than

'" & Replace(variable,"'","''") & "'

if you use it a lot in your SQL statements(which you should in ASP, since otherwise anything that a user enters that might contain a single quote will throw an error...).

;)

[Morgoth]

What the???

There is no replace function! I have looked for it...
I don't have one at all!

That is why I made one for myself, because there is none.

I know you all must have one, so tell me where I can find it on my drive!

[raf]

Morgoth,

I'm slightly starting the beginning of a modification of my image of you. There is a VBscript function Replace(variable,"string","other string") that you can call.
Are you getting paranoid ? ;)

Were you can find it on your drive? Beats me. I just code and let the webserver process all these bits and bites.

[Morgoth]

So this is VBscript now?

I hardly use VBscript, I just type in:
<%
Response.Write Replace("1","2","3")
%>
or in other orders (321,231,132,312,etc) it just doesn't fix my strings.


I don't know what's going on, and I'll have to double check myself when I get the server back up and working and I will be able to test it out...

[waj_muller]

There are several ways to deal with this,the easiest way is to deal with it as a flat string,in other words force the script to ignore whats in the string,here's, note only for updates and inserts,searches will work if you use a simular approach........Have fun
This is for updating.....
Set orec = server.CreateObject("ADODB.Recordset")
SQL = "Select * FROM table WHERE id=('"&id&"')"
orec.Open SQL,dbconn, 3, 2, 1
orec("formfield")=Request.Form ("formfield")

orec.update
orec.Close
set orec=nothing

The recordset properties with the right cursors ignores whats in the string and parses it directly without interfering with your code.

And this one is for adding a new record.....

set orec = Server.CreateObject("ADODB.Recordset")
orec.open "tablename",dbconn,3,2,2
orec.addnew
orec("formfield")=Request.Form ("formfield")
orec.Update
set orec=nothing

Almost the same just a slightly diffrent keyset.This can be a bit more resource eating but not much,bemchmark it youl see its not much...
This method works the best,you can insert almost bloody anything.
Now imagene having to parse a paragraph of 2000 caracters the old way,now that eats resources,this does not no exstra worries not even when you pull it outof sql,it stays as you put it in, something nice from dear uncle microsoft..

Have fun

[whammy]

Hmm... interesting. At work we don't work too much with the different recordset properties (since there usually is no need to). I will have to play around with that. :)

To Morgoth:

What you have typed an example won't work... since "2" isn't in the string "1" to be replaced with "3".

This, however, will work:

<% = Replace("Morgoth","o","0") %>

Which output would be:

M0rg0th

:)

[Morgoth]

I knew someone would say that, but I ment 1, 2, and 3 as the way I write it out... the order.
1 = The string.
2 = The letter to replace.
3 = The letter that 2 will be replaced with.

But I don't know about it ever working for me. If I had it working before, would I of wrote up that replace function???

[whammy]

Perhaps you just had the syntax wrong. It even works for me on Windows ME running PWS. :D

[Morgoth]

That is what I think now...
I guess I'll have to review it...

[whammy]

OK... well my answer (or waj's above) should solve the problem.

I'm going to go ahead and close this since there was no response from the original poster and it's getting way off-topic. :|