escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

try
{
int result = command.ExecuteNonQuery();

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

connection.Close();If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"eggie5" <eggie5@.gmail.comwrote in message
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

>I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
>
I'm not using any fancy ado.net objects:
>
string sql= [whatever the user passes in]
>
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
>
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
>
>
try
{
int result = command.ExecuteNonQuery();
>
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
>
connection.Close();
>

|||oops sorry!

On Jun 3, 8:18 pm, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
>
This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"eggie5" <egg...@.gmail.comwrote in message
>
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...
>

Quote:

Originally Posted by

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?

>

Quote:

Originally Posted by

I'm not using any fancy ado.net objects:

>

Quote:

Originally Posted by

string sql= [whatever the user passes in]

>

Quote:

Originally Posted by

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStr ing].ToString());
connection.Open();

>

Quote:

Originally Posted by

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

>

Quote:

Originally Posted by

try
{
int result = command.ExecuteNonQuery();

>

Quote:

Originally Posted by

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

>

Quote:

Originally Posted by

connection.Close();