C# and ADO.Net - Cheap Question!

  • Thread starter Thread starter Matthias S.
  • Start date Start date
M

Matthias S.

Hi,

I'd like to INSERT a string into a database using C#. The way I've tried
it is the following:

sQuery = string.Format("INSERT INTO myTable (Header, Body) VALUES
('{0}','{1}')", sHeader, sBody);

It works fine until I have an occurence of ' (an apostrophy) in either
the string-variables sHeader or sBody. How can I circumvent this?

Thanks in advance!
 
Thanks for your reply. I actually don't know whether the sHeader or
sBody contain a '.

So if I go and Replace all occurences of ' with \' my querystring looks
like this:

"INSERT INTO myTable (Header, Body) VALUES ('thei\'re house is
fine.','house')"

Which is not quite what I expected. Again, thanks for your help which is
highly appreceated.

/Matthias
 
You have two choices. Either write a method like this:


/// <summary>
/// Escapes the given text so that it can appear within single
quotes in a
/// <see cref="DataColumn.Expression"/>.
/// </summary>
/// <param name="textToEscape">The string to escape.</param>
/// <returns>The escaped string, ready to be included in a
/// <see cref="DataColumn.Expression"/>.</returns>
public static string EscapeText(string textToEscape)
{
string backslashesEscaped = textToEscape.Replace(@"\",
@"\\");
string backslashAndSingleQuoteEscaped =
backslashesEscaped.Replace(@"'", @"\'");

return backslashAndSingleQuoteEscaped;
}

and say

sQuery = String.Format("INSERT INTO myTable (Header, Body) VALUES
('{0}','{1}')", EscapeText(sHeader), EscapeText(sBody));

or, even better, use parameters when you're building your SQL command.
If you're using Odbc, for example, look at the OdbcParameter class.

Parameters are the preferred way to do things, because then you can
never forget to escape strings and you're not subject to SQL injection
attacks as a result. However, be forewarned that there is a bug in
..NET's ODBC support that makes decimal parameters blow up, so you have
to insert decimal values directly into the query string as you're doing.
 
Hi,

To avoid this issue you need to pass values as a parameters, not to
concatenate SQL statement. In this case provider will handle single quotes
properly regardless how many of them are in a value(s). It also handles
other special characters
 
It's better to use parametere or replace the single quotes
with double quotes to avoid SQL-injection.

HTH

Elton Wang
(e-mail address removed)
 
If you do it properly and use use either a parametised SQLCommand or
parametised OleDbCommand object than you won't have this issue.
 
Back
Top