Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 9 of 9
  1. #1
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    14 times
    A good read on a serious potential type of security hole when working with *SQL:

    http://www.4guysfromrolla.com/webtech/061902-1.shtml
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  2.  

  3. #2
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,402
    Member #
    425
    Liked
    2747 times
    A code snippet I'd like to add

    The following is a function I have created and use for the prevention of SQL injections as well as to allow information containing single or double quotes to be safely stored in a database:

    Code:
    function SQLComply (Term)
    
         Term = Trim (Term)
         if Term <> "" then
              Term = Replace (Term, "", """")
              Term = Replace (Term, "'", "''")
              ' Next two lines are included to combat double quotes being stored by mistake.
              Term = Replace (Term, """""""", """")
              Term = Replace (Term, "''''", "''")
         end if
         SQLComply = Term
    
    end function
    When you are retrieving form input, call it as follows:

    Form_Variable = SQLComply (Request.Form (Form_Element))

    This code will work both for SQL Server and for Access. (I've tested it on both.)
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,730
    Member #
    819
    Liked
    201 times
    An even better way to prevent these problems is to use Stored Procedures with ADO Command objects rather than executing string SQL directly in your code.

    Here's a simple example:

    Database Stuff
    Code:
    CREATE DATABASE test
    GO
    
    USE test
    GO
    
    CREATE TABLE person
    (
      id    INT NOT NULL IDENTITY(1,1),
      name  VARCHAR(255) NOT NULL
    )
    GO
    
    ALTER TABLE person
      ADD PRIMARY KEY CLUSTERED (id)
    GO
    
    CREATE PROCEDURE addPerson
    (
    	@id INT OUTPUT,
    	@name VARCHAR(255) = ''
    )
    AS
    	SET NOCOUNT ON
    	INSERT INTO person(name) VALUES(@name)
    	SET @id = @@identity
    	SET NOCOUNT OFF
    GO
    VB Stuff
    Code:
    <%
        Dim oConn
        Dim oCmd
        Set oConn = Server.CreateObject("ADODB.Connection")
        Set oCmd = Server.CreateObject("ADODB.Command")
        oConn.Open "ConnectionString"
        Set oCmd.ActiveConnection = oConn
        oCmd.CommandType = adCmdStoredProc
        oCmd.CommandText = "addPerson"
        oCmd.Parameters.Refresh
        oCmd.Parameters.Item("@name").Value = "Name"
        oCmd.Execute
        Response.Write("id - " & oCmd.Parameters.Item("@id").Value
        oConn.Close
        Set oCmd = Nothing
        Set oConn = Nothing
    %>
    Ugh... too much typing !!!

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,730
    Member #
    819
    Liked
    201 times
    The reason Stored Procedures with ADO Commands are better is that they will only execute one sql command, unless they are DESIGNED to to otherwise (i.e. through dynamic queries built with EXEC).

    Additionally, input values are restricted at the SQL Server by parameter type and length. This prevents all forms of SQL injection attacks.

    If your parameter is a varchar, and someone places SQL content into it, it will simply insert that content into the corresponding column, etc.

    If you're doing something like this:
    Code:
    sql = "SELECT * FROM articles WHERE id = " & Request("id")
    You're opening yourself up to someone supplying a Request value of "0; DELETE FROM articles", which would result in

    Code:
    sql = "SELECT * FROM articles WHERE id = 0; DELETE FROM articles"
    Using Stored Procedures, you would have something like this:
    Code:
    CREATE PROCEDURE getArticles
    (
        @id INT
    )
    AS
        SELECT * FROM articles WHERE id = @id
    GO
    it would never even execute, because "0; DELETE FROM articles" is not an int, and wouldn't be accepted by the procedure.

    Furthermore, even if @id were a varchar, it would utilize the entire "0; DELETE FROM articles" string for comparison rather than breaking it up into a separate query.

    Hope this explains a little better

  6. #5
    Senior Member kleptos's Avatar
    Join Date
    May 2002
    Location
    The Dark Carnival
    Posts
    243
    Member #
    110
    Too bad MySql cant handle procedures.....
    .: Kleptos :.

  7. #6

  8. #7
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,650
    Member #
    1234
    Liked
    137 times
    DUDE, that post was from 6.5 years ago. Things change!
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com
    Founder/Creator/Admin of ZE SECRET PROJECT!
    New Game: http://www.wats.it
    DWM Software Co. - software . mobile . database . hosting . design

  9. #8
    Senior Member
    Join Date
    Dec 2009
    Location
    Chicago, IL
    Posts
    114
    Member #
    20579
    Liked
    2 times
    didn't see the date, sorry I'm new here.

  10. #9
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,730
    Member #
    819
    Liked
    201 times
    LOL

    Procedures are still iffy with PHP/MySQL. Not sure if they fixed it after 5.2.8, but php had a problem where if a procedure returned a resultset, PHP wouldn't load it.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:21 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2017 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com