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.ConnectionStrings[Utils.G etConnectionString].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();

Re: escaping data for update query

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" wrote in message
news:1180917930.810194.38600@q75g2000hsh.googlegroups.com...
>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.ConnectionStrings[Utils.G etConnectionString].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();
>

Re: escaping data for update query

oops sorry!

On Jun 3, 8:18 pm, "Dan Guzman"
wrote:
> 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" wrote in message
>
> news:1180917930.810194.38600@q75g2000hsh.googlegroups.com...
>
> >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.ConnectionStrings[Utils.G etConnectionStr ing].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();