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]

"Cirene" <ci****@nowhere.comwrote in message
news:eL*************@TK2MSFTNGP02.phx.gbl...

>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

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

[=?Utf-8?B?TWlsb3N6IFNrYWxlY2tpIFtNQ0FEXQ==?=]

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" wrote:

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???

[jaems]

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

Jaez
"Cirene" <ci****@nowhere.comwrote in message
news:eL*************@TK2MSFTNGP02.phx.gbl...

>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]

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 [MCAD]" <mi*****@DONTLIKESPAMwp.plwrote in message
news:83**********************************@microsof t.com...

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" wrote:

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

"jaems" <ja***@ntlworld.comwrote in message
news:ip*****************@newsfe20.ams2...

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

Jaez
"Cirene" <ci****@nowhere.comwrote in message
news:eL*************@TK2MSFTNGP02.phx.gbl...

>>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???

[=?Utf-8?B?TWlsb3N6IFNrYWxlY2tpIFtNQ0FEXQ==?=]

Hi there,

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

Regards
--
Milosz
"Cirene" wrote:

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 [MCAD]" <mi*****@DONTLIKESPAMwp.plwrote in message
news:83**********************************@microsof t.com...

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" wrote:

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???