allow users to enter single quotes in text box

I would like to allow people to use single quotes while writing something in a text box that will be updated in a DB.

Currently if people try to type for example: "Friend's"  there is an error.

Can someone help me modify my code below to allow me to do this?

sql = "UPDATE tblData SET back_colour = '" & request("bcolor") & "', font_size = '" & request("fsize") & "', main_text = '" & request("mtext") & "', page_name = '" & request("pname") & "', modified=true WHERE email ='" & session("usersession") & "'"

I need it to be enabled for the whole sql statement and not just one DB field.

Thanks
sh00tarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kevp75Commented:
use replace(request("FieldName"),"'","`")
0
kevp75Commented:
or:
Function SQLInjectProtect(str)
   SQLInjectProtect = replace(str,"'","`")
End Function

and replace:sql = "UPDATE tblData SET back_colour = '" & request("bcolor") & "', font_size = '" & request("fsize") & "', main_text = '" & request("mtext") & "', page_name = '" & request("pname") & "', modified=true WHERE email ='" & session("usersession") & "'"

with:
sql = "UPDATE tblData SET back_colour = '" & SQLInjectProtect(request("bcolor")) & "', font_size = '" & SQLInjectProtect(request("fsize")) & "', main_text = '" & SQLInjectProtect(request("mtext")) & "', page_name = '" & SQLInjectProtect(request("pname")) & "', modified=true WHERE email ='" & SQLInjectProtect(session("usersession")) & "'"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alorentzCommented:
shootar - the proper(most common) way to do this is to replace the ' with ''.  Similar to kevp75 answer, but like this:

function fmtSQL(txt)
'FORMAT DYNAMIC SQL TEXT
 txt = replace(txt, "'", "''")
 fmtSQL = txt
end function

0
sh00tarAuthor Commented:
Thank you both for the great anwers.  I will give the majority of the points to kevp75 since he was first to respond and gave me both the function and the sql line.

alorentz gave me the function that I'm using right now since i didn't want to change ' into ` as was the case with kevp75's code.

Thank you both.

I have another question which I hope any of you might be able to help me with.  Found on this page:

I don't know if I'm able to make links in this box but i'll try.

<a href="http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21815209.html">This Page</a>


Once again, Thank you.
0
kevp75Commented:
glad I could help, thanks for the grade
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.