Cleaning a string before SQL submission

I am posting ntext to a SQL database.  I know that I need to replace all apostrophes with double apostrophes and replace one quote with double quotes.

What else do I need to do to a string before submitting to a database?   Also, I need to be able to easily retrieve them....right now by replacing a single quote with a double quote, it retrieves without any extra code.  So I need to know if there's anything else pre-submit I need to do to a string, as well as post-retrieve that I need to get the original string back again.

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.

Jorge PaulinoIT Pro/DeveloperCommented:
>> What else do I need to do to a string before submitting to a database?  

Nothing more!
We have a "message board" of sorts on our intranet site and we use the following.

' Replace :
Replace(strNewMessage, ":", "-", 1)

' Replace carriage returns (enter key) with html break
Replace(strNewMessage, Chr(13), "<br>")

As well as the double quotes that you mentioned.  The first one (:) only caused an issue once and it seemed to be what followed it but I did not make a note of what that was.  After those 3 replaces the messages seem to read fine from a sql database using a sql datareader.

Correct me if I'm wrong, but I think writing parameterized SQL statements negated the need to do this.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

See this post by Scott Guthrie:

"This will prevent someone from trying to sneak in additional SQL expressions (since ADO.NET above knows to string encode the au_id value)..."

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
Try this convert the strings to their hexadecimal equivalent. Then store them on the server.

Convert them back when you need to use them.  Then you never have to worry about syntax. :-)

byte[] abData;
string Str;
int i;
Str = "Hello world!";
// Convert string to bytes
abData = System.Text.Encoding.Default.GetBytes(Str);
for (i = 0; i < abData.Length; i++)
      Console.WriteLine("{0:X}", abData[i]);
// Convert bytes to string
Str = System.Text.Encoding.Default.GetString(abData);
Console.WriteLine("'{0}'", Str);
jpaulino is right,
use a command object like SQLCommand, and parameters and it cleans up the string for you so that it avoids SQL injections as well as the ' problems.
Joel CoehoornDirector of Information TechnologyCommented:
Something else to mention here is that in addition to the security benefits, if you use a parameterized query sql server can cache the compiled version of your query on the server, helping it execute a little faster.
Are you serious?  I use parameters now as part of our upgrade to VS2005 but had no idea that the parameters would take care of this issue for me.  I knew it was good for SQL injections but wow that's fantastic.  Thanks to you and jpaulino for this.  If it was my quesion I would award you both the points.

Keep up the good work!
Jorge PaulinoIT Pro/DeveloperCommented:

I always do that way and I never had problems! Just replace the quotes (like arthurh88 said that as done it)
Hey that was me that brought up SQL parameters not jpaulino! :)
Your right zkeown :-( sorry about that.  Good idea.
Try to write  a common function that will put a quotes to all the string varaibles.
Just call that in place where ever you need double quotes. This will be easy to work with.
I apologize, I glanced incorrectly at the name.  The person who originally suggested the correct answer was zkeown.

To the rest of you who suggest writing your own handler routine, if all you do is replace quotes, you are wide open for SQL injection attacks.  A smart hacker could provide text where you expect data that would could reveal unintended data, insert new data, delete data, etc.  

It is true that you could write your own code to handle those cases by not allowing certain SQL keywords.  The dotnet command objects have been thoroughly tested and already exist.

I suggest you follow the advice of zkeown...which is also my advice and use command objects.

If you will visit an article that I wrote for CodeProject some time ago, it includes how to use command objects.  You can read the article, or just download the code and look at code samples:
arthurh88Author Commented:
thank you
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
.NET Programming

From novice to tech pro — start learning today.