SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updated SQL Injection


Updated SQL Injection

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29987 Visits: 9671

No the concatenation becomes this :

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


Mike C
Mike C
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2753 Visits: 1168

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


Debra Hodges-389283
Debra Hodges-389283
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 1
Wonderful article! Thank you so much!
Joel Rea
Joel Rea
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

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


Mike C
Mike C
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2753 Visits: 1168

“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


Joel Rea
Joel Rea
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

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


Mike C
Mike C
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2753 Visits: 1168

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.


George Damien Varkey
George Damien Varkey
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 47
Good Article. Every one should go through this.
markus-684303
markus-684303
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
chisholmd
chisholmd
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

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

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