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?

[Webinar] Streamline your web hosting managementRegister Today

zkeownConnect With a Mentor Commented:
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)..."
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.

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Correct me if I'm wrong, but I think writing parameterized SQL statements negated the need to do this.
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
All Courses

From novice to tech pro — start learning today.