Cleaning a string before SQL submission

>> 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.

hth

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.

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.

SStory,
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!

SStory,

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:

http://www.codeproject.com/KB/vb/vinheritdform.aspx

thank you