Avoiding SQL Injection with FormView controls

  • Thread starter Thread starter Cirene
  • Start date Start date
C

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???
 
Cirene said:
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???
Click to expand...

If you want to avoid SQL injection use parameters.

LS
 
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>
 
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
 
So how exactly does using parameters prevent injection - ie what does the
code in command.Parameters.Add do?

Jaez
 
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...)

Milosz Skalecki said:
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


Cirene said:
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???
Click to expand...
Click to expand...
 
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.
 
Hi there,

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

Regards
--
Milosz


Cirene said:
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...)

Milosz Skalecki said:
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


Cirene said:
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???
Click to expand...
Click to expand...
Click to expand...
 
Back
Top