allow users to enter single quotes in text box

Posted on 2006-04-15
Last Modified: 2008-03-03
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.

Question by:sh00tar
    LVL 25

    Expert Comment

    use replace(request("FieldName"),"'","`")
    LVL 25

    Accepted Solution

    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") & "'"

    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")) & "'"
    LVL 31

    Assisted Solution

    shootar - the proper(most common) way to do this is to replace the ' with ''.  Similar to kevp75 answer, but like this:

    function fmtSQL(txt)
     txt = replace(txt, "'", "''")
     fmtSQL = txt
    end function


    Author Comment

    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="">This Page</a>

    Once again, Thank you.
    LVL 25

    Expert Comment

    glad I could help, thanks for the grade

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    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 information …
    Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now