[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

String contains apostrophe and commas and generate sql error

Posted on 2009-05-01
7
Medium Priority
?
1,041 Views
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.

Example
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?
0
Comment
Question by:cobolinx1
7 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 24283793
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.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24283822
don't use embedded literals like that, use bind variables instead.

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

Expert Comment

by:sdstuber
ID: 24283827
oops, late,  dportas and I have the same suggestion.  I fully agree with dportas' earlier post
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 6

Expert Comment

by:RPCIT
ID: 24283976
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 ...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24283983
>>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.
0
 
LVL 8

Expert Comment

by:RyanAndres
ID: 24284105
The answer is: Parameterized Queries

See following link:
http://msdn.microsoft.com/en-us/library/bb738521.aspx

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

Author Closing Comment

by:cobolinx1
ID: 31577084
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!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

834 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