Avoiding SQL Injection with FormView controls

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

Lloyd Sheen

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
 
A

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

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
 
J

jaems

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

Jaez
 
C

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

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

Milosz Skalecki [MCAD]

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Defending against SQL injection.... 4
SQL Server and .NET Interview questions free download 0
Download the JAVA , .NET and SQL Server interview with answers 0
Download the JAVA , .NET and SQL Server interview PDF 0
Asp.net Important Topics. 0
comp.lang.c Answers to Frequently Asked Questions (FAQ List) 15
comp.lang.c Answers to Frequently Asked Questions (FAQ List) 1

Members online

Total: 78 (members: 2, guests: 76)
Robots: 441

Forum statistics

Threads
473,968
Messages
2,570,153
Members
46,701
Latest member
XavierQ83

Latest Threads

Top