Avoiding SQL Injection with FormView controls

Discussion in 'ASP .Net' started by Cirene, May 10, 2008.

  1. Cirene

    Cirene Guest

    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, May 10, 2008
    #1
    1. Advertising

  2. Cirene

    Lloyd Sheen Guest

    "Cirene" <> wrote in message
    news:...
    >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
     
    Lloyd Sheen, May 10, 2008
    #2
    1. Advertising

  3. Cirene

    Alex Meleta Guest

    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>
     
    Alex Meleta, May 10, 2008
    #3
  4. 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???
    >
    >
    >
     
    Milosz Skalecki [MCAD], May 10, 2008
    #4
  5. Cirene

    jaems Guest

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

    Jaez


    "Cirene" <> wrote in message
    news:...
    >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, May 11, 2008
    #5
  6. Cirene

    Cirene Guest

    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]" <> wrote in message
    news:...
    > 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???
    >>
    >>
    >>
     
    Cirene, May 12, 2008
    #6
  7. Cirene

    Paul Shapiro Guest

    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" <> wrote in message
    news:ipJVj.10905$2...
    >
    > So how exactly does using parameters prevent injection - ie what does the
    > code in command.Parameters.Add do?
    >
    > Jaez
    >
    >
    > "Cirene" <> wrote in message
    > news:...
    >>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, May 12, 2008
    #7
  8. 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]" <> wrote in message
    > news:...
    > > 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???
    > >>
    > >>
    > >>

    >
    >
    >
     
    Milosz Skalecki [MCAD], May 12, 2008
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. poppy

    SQL Injection Attacks

    poppy, Nov 2, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    413
    Scott Allen
    Nov 3, 2004
  2. Darrel
    Replies:
    9
    Views:
    3,638
    Steve C. Orr [MVP, MCSD]
    Nov 11, 2004
  3. MattB

    SQL injection

    MattB, Mar 30, 2005, in forum: ASP .Net
    Replies:
    10
    Views:
    710
    Peter Blum
    Mar 31, 2005
  4. Ranginald
    Replies:
    10
    Views:
    880
    Ranginald
    Apr 27, 2006
  5. =?Utf-8?B?c3M=?=

    sample validation code for sql injection attact

    =?Utf-8?B?c3M=?=, May 5, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    638
    =?UTF-8?B?R8O2cmFuIEFuZGVyc3Nvbg==?=
    May 9, 2006
Loading...

Share This Page