Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updated SQL Injection


Updated SQL Injection

Author
Message
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/updatedsqlinjection.asp
N Cook
N Cook
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 22
Good article. Thanks.
Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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
mark hutchinson
mark hutchinson
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 446

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).





R M Buda
R M Buda
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 300
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.
saleem-200718
saleem-200718
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

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!


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

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!


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

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!


Frances L
Frances L
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search