Updated SQL Injection
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/updatedsqlinjection.asp
-
Good article. Thanks.
-
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.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
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).
-
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.
-
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.
-
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-2 = 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!
-
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!
-
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!
-
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.
-
-
To "escape" single quotes in SQL Server, you have to double them up meaning you put two single quotes back-to-back:
SELECT * FROM [Login] where [User] = 'Frances'' test of SQL''s escaped quotes';
Notice that the escaped quotes in the example are two separate single quotes, not one "double" quotation mark.
Thanks
-
Wonderful article! Thank you so much!
-
Oooooo-kaaay! I had just written up a long reply here with lots of important information, and went to HTML view to edit some of the generated code (did you know that this Forum software inserts “ ”s whenever you go back and edit already-written text in the Design view?), then clicked Preview, and voila! Bye-bye message! Empty editor box! Bad Forum software! Bad!!
Well, I’m not typing the whole thing again. I have work to do. So, you’ll just have to settle for this one bit of dangerously bad advice that I noticed in your article:
“Validation can be performed server-side, client-side, or even both!” — Bzzzzzt! Wrong answer!
“Validation must be performed server-side. You may (and should also add client-side validation to speed up the user experience and save server round-trips for those users whose browsers support it, but you must always back it up with equivalent server-side validation. It’s trivially easy for hackers to bypass client-side validation, either by faking GETs or even POSTs, bypassing your Hidden and Read-Only fields, stuffing non-listed values into your drop-down menus and radio button lists (all of those can be done with a very common Firefox Extension, for instance), or even by simply turning off JavaScript!” — Much better.
ASP.NET 2’s built-in Validators can add the appropriate client-side code for you, and the CustomValidator can allow you to code a JavaScript client-side custom validator linked to it. These will automatically be rendered to browsers known to support the appropriate level of JavaScript and either Microsoft or W3C DOM.
So, for instance, if you want to prevent anything except alphanumerics from being entered into the Login Name field, as well as limiting the number of characters (you should always do this anyway, both to prevent overflowing the database field [“Text or binary data would be truncated.” server error] and to shorten the amount of SQL code that a hacker could inject should he manage to get past all of your protections), you can do this:
User Name: <asp:TextBox runat="server" ID="tbUserName" Columns=16 MaxLength=16 /> <asp:RequiredFieldValidator runat="server" ID="rfvUserName" ControlToValidate="tbUserName" EnableClientScript="true" Text="×" ErrorMessage="User Name is REQUIRED!" /> <asp:RegularExpressionValidator runat="server" ID="rgxvUserName" ControlToValidate="tbUserName" ValidationExpression="^\w{4,16}$" EnableClientScript="true" Text="×" ErrorMessage="User Name must be between 4 and 16 alphanumeric (or underscore) characters long." />...
<asp:ValidationSummary runat="server" ID="vsLogin" DisplayMode="BulletList" HeaderText="Please correct the following and try again:" EnableClientScript="true" />
Note that not a single line of VB.NET or C#.NET code is required! This is all done in ASP.NET 2 tags! Two Validators do all the work: RequiredFieldValidator to make sure that something was entered, and RegularExpressionValidator to make sure that it was within the proper length range and consisted entirely of upper or lower-case letters, digits, and underscores. No other characters (including spaces) accepted, let alone such SQL Injection-dangerous ones as single quotes, hyphens, or semicolons.
The other thing I went on at some length about before this lousy Forum ate my post was Macrodobe Dreamweaver. Suffice it to say that the ASP and ASP.NET 1.1 (DW doesn’t even support ASP.NET 2 except for hand-coding) code generated by any version of Dreamweaver is horrendously inefficient, but that if any of you used it in the past to get started with ASP or ASP.NET and you still have any sites out there that use it, you must upgrade to DW 8.0.2 and rebuild your ASP Recordsets or ASP.NET DataSets, or else replace them with proper hand-coded parametized safe and efficient code. All versions of DW prior to 8.0.2, from the original UltraDev on up through 8.0.1, produce code that has only minimal and easily bypassable protections against SQL Injection.
-
“Validation must be performed server-side. You may (and should also add client-side validation to speed up the user experience and save server round-trips for those users whose browsers support it, but you must always back it up with equivalent server-side validation. It’s trivially easy for hackers to bypass client-side validation, either by faking GETs or even POSTs, bypassing your Hidden and Read-Only fields, stuffing non-listed values into your drop-down menus and radio button lists (all of those can be done with a very common Firefox Extension, for instance), or even by simply turning off JavaScript!” — Much better.
I agree *if* you are not using parameterized queries, and are using the string concatenation method (very bad idea) as pointed out in the article. If, however, you are using parameterized queries, intermediate SQL Server server-side validation becomes less of an issue as you don't have to be concerned with SQL commands being injected directly into your queries via parameters, nor with semicolons, apostrophes, or double dashes wreaking havoc in your queries. Also, if your parameters are defined explicitly to be of the appropriate type and length, truncation and overrun errors from the UI are not an issue that IIS can't resolve.
Proper DRI, FK relationships, and check constraints on tables will help prevent garbage data from being entered server-side as well.
Faking GETs and POSTs is beyond the scope of the article, but client-side validation in terms of SQL Server includes IIS validation. IIS is just another "client" as far as SQL Server is concerned. When your hidden form field variables, etc., are validated on IIS before IIS submits the query to SQL Server, faking GETs, POSTs, and manipulating form fields is not an issue as far as SQL Server is concerned. Your reference to client-side JavaScript validation makes me think you are considering the client-server model like this:
---------------IIS (Server)
Web Browser (Client) --------|
---------------SQL Server (Server)
When in actuality the Web Browser is not directly connecting to the SQL Server. The actual model is more like this:
Web Browser (Client) ------- IIS(Server/Client) -------- SQL Server (Server)
(Of course on SQL 2005, with HTTP Endpoints, this model changes yet again... But that's for another article).
BTW - When I type a really long post (like this one), I usually highlight the whole thing (CTRL+A) and copy it to the clipboard (CTRL+C) before submitting to keep it from goinf *poof* when I hit submit. If it does disappear, I can just paste it right back in and hit submit again
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply