sql injection

Discussion in 'ASP General' started by gdp, Jan 22, 2004.

  1. gdp

    gdp Guest

    hi...when guarding against sql injection attack from modified form or
    querystring variables is it enough to strip out just apostrophes...if the
    variable USERNAME is the name of a text box passed to a script is the below
    always safe...

    q1="select * from TABLENAME where username='" &
    trim(replace(request("USERNAME"),"'","''")) & "'"

    thankyou for all help given

    regards

    gdp
     
    gdp, Jan 22, 2004
    #1
    1. Advertising

  2. In my opinion, this should pretty much solve the common SQL injection
    attacks. The following document seems to agree, plus has extensive coverage
    on the topic:

    http://www.nextgenss.com/papers/advanced_sql_injection.pdf

    --
    Manohar Kamath
    Editor, .netBooks
    www.dotnetbooks.com


    "gdp" <> wrote in message
    news:rqXPb.732$...
    > hi...when guarding against sql injection attack from modified form or
    > querystring variables is it enough to strip out just apostrophes...if the
    > variable USERNAME is the name of a text box passed to a script is the

    below
    > always safe...
    >
    > q1="select * from TABLENAME where username='" &
    > trim(replace(request("USERNAME"),"'","''")) & "'"
    >
    > thankyou for all help given
    >
    > regards
    >
    > gdp
    >
    >
     
    Manohar Kamath [MVP], Jan 22, 2004
    #2
    1. Advertising

  3. Not necessarily, there are other obscure scenarios, usually surrounding
    techniques you shouldn't be using anyway. Imagine this:

    CREATE PROCEDURE dbo.getfoo
    @tablename VARCHAR(32)
    AS
    BEGIN
    SET NOCOUNT ON
    EXEC('SELECT * FROM '+@tablename)
    END
    GO

    Then from ASP:

    <%
    set rs = conn.execute("EXEC dbo.foo '" &
    request.querystring("tablename") & "'")
    %>

    Then hit this with:

    http://www.yoursite.com/yourpage.asp?tablename=foo;TRUNCATE TABLE foo

    No apostrophes to replace, so even if you did your little replace method,
    the table would still get truncated. This is certainly something that a
    knowledgeable user could try, if you allow them to know the names of tables
    (which they have no real need to know) and allow them to enter such names
    unchecked.

    Of course you could prevent this as follows:

    CREATE PROCEDURE dbo.getfoo
    @tablename VARCHAR(32)
    AS
    BEGIN
    SET NOCOUNT ON
    IF OBJECT_ID(@tablename) IS NOT NULL
    EXEC('SELECT * FROM '+@tablename)
    END
    GO

    The main thing is to avoid potential scenarios where a string can be
    executed unchecked and un-type-verified. See
    http://www.sommarskog.se/dynamic_sql.html for other perils of using dynamic
    SQL in a stored procedure.

    Then, avoid dynamic SQL in your execute string in ASP as well, as much as
    possible. For anything remaining, the replace of ' should be sufficient.

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "gdp" <> wrote in message
    news:rqXPb.732$...
    > hi...when guarding against sql injection attack from modified form or
    > querystring variables is it enough to strip out just apostrophes...if the
    > variable USERNAME is the name of a text box passed to a script is the

    below
    > always safe...
    >
    > q1="select * from TABLENAME where username='" &
    > trim(replace(request("USERNAME"),"'","''")) & "'"
    >
    > thankyou for all help given
    >
    > regards
    >
    > gdp
    >
    >
     
    Aaron Bertrand - MVP, Jan 22, 2004
    #3
  4. gdp

    Ray at Guest

    Ray at, Jan 23, 2004
    #4
  5. gdp

    Jeff Cochran Guest

    On Thu, 22 Jan 2004 15:50:21 -0600, "Manohar Kamath [MVP]"
    <> wrote:

    >In my opinion, this should pretty much solve the common SQL injection
    >attacks. The following document seems to agree, plus has extensive coverage
    >on the topic:
    >
    >http://www.nextgenss.com/papers/advanced_sql_injection.pdf


    Now this is a great resource. Thanks.

    Jeff
     
    Jeff Cochran, Jan 23, 2004
    #5
  6. gdp

    Bob Barrows Guest

    Manohar Kamath [MVP] wrote:
    > In my opinion, this should pretty much solve the common SQL injection
    > attacks. The following document seems to agree, ...


    It does? To me, it seems to be saying that this method (escaping quotes) can
    be defeated.

    IMO, based on what I've read, the most foolproof way to avoid sql injection
    is to avoid dynamic sql, whether that dynamic sql is created in asp code or
    in a SQL Server stored procedure (sp_ExecuteSQL can be used to parameterize
    dynamic sql statements in stored procedures). Passing parameters correctly
    to a stored procedure that does not use dynamic sql will prevent all the
    examples of injection I've seen from working. The pdf seems to agree with
    this.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Jan 23, 2004
    #6
    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:
    418
    Scott Allen
    Nov 3, 2004
  2. Darrel
    Replies:
    9
    Views:
    3,648
    Steve C. Orr [MVP, MCSD]
    Nov 11, 2004
  3. MattB

    SQL injection

    MattB, Mar 30, 2005, in forum: ASP .Net
    Replies:
    10
    Views:
    713
    Peter Blum
    Mar 31, 2005
  4. Ranginald
    Replies:
    10
    Views:
    887
    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:
    645
    =?UTF-8?B?R8O2cmFuIEFuZGVyc3Nvbg==?=
    May 9, 2006
Loading...

Share This Page