Before saving in database I want the string to be free from any special characters and escape sequences?

Question

I want to save a string to a MSSQL 2008 server.

Sometimes some special characters are in there, which gives an exception while saving to the database.

What is the best way to handle this type of situation. Are there any built in functions that I may have overlooked to use?

score 3 · Answer 1 · edited May 23 '17 at 11:56

3

Use parameters. I believe using parameters handle these things internally

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

Why do we always prefer using parameters in SQL statements?

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

edited May 23 '17 at 11:56

Community

1
1

answered Feb 04 '14 at 14:59

Louis van Tonder

3,664
3
31
62

score 0 · Accepted Answer · answered Feb 04 '14 at 14:55

0

you have to check in your string for the occurrence of every special character and you have to replace every special character with their respective escape sequenced format.

you can use the following function for the same. it's a sample.

public String addescape(String str)
{
    String temp = "";
    foreach (char ch in str)
    {

        if (ch == '\0')
            temp += "";
        else if (ch == '\'')
            temp += "&#39;";
        else if (ch == '\\')
            temp += "&#34;";
        else if (ch == '<')
            temp += "&lt;";
        else if (ch == '>')
            temp += "&gt;";
        else
            temp += ch;
    }
    return (temp);
}

answered Feb 04 '14 at 14:55

Sumit Chourasia

2,394
7
30
57

This would create a lot of string allocations. You should be using the StringBuilder class instead (http://msdn.microsoft.com/en-us/library/system.text.stringbuilder(v=vs.110).aspx) – Zeph Feb 04 '14 at 15:04
You should use parameters. Friendly tip here to see my answer for your own future benefit. – Louis van Tonder Feb 04 '14 at 15:06

score 0 · Answer 3 · edited May 23 '17 at 10:31

0

How are you inserting into the database - can you use Paramaterized queries to do your insert? https://stackoverflow.com/a/5529010/685341

edited May 23 '17 at 10:31

Community

1
1

answered Feb 04 '14 at 15:00

Jay

9,561
7
51
72

score 0 · Answer 4 · answered Feb 04 '14 at 15:00

0

For special characters i would do adding some more character whatever you like to use.For example add * character. Write it to the database with modified state. And then when you read data from db you can remove out this characters you added. This is a solution(definitely there is a inbuilt function .NET enviroment) but i don't know...

answered Feb 04 '14 at 15:00

husonos

241
3
14

You should use parameters. Friendly tip here to see my answer for your own future benefit. – Louis van Tonder Feb 04 '14 at 15:06

Before saving in database I want the string to be free from any special characters and escape sequences?

4 Answers4