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!

How about prefixing the apostrophy with the "\" escape character, in other
words "\'" ?

Show quote

Hide quote

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

Zak wrote:

Show quote

Hide quote

How about prefixing the apostrophy with the "\" escape character, in other
words "\'" ?

Show quote

Hide quote

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

Show quote

Hide quote

It's better to use parametere or replace the single quotes
with double quotes to avoid SQL-injection.

HTH

Elton Wang
elton_w***@hotmail.com

way I've tried
apostrophy) in either

Show quote

Hide quote

If you do it properly and use use either a parametised SQLCommand or
parametised OleDbCommand object than you won't have this issue.

Show quote

Hide quote

Yes i think too  that parameterize an oledbcomamand is the solution  is
the solutions.
I ve put a sample code here :
http://oraclevsmicrosoft.blogs­pot.com/2005/03/quotes-paramet­ers.html



hope this helps


Marc Boizeau


http://oraclevsmicrosoft.blogs­pot.com

A question about closing the SqlDataReader
Can't find stored procedure
Excel and ADO.NET
return OleDbDataReader from static function
ado.net create a table