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 Thursday, September 21, 2006 7:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594

No the concatenation becomes this :

 

SELECT * FROM [Login] where [User] = 'Ninja''s_RGR''us';

Post #310240
Posted Thursday, September 21, 2006 8:12 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

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

Post #310266
Posted Friday, December 15, 2006 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 13, 2007 10:43 AM
Points: 10, Visits: 1
Wonderful article!  Thank you so much!
Post #330826
Posted Friday, December 15, 2006 11:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 27, 2010 10:56 AM
Points: 9, Visits: 33

   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="&times;" ErrorMessage="User Name is REQUIRED!" />
<asp:RegularExpressionValidator runat="server" ID="rgxvUserName" ControlToValidate="tbUserName"
ValidationExpression="^\w{4,16}$" EnableClientScript="true" Text="&times;"
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.

Post #330925
Posted Friday, December 15, 2006 12:12 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

“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

Post #330946
Posted Friday, December 15, 2006 1:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 27, 2010 10:56 AM
Points: 9, Visits: 33

   Even with parameterized queries, new SQL Injection techniques can still work. My point, however, was that in general one should never rely on client-side validation alone. This includes even pages that do no data access, but use client-side validation or calculation for other purposes. Client-side validation (or calculation) is icing on the cake, and should always be backed up by server-side validation and/or calculation. It’s not just a matter of stopping SQL Injection attacks. It’s a matter of, for instance, preventing a user from overriding the calculated total on a shopping cart, and getting a whole shipment of PlayStation 3s for 1¢ at your client’s expense (and likely yours as well once said client rightly sues your @$$ into a bankruptcy black hole).

   I’m well aware that IIS sits between the Web Browser and SQL Server, and acts as both Client to SQL Server and Server to the Web Browser. I’m not stupid. As for the [Ctrl]+[A] [Ctrl]+[C] thing, I normally do do that. I just forgot that time.

   Thanks for mentioning proper parameter types and sizes. Yes, that does prevent truncation error messages, but if you don’t also warn the user about the proper length, or prevent said user from accidentally entering more text into a TextBox than the database field can hold by using MaxLength backed up by validation, the user might get ticked upon finding his or her nice long entry truncated without warning. Imagine if this Forum had a 255-character-per-post limit that it neglected to warn us about.

Post #330970
Posted Friday, December 15, 2006 1:55 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

Not implying by any means that you're stupid.  I just wanted to make sure we're on the same page when we talk about "client-side validation".  From a Web Designer's perspective, for instance, "client-side validation" might consist primarily of JavaScript-style validations.  From a SQL Programmer's perspective, client-side is everything up to the point that the data is passed to SQL Server, which includes IIS validations (which would be "server-side" from the Web Designer's point-of-view).

The problem with using terms like "client-side" and "server-side" is that it fails to encompass the many layers and perspectives in a modern n-tier system, where the middle tiers act as both clients and servers.  I should have probably used the more appropriate terms "data layer", "business logic layer", "application/user interface layer", etc.  For purposes of this article, "server-side" is the data layer, "client-side" is everything that feeds the data layer.

As for SQL injection using parameterized queries, I've yet to see one work.  SQL Server literally separates the data from the actual query with a parameterized query.  I have heard of buffer overrun attacks that can work, but only if you're on older SQL Server service packs and you don't validate your data for length.  If you have an example of a SQL injection technique that can circumvent query parameterization, I'd be very interested in testing it out!

Thanks.

Post #330985
Posted Monday, December 18, 2006 12:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 21, 2011 9:47 AM
Points: 16, Visits: 47
Good Article. Every one should go through this.
Post #331136
Posted Friday, March 21, 2008 12:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2008 11:09 PM
Points: 1, Visits: 4
These functions clean the SQL and use Regular Expressions

For ASP/VB:
http://www.swingnote.com/downloads/mssqlsafe.txt

For PHP:
http://www.swingnote.com/downloads/mysql_safe.txt

Markus Diersbock
Post #472685
Posted Friday, March 21, 2008 1:28 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 2:35 PM
Points: 194, Visits: 86
One thing that has not been mentioned is permission chaining. Each of my web apps uses its own SQL account and that account is ONLY given permission to run the stored procedures called from the app. Sometimes I'll use two accounts one each for different portions of the app. Because of permission chaining that account does not need permissions to access any tables directly.

1) Encrypt sensitive data
2) Server side validation (versus injection and cross site scripting)
3) Paramertized stored procedures
4) Reduced privilige account from web apps
5) Recordset returned should only contain the minimum required fields (no select * ...)
6) Frequent off server backups :)

I think a point that should be drove home very firmly is that you can NOT rely soly on variable validation.

I once read an excellent article describing all sorts of ways to trick single quote substitution. I couldn't find it again but this page offers at least one example.
http://dotnet.org.za/codingsanity/archive/2005/09/28/44998.aspx

On a related note:
Once and awhile I download some neat looking free web app written in PHP. As soon as I see all the dynamic SQL in the PHP code I can't bear it and have to delete it.

Using paramertized stored procedures is:
-more secure
-more performant
-more readable in code

Anyone who thinks its "faster and easier" just to toss dynamic SQL into their code should think again. Once you develop the habit you'll wonder why you did it any other way.







Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Post #472699
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse