Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Updated SQL Injection Expand / Collapse
Author
Message
Posted Monday, August 29, 2005 9:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/updatedsqlinjection.asp
Post #215144
Posted Wednesday, September 21, 2005 6:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 5:54 PM
Points: 99, Visits: 22
Good article. Thanks.
Post #222020
Posted Wednesday, September 21, 2005 7:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 295, Visits: 278
It should be noted (and probably has been in the past) that stored procedure calls need to be parameterized as well. Use the appropriate Command class for the target DBMS such as SqlCommand, OracleCommand, etc. and build it with Parameters. The best and easiest way to perform data access is to use the Microsoft Enterprise Library. It takes a few minutes to learn but the payoff is immense. Reduced development time, simpler to adhere to best practices, and increased security (connection information can be encrypted) are just a couple benefits of using this free package.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #222044
Posted Wednesday, September 21, 2005 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 3:34 PM
Points: 40, Visits: 345

How about a .Net code example of the validation to which you allude (username character limits)?

It would been more helpful if you mentioned the use of .Net validators and gave a code example.  For instance, you might include a validator that requires a password.

How about a TSQL code example of the validation to which you allude (username character limits)?

Excellent example of a reason for restricting access to only stored procs and not tables. kudos.

You might hash both the username and password field data and send it to the stored proc or at least the password field data.

If you are concerned about man-in-the-middle attacks/sniffers, you might send some special data back from the stored proc, such as the hash of username & password & datetime (fuzzy).

MD5 is no longer considered a trustworthy hash by NIST:
http://www.codeproject.com/useritems/GoodbyeMD5.asp
a better recommendation is SHA-256 (or stronger).




Post #222058
Posted Wednesday, September 21, 2005 7:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:02 AM
Points: 146, Visits: 238
I am surprised there is so much discussion of SQL injection when a simple coding technique can always be used to avoid it.


The most important point Michael makes is "be certain to replace single quotes (') with escaped single quotes ('') in string constants". So long as you do this you can safely use dynamic SQL.

This escaping should always be done to avoid the resulting SQL syntax errors that would usually occur when someone enters a single quote in a text field. Not doing this is simply sloppy coding. An alternative Michael mentions is sp_executesql.

The SQL injection scares remind me of the Y2K scares. There are potential problems, but these are easily solved by decent coding practice. So long as user input is confined to variable values by the escaping of single quotes or sp_executesql, then dynamic SQL is safe and often very convenient.
Post #222090
Posted Wednesday, September 21, 2005 8:06 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 25, 2011 9:51 AM
Points: 5, Visits: 17

This is a great artical and i have seen many describing the threat of SQL injection.

I would recommend to give the solution also using which we can avoid the Injection threat.

Post #222104
Posted Wednesday, September 21, 2005 10:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

Thanks for the feedback.  I had read that MD5 collisions were becoming more common, but hadn't realized that NIST had declared it unsecure.  SHA-256 and SHA-512 are good alternatives for now.

The .NET code for validation can be as simple as a FOR...NEXT loop comparing your string character by character, a Regular Expression or it can take advantage of the ASP Validators.  Here's a very simple example of a FOR...NEXT loop to validate a username consists of only the characters "A" - "Z" and "a" - "z", as I alluded to:

Function ValidUser(s As String) As Boolean
    Dim f As Boolean = True
    s = s.ToUpper()
    For i = 0 To s.Length - 1
        If (s.Substring(i, 1) < "A" OrElse s.Substring(i, 1) > "Z") Then
            f = False
        EndIf
    Next
End Function

.NET has several built-in validators that can be used also to validate input to various degrees; I assume you're talking about the RequiredFieldValidator in your post.  To use, just drag the RequiredFieldValidator onto the form next to the Password text box, and change the properties as follows:

1.  Set .ControlToValidate to Password.
2.  Set the .Text property to a descriptive message, such as "Password Required".

T-SQL validation could also be performed using a loop, as alluded to.  Here's a UDF that can be called from within a SQL SP:

CREATE FUNCTION dbo.udf_ValidUserName(@s VARCHAR(255))
RETURNS CHAR(1)
AS
BEGIN
    DECLARE @i INTEGER
    SET @i = 1
    DECLARE @result CHAR(1)
    SET @result = 'T'
    SET @s = UPPER(@s)
    WHILE @i <= LEN(@s)
    BEGIN
        IF (SUBSTRING(@s, @i, 1) < 'A' OR
            SUBSTRING(@s, @i, 1) > 'Z')
        BEGIN
            SET @result = 'F'
        END
        SET @i = @i + 1
    END
    RETURN @result
END

Thanks!

 

Post #222251
Posted Wednesday, September 21, 2005 10:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

Thanks for the feedback!

Keep in mind though that even though in the example I gave all data is passed as VARCHAR's which need to be quoted, not all data passed via SQL Statements will be quoted.  For instance:

SELECT * FROM Table1 WHERE IntegerID = 19823

If this were dynamically constructed, like this:

SqlString = "SELECT * FROM Table1 WHERE IntegerID = " & InputID.Text

The user can inject SQL commands with no regard for quotes.  The safest route is to always use .NET parameterized queries and sp_executesql.

But probably an even bigger problem that I've seen is DBA's and developers giving the ASPNET user (and other users) far too many rights on the server and in the databases themselves.  It's often a lot easier just to grant a user SA rights than to figure out exactly what rights they actually need.

Thanks!

Post #222253
Posted Wednesday, September 21, 2005 10:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

Thanks for the feedback.

To avoid SQL injection:  always validate user input, use parameterized queries and sp_executesql, and limit the permissions of the ASPNET user on the server and within the database.

There are many ways to do these things, and you should choose the method that works best for you.  I've posted a couple of code samples above in VB and T-SQL.

In addition, limit the amount of data sent across the wire and hash or encrypt sensitive data on the wire, and in your database, when possible.  Again there are many methods of doing this, including AES Encryption, SHA-256/SHA-512 hashing, etc.

Thanks!

Post #222259
Posted Thursday, September 21, 2006 6:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 18, 2011 6:11 PM
Points: 355, Visits: 299

Thank you for the article. It is eye opening for me. Our company user name first letter of the first name and last name + one random number they generate. so I think the validation should be just special character. Also I do not understand why you said replace single quota with double quota.

SELECT * FROM [Login] where [User] = "test";

It will not returen any data. It have Invalid column name 'test' error.

 

Post #310212
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse