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.GetConnection
String].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();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> 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=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=[/vb
col]
tr=ADing].ToString());[vbcol=seagreen]
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 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 =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||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.

> 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?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.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.GetConnecti
onString].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();
>