Avoiding SQL Injection with FormView controls

[Cirene]

I am using formview controls to insert/update info into my tables.

I'm worried about SQL injection.

How do you recommend I overcome this issue?

In the past I've called a custom cleanup routine like this:
Public Function CleanUpText(ByVal TextToClean As String) As String
TextToClean = TextToClean.Replace(";", ".")
TextToClean = TextToClean.Replace("*", " ")
TextToClean = TextToClean.Replace("=", " ")
TextToClean = TextToClean.Replace("'", " ")
TextToClean = TextToClean.Replace("""", " ")
TextToClean = TextToClean.Replace("1=1", " ")
TextToClean = TextToClean.Replace(">", " ")
TextToClean = TextToClean.Replace("<", " ")
TextToClean = TextToClean.Replace("<>", " ")
TextToClean = TextToClean.Replace("null", " ")
TextToClean = TextToClean.Replace("delete", "_delete")
TextToClean = TextToClean.Replace("remove", "_remove")
TextToClean = TextToClean.Replace("copy", "_copy")
TextToClean = TextToClean.Replace("table", "_table")
TextToClean = TextToClean.Replace("drop", "_drop")
TextToClean = TextToClean.Replace("select", "_select")
TextToClean = TextToClean.Replace("user", "_user")
TextToClean = TextToClean.Replace("create", "_create")

Return TextToClean
End Function

What do you think of this method? Is it cludgey???

 

[Lloyd Sheen]

I am using formview controls to insert/update info into my tables.

I'm worried about SQL injection.

How do you recommend I overcome this issue?

In the past I've called a custom cleanup routine like this:
Public Function CleanUpText(ByVal TextToClean As String) As String
TextToClean = TextToClean.Replace(";", ".")
TextToClean = TextToClean.Replace("*", " ")
TextToClean = TextToClean.Replace("=", " ")
TextToClean = TextToClean.Replace("'", " ")
TextToClean = TextToClean.Replace("""", " ")
TextToClean = TextToClean.Replace("1=1", " ")
TextToClean = TextToClean.Replace(">", " ")
TextToClean = TextToClean.Replace("<", " ")
TextToClean = TextToClean.Replace("<>", " ")
TextToClean = TextToClean.Replace("null", " ")
TextToClean = TextToClean.Replace("delete", "_delete")
TextToClean = TextToClean.Replace("remove", "_remove")
TextToClean = TextToClean.Replace("copy", "_copy")
TextToClean = TextToClean.Replace("table", "_table")
TextToClean = TextToClean.Replace("drop", "_drop")
TextToClean = TextToClean.Replace("select", "_select")
TextToClean = TextToClean.Replace("user", "_user")
TextToClean = TextToClean.Replace("create", "_create")

Return TextToClean
End Function

What do you think of this method? Is it cludgey???

If you want to avoid SQL injection use parameters.

LS

 

[Alex Meleta]

Hi Cirene,

There's how to prevent it - http://msdn.microsoft.com/en-us/library/ms998271.aspx

And with agreement of Lloyd, what is your function for?

Regards, Alex



C> I am using formview controls to insert/update info into my tables.
C>
C> I'm worried about SQL injection.
C>
C> How do you recommend I overcome this issue?
C>
C> In the past I've called a custom cleanup routine like this:
C> Public Function CleanUpText(ByVal TextToClean As String) As
C> String
C> TextToClean = TextToClean.Replace(";", ".")
C> TextToClean = TextToClean.Replace("*", " ")
C> TextToClean = TextToClean.Replace("=", " ")
C> TextToClean = TextToClean.Replace("'", " ")
C> TextToClean = TextToClean.Replace("""", " ")
C> TextToClean = TextToClean.Replace("1=1", " ")
C> TextToClean = TextToClean.Replace(">", " ")
C> TextToClean = TextToClean.Replace("<", " ")
C> TextToClean = TextToClean.Replace("<>", " ")
C> TextToClean = TextToClean.Replace("null", " ")
C> TextToClean = TextToClean.Replace("delete", "_delete")
C> TextToClean = TextToClean.Replace("remove", "_remove")
C> TextToClean = TextToClean.Replace("copy", "_copy")
C> TextToClean = TextToClean.Replace("table", "_table")
C> TextToClean = TextToClean.Replace("drop", "_drop")
C> TextToClean = TextToClean.Replace("select", "_select")
C> TextToClean = TextToClean.Replace("user", "_user")
C> TextToClean = TextToClean.Replace("create", "_create")
C> Return TextToClean
C> End Function
C> What do you think of this method? Is it cludgey???
C>

 

[Milosz Skalecki [MCAD]]

Hi Cirene,

You don't need to waste your time writing "CleanUpText" like methods, use
parameters instead as they take care of sql injection internally (one of many
adventages of using parameters):

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand command = new SqlCommand("SELECT * FROM Table WHERE Id
= @Id", connection))
{
command.Parameters.Add("@Id", SqlDbType.Int).Value = 1;
connection.Open();

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int value1 = (int) reader["Column1"];
// etc.
}
}
}
}

HTH

 

[jaems]

So how exactly does using parameters prevent injection - ie what does the
code in command.Parameters.Add do?

Jaez

 

[Cirene]

Is the "automatic" way (using the GUI) just as safe as stored proc, or
should I validate extra to be safe? (Ex: Drop gridview on form, create SQL
Data Source wtih the wizard, etc...)

Hi Cirene,

You don't need to waste your time writing "CleanUpText" like methods, use
parameters instead as they take care of sql injection internally (one of
many
adventages of using parameters):

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand command = new SqlCommand("SELECT * FROM Table WHERE
Id
= @Id", connection))
{
command.Parameters.Add("@Id", SqlDbType.Int).Value = 1;
connection.Open();

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int value1 = (int) reader["Column1"];
// etc.
}
}
}
}

HTH
--
Milosz

I am using formview controls to insert/update info into my tables.

I'm worried about SQL injection.

How do you recommend I overcome this issue?

In the past I've called a custom cleanup routine like this:
Public Function CleanUpText(ByVal TextToClean As String) As String
TextToClean = TextToClean.Replace(";", ".")
TextToClean = TextToClean.Replace("*", " ")
TextToClean = TextToClean.Replace("=", " ")
TextToClean = TextToClean.Replace("'", " ")
TextToClean = TextToClean.Replace("""", " ")
TextToClean = TextToClean.Replace("1=1", " ")
TextToClean = TextToClean.Replace(">", " ")
TextToClean = TextToClean.Replace("<", " ")
TextToClean = TextToClean.Replace("<>", " ")
TextToClean = TextToClean.Replace("null", " ")
TextToClean = TextToClean.Replace("delete", "_delete")
TextToClean = TextToClean.Replace("remove", "_remove")
TextToClean = TextToClean.Replace("copy", "_copy")
TextToClean = TextToClean.Replace("table", "_table")
TextToClean = TextToClean.Replace("drop", "_drop")
TextToClean = TextToClean.Replace("select", "_select")
TextToClean = TextToClean.Replace("user", "_user")
TextToClean = TextToClean.Replace("create", "_create")

Return TextToClean
End Function

What do you think of this method? Is it cludgey???

 

[Paul Shapiro]

Parameters protect against sql injection because the parameter value is
passed to the sql server. The server uses the parameter value directly when
processing the query, and does not just substitute the parameter into the
sql statement text. Data values that would enable sql injection will instead
either cause query errors or where clause matching failure.

 

[Milosz Skalecki [MCAD]]

Hi there,

Usually you use gridview, and formview in conjunction with SqlDataSource
which employs Parameters internally.

Regards
--
Milosz

Is the "automatic" way (using the GUI) just as safe as stored proc, or
should I validate extra to be safe? (Ex: Drop gridview on form, create SQL
Data Source wtih the wizard, etc...)

Hi Cirene,

You don't need to waste your time writing "CleanUpText" like methods, use
parameters instead as they take care of sql injection internally (one of
many
adventages of using parameters):

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand command = new SqlCommand("SELECT * FROM Table WHERE
Id
= @Id", connection))
{
command.Parameters.Add("@Id", SqlDbType.Int).Value = 1;
connection.Open();

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int value1 = (int) reader["Column1"];
// etc.
}
}
}
}

HTH
--
Milosz

I am using formview controls to insert/update info into my tables.

I'm worried about SQL injection.

How do you recommend I overcome this issue?

In the past I've called a custom cleanup routine like this:
Public Function CleanUpText(ByVal TextToClean As String) As String
TextToClean = TextToClean.Replace(";", ".")
TextToClean = TextToClean.Replace("*", " ")
TextToClean = TextToClean.Replace("=", " ")
TextToClean = TextToClean.Replace("'", " ")
TextToClean = TextToClean.Replace("""", " ")
TextToClean = TextToClean.Replace("1=1", " ")
TextToClean = TextToClean.Replace(">", " ")
TextToClean = TextToClean.Replace("<", " ")
TextToClean = TextToClean.Replace("<>", " ")
TextToClean = TextToClean.Replace("null", " ")
TextToClean = TextToClean.Replace("delete", "_delete")
TextToClean = TextToClean.Replace("remove", "_remove")
TextToClean = TextToClean.Replace("copy", "_copy")
TextToClean = TextToClean.Replace("table", "_table")
TextToClean = TextToClean.Replace("drop", "_drop")
TextToClean = TextToClean.Replace("select", "_select")
TextToClean = TextToClean.Replace("user", "_user")
TextToClean = TextToClean.Replace("create", "_create")

Return TextToClean
End Function

What do you think of this method? Is it cludgey???