String contains apostrophe and commas and generate sql error

Posted on 2009-05-01
Last Modified: 2013-12-25
I've created a sql string that I'm going to run a command with and the values came from fields on the screen. One of them could contain apostrophes and commas. I've taken care of the apostrophes with the .replace(" ' "." '' ") method but the commas are causing a problem when inserting.

textbox1.text = " 'This' , 'is' a 500 point question , that is up for grabs."
textbox2.text = "Who wants it?"
sqlstring =  "insert into my table values(" ' " & textbox1.text & "," & textbox2.text "

the sql string will look like this:
insert into mytable values(''This' , ''is'' a 500 point question , that is up for grabs.
, "Who wants it?'" ")

The commas in  'This' , 'is' a 500 point question , that is up for grabs. is making it look like there is really four values to insert. How to escape the comma?
Question by:cobolinx1
    LVL 22

    Accepted Solution

    Don't ever write SQL the way you have done. It's insecure, inefficient and unreliable. The correct way to do it is to use the Parameters collection. You don't have to escape any characters, just pass the string.
    LVL 73

    Expert Comment

    don't use embedded literals like that, use bind variables instead.

    basically you are encountering a self-inflicted sql injection attack.
    LVL 73

    Expert Comment

    oops, late,  dportas and I have the same suggestion.  I fully agree with dportas' earlier post
    LVL 6

    Expert Comment

    I would agree that parameters are the way to go.. but ...  when I'm converting data, I will often go the "cheap/ lazy" route.

    I use a function like this...
            public string SQDQ(string inVal)
                string outVal = inVal.Replace("'", "''");
                outVal = outVal.Replace("\"", "\"\"");
                return outVal;

    then wrap your "unsafe" strings in it..  like SQDQ(textbox1.text)

    SQL will interpret the double ' or " as a single "escaped" version ...
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>How to escape the comma?<<
    There is no need to escape a comma.  Only single quotes need to be escaped.  That is if you are unable to use parametized queries, as the previous comments have suggested.
    LVL 8

    Expert Comment

    The answer is: Parameterized Queries

    See following link:

    This will ensure safe queries no matter what and also will make maintaining your code much easier and cleaner in the long run.

    Author Closing Comment

    Unfortunatly I made a class to deal with db connections, getting datasets;datareaders; and returning simple queries. I just made a bunch of optional variables to handle the sql parameters. As long as their optional I don't need to change all my calls. Cheers!!

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    733 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

    24 Experts available now in Live!

    Get 1:1 Help Now