cobolinx1
asked on
String contains apostrophe and commas and generate sql error
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops, late, dportas and I have the same suggestion. I fully agree with dportas' earlier post
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 ...
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 ...
>>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.
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.
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.
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.
ASKER
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!!
basically you are encountering a self-inflicted sql injection attack.