Post reply

How NOT to use parameters to avoid SQL injection

  • November 26, 2008 at 9:33 am

    #124542

    I was asked to peek into a simple web project, which they were upgrading to avoid sql injection attacks.

    I was happy to hear they switched to using stored procedures and adding a parameters to avoid SQL Injections, until I saw this procedure; figured some of you might like a laugh before the holidays.

    [font="Courier New"]

    CREATE PROCEDURE pr_Search  @text VARCHAR(1000)

    AS

    BEGIN

      DECLARE @sql VARCHAR(MAX)

      SET @sql = 'SELECT * FROM VW_TERMS WHERE TERM=@text ORDER BY TERMID'

      SET @sql = REPLACE(@sql,'@text',@text)

      EXEC(@sql)

    END[/font]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 26, 2008 at 9:36 am

    #903883

    LOL. Well... they DID use a parameter.:hehe:

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • November 26, 2008 at 9:54 am

    #903899

    <Sigh>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 26, 2008 at 11:15 am

    #903944

    Sweet!

    I'll bet it makes programming easy.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 26, 2008 at 11:29 am

    #903948

    Heh. Well, the good news is: that one looks easy to fix. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply