Automatically escaping special characters for SQL query?

Does C# have an "escape_string()" sort of function?

Page 1 of 1

3 Replies - 44627 Views - Last Post: 15 January 2009 - 05:41 AM Rate Topic: -----

#1 soulhunter123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-December 08

Automatically escaping special characters for SQL query?

Posted 09 December 2008 - 03:05 PM

Hi all!

I have an INSERT query that puts a block of text into an SQL field of type "memo". Sometimes the text contains special characters like apostrophes, commas, etc. and I would like to escape all of these automatically each time (without replacing each individual symbol as needed!)

Is there a function something like escape_string(); for C# which can escape all the special characters for me in a string?

Thank you v. much.

Is This A Good Question/Topic? 0
  • +

Replies To: Automatically escaping special characters for SQL query?

#2 soulhunter123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-December 08

Re: Automatically escaping special characters for SQL query?

Posted 09 December 2008 - 03:21 PM

I discovered the apostrophes were the culprits. You can have a method to change them like this:

			public static string ReplaceEscape(string str)
			{
				str = str.Replace("'", "''");
				return str;
			}


Anyone know a better function?
Was This Post Helpful? 0
  • +
  • -

#3 anima  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-January 09

Re: Automatically escaping special characters for SQL query?

Posted 15 January 2009 - 03:30 AM

if (strValue.Contains("'"))
{
strValue = strValue.Replace("'", "' + NCHAR(96) + '");
}
Was This Post Helpful? 0
  • +
  • -

#28 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,854
  • Joined: 16-October 07

Re: Automatically escaping special characters for SQL query?

Posted 15 January 2009 - 05:41 AM

No, and please don't. Creating an SQL string manually is a poor way to do it. You really want to bind your parameters. It clears up your problem, prevents SQL attacks, and makes the query run faster.

Here's a quick example:
// we'll assume there's a connection set up in the instance

// pass a value
public void updateLog(string msg) {
	// create your command, not the @msg where the parameter will go
	SqlCommand cmd = new SqlCommand("insert into log(msg) values (@msg)", this.conn);

	// bind the value to the parameter reference
	cmd.Parameters.AddWithValue("@msg", msg);

	// fire
	try {
		cmd.Connection.Open();
		cmd.ExecuteNonQuery();
	} finally {
		cmd.Connection.Close();
	}
}


This post has been edited by baavgai: 15 January 2009 - 05:41 AM

Was This Post Helpful? 1

Page 1 of 1