Simple Rules To Stop Bad Guys

Hi, RockyH here,

I was browsing for IT security news from the hotel this evening and came across this gem:


That’s it. Of course there is no information about who to email, and why should their be. If they can’t figure out how to tell the difference between malicious traffic and real traffic other than by blocking entire IP ranges, there is little chance they could filter out spam should their email address be harvested off their web page.

After saying that I checked again the following night and they had amended their little blocked access page:


Besides, at best this is a triviality. It’s security theatre. When you set your machine to use a proxy through work or something like TOR you can get right past this kind of thing.

Almost every modern firewall product can do packet inspection to look for genuine malicious attack patterns.  In the modern Internet there is no need to do blind IP Range blocking. This was never a good idea in the first place, after all what you have really done is create a Denial Of Service (DOS) attack on yourself. Good thinking.

That reminds me of a guy who told me about the protection he built into his web site to prevent SQL injection. He said, “What I do is look for SQL injection attacks like OR 1=1 and if I find one, I kill the web application with an exception.” Right, so now all I have to do to take down your site is send you an ‘OR 1=1’ in the search page and Blamo, your site goes offline. Good thinking.

Ok everyone, pay attention! The best way to handle these kinds of things is a very simple tactic called Input Validation. Say it with me now, Input Validation!

All your commercial firewalls can do stateful packet inspection and drop suspect packets. Things like Threat Management Gateway (TMG) can even inspect traffic at the logical level and filter out known bad attack strings such as those used to exploit known vulnerabilities. Now I don’t recommend this kind of black-list inspection as your only means of defence, but it’s good to put a rule in place to plug the whole while the developers work on the patch.

With over 90% of the actual attacks happening at the application layer, this is where you should concentrate your defensive measures. It all starts with the software. If you have in-house developed applications, you can no longer afford to rely on goofy blacklisting mentioned above.

Here are a few simple rules for application development that will stop a vast majority of the attacks out there.

Rule #1: Implement a Secure Development Lifecycle in your organisation.

This includes the following activities:

  • Train your developers, and testers in secure development and secure testing respectively

  • Establish a team of security experts to be the ‘go to’ group when people want advice on security

  • Implement Threat Modelling in your development process. If you do nothing else, do this!

  • Implement Automatic and Manual Code Reviews for your in-house written applications

  • Ensure you have ‘Right to Inspect’ clauses in your contracts with vendors and third parties that are producing software for you

  • Have your testers include basic security testing in their standard testing practices

  • Do deployment reviews and hardening exercises for your systems

  • Have an emergency response process in place and keep it updated

If you want some good information on doing this, email me and check out this link:

Rule #2: Implement a centralised input validation system (CIVS) in your organisation.

These CIVS systems are designed to perform common input validation on commonly accepted input values. Let’s face it, as much as we’d all like to believe that we are the only ones doing things like, registering users, or recording data from visitors it’s actually all the same thing.

When you receive data it will very likely be an integer, decimal, phone number, date, URI, email address, post code, or string. The values and formats of the first 7 of those are very predictable. The string’s are a bit harder to deal with but they can all be validated against known good values. Always remember to check for the three F’s; Form, Fit and Function.

  • Form: Is the data the right type of data that you expect? If you are expecting a quantity, is the data an integer? Always cast data to a strong type as soon as possible to help determine this.

  • Fit: Is the data the right length/size? Will the data fit in the buffer you allocated (including any trailing nulls if applicable). If you are expecting and Int32, or a Short, make sure you didn’t get an Int64 value. Did you get a positive integer for a quantity rather than a negative integer?

  • Function: Can the data you received be used for the purpose it was intended? If you receive a date, is the date value in the right range? If you received an integer to be used as an index, is it in the right range? If you received an int as a value for an Enum, does it match a legitimate Enum value?

In a vast majority of the cases, string data being sent to an application will be 0-9, a-z, A-Z. In some cases such as names or currencies you may want to allow –, $, % and ‘. You will almost never need , <> {} or [] unless you have a special use case such as in which case see Rule #3.

You want to build this as a centralised library so that all of the applications in your organisation can use it. This means if you have to fix your phone number validator, everyone gets the fix. By the same token, you have to inspect and scrutinise the crap out of these CIVS to ensure that they are not prone to errors and vulnerabilities because everyone will be relying on it. But, applying heavy scrutiny to a centralised library is far better than having to apply that same scrutiny to every single input value of every single application.  You can be fairly confident that as long as they are using the CIVS, that they are doing the right thing.

Fortunately implementing a CIVS is easy if you start with the Enterprise Library Validation Application Block which is a free download from Microsoft that you can use in all of your applications.

Rule #3: Implement input/output encoding for all externally supplied values.

Due to the prevalence of cross site scripting vulnerabilities, you need to encode any values that came from an outside source that you may display back to the browser. (even embedded browsers in thick client applications). The encoding essentially takes potentially dangerous characters like < or > and converts them into their HTML, HTTP, or URL equivalents.

For example, if you were to HTTP encode <script>alert(‘XSS Bug’)</script> it would look like: &lt;script&gt;alert('XSS Bug')&lt;/script&gt;  A lot of this functionality is build into the .NET system. For example, the code to do the above looks like:

Server.HtmlEncode("<script>alert('XSS Bug')</script>");

However it is important to know that the Server.HTMLEncode only encodes about 4 of the nasty characters you might encounter. It’s better to use a more ‘industrial strength’ library like the Anti Cross Site Scripting library. Another free download from Microsoft. This library does a lot more encoding and will do HTTP and URI encoding based on a whitelist. The above encoding would look like this in AntiXSS

using Microsoft.Security.Application;
AntiXss.HtmlEncode("<script>alert('XSS Bug')</script>");

You can also run a neat test system that a friend of mine developed to test your application for XSS vulnerabilities in its outputs. It is aptly named XSS Attack Tool.

Rule #4: Abandon Dynamic SQL

There is no reason you should be using dynamic SQL in your applications anymore. If your database does not support parameterised stored procedures in one form or another, get a new database.

Dynamic SQL is when developers try to build a SQL query in code then submit it to the DB to be executed as a string rather than calling a stored procedures and feeding it the values. It usually looks something like this:

(for you VB fans)

dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = "
sql = sql & request.querystring("ArticleID")
set results = objConn.execute(sql)

In fact, this article from 2001 is chock full of what NOT to do. Including dynamic SQL in a stored procedure.

Here is an example of a stored procedure that is vulnerable to SQL Injection:

Create Procedure GenericTableSelect @TableName VarChar(100)
Declare @SQL VarChar(1000)
SELECT @SQL = @SQL + @TableName
Exec ( @SQL) GO

See this article for a look at using Parameterized Stored Procedures.

Rule #5: Properly architect your applications for scalability and failover

Applications can be brought down by a simple crash. Or a not so simple one. Architecting your applications so that they can scale easily, vertically or horizontally, and so that they are fault tolerant will give you a lot of breathing room.

Keep in mind that fault tolerant is not just a way to say that they restart when they crash. It means that you have a proper exception handling hierarchy built into the application.  It also means that the application needs to be able to handle situations that result in server failover. This is usually where session management comes in.

The best fault tolerant session management solution is to store session state in SQL Server.  This also helps avoid the server affinity issues some applications have.

You will also want a good load balancer up front. This will help distribute load evenly so that you won’t run into the failover scenario often hopefully.

And by all means do NOT do what they did on the site in the beginning of this article. Set up your routers and switches to properly shunt bad traffic or DOS traffic. Then let your applications handle the input filtering.

Rule #6: Always check the configuration of your production servers

Configuration mistakes are all too popular. When you consider that proper server hardening and standard out of the box deployments are probably a good secure default, there are a lot of people out there changing stuff that shouldn’t be. You may have remembered when Bing went down for about 45 minutes. That was due to configuration issues.

To help address this, we have released the Web Application Configuration Auditor (WACA). This is a free download that you can use on your servers to see if they are configured according to best practice. You can download it at this link. [edited to fix link]

You should establish a standard SOE for your web servers that is hardened and properly configured. Any variations to that SOE should be scrutinised and go through a very thorough change control process. Test them first before turning them loose on the production environment…please.

So with all that being said, you will be well on your way to stopping the majority of attacks you are likely to encounter on your web applications. Most of the attacks that occur are SQL Injection, XSS, and improper configuration issues. The above rules will knock out most of them. In fact, Input Validation is your best friend. Regardless of inspecting firewalls and things, the applications is the only link in the chain that can make an intelligent and informed decision on if the incoming data is actually legit or not. So put your effort where it will do you the most good.

Comments (9)
  1. Alastair Upton says:

    Shouldn’t Rule #4 read ‘Use parameterized SQL?’ Dynamic SQL is perfectly fine (and it is what most ORMs use) provided that it is parameterized. It is not the use of stored procedures that protect you (as you have shown in the example) but the additional protection of parameter validation over string concatenation.

    It is also important to protect the various layers of your application (e.g. implementing security on the database tier to make sure minimal rights are granted on objects).

  2. TheRockyH says:

    Hi Alistair,

    Not really. Dynamic SQL by definition is not fine. It depends on how you are considering ‘parameterized’ SQL. If you are referring to parameterized stored procedures, then yes, that is the way to go but that is not dynamic SQL.

    Essentially, dynamic SQL is when you build the SQL up as a string and do all of your variable/parameter substitution (either in code, or in the stored proc itself) before you send the SQL statement to the DB engine to be processed as a command string.

    Now, that being said, ORMs, and NHibernate, etc. can produce good SQL, that is technically dynamic, but with proper and extensive input validation.

    However these rules are aimed at developers writing code and the code that they write. Not necessarily the libraries they use. Your point is taken though. Thanks for the comment.

  3. Richard says:

    Where’s the missing link to download WACA?

  4. TheRockyH says:

    Sorry Richard, I fixed the link. You will have to login/register on Microsoft Connect, then download it from there.

  5. Daniel says:

    Alastair Upton is right, RockyH is wrong. "Abandon Dynamic SQL" is a superficial and wrong statement.

    For example SharePoint uses dynamic SQL excessively, and as long as you do it right (and if you do it only when you HAVE to do it, because it also may hurt perf and maintainability), with parameterized queries and optionally sp_executesql, there is no bigger attach vector than with a parameterized sp.

    If you write such articles about security, at least get the facts straight.

  6. TheRockyH says:

    Hello Daniel. Thank you for your passionate response.

    So just to be clear you said:

    ” there is no bigger attach vector than with a parameterized sp.” aside from the misspelling of attack I think you are trying to say that Parameterised Stored Procedures are the biggest attack vector. Then you immediately followed with:

    “If you write such articles about security, at least get the facts straight.”

    Excellent advice, especially considering your previous statement.

    So just for the sake of getting facts straight, can you give me any examples of where a properly created (not using string concatenation and calling EXEC or sp_execsql which is vulnerable to SQL injection) parameterised stored procedure is exploited through SQL injection?

    Here are some of the many many examples that describe where and why you should not use dynamic sql.


    Implement SQL strings using prepared statements that bind variables. Prepared statements that do not bind variables can be vulnerable to attack.

    Use vigorous white-list style checking on any user input that may be used in a SQL command. Rather than escape meta-characters, it is safest to disallow them entirely. Reason: Later use of data that have been entered in the database may neglect to escape meta-characters before use. Narrowly define the set of safe characters based on the expected value of the parameter in the request.

    From: <- This CWE definition is all about not using Dynamic SQL and properly validating input.

    Process SQL queries using prepared statements, parameterized queries, or stored procedures. These features should accept parameters or variables and support strong typing. Do not dynamically construct and execute query strings within these features using “exec” or similar functionality, since you may re-introduce the possibility of SQL injection.


    Never build Transact-SQL statements directly from user input.

    Use stored procedures to validate user input.

    Use Type-Safe SQL Parameters

    The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value instead of as executable code

    Use Parameterized Input with Stored Procedures

    Use the Parameters Collection with Dynamic SQL

    If you cannot use stored procedures, you can still use parameters, as shown in the following code example:

    SqlDataAdapter myCommand = new SqlDataAdapter(

    “SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id”, conn);

    SQLParameter parm = myCommand.SelectCommand.Parameters.Add(“@au_id”,

                           SqlDbType.VarChar, 11);

    Parm.Value = Login.Text;


    Use stored procedures for database access

    When the database server supports them, use stored procedures for performing access on the application’s behalf, which can eliminate SQL [injection] entirely (assuming the stored procedures themselves are written properly).

    From: the Microsoft SDL documentation, and quoted on Michael Howard’s blog:

    Applications accessing a database must do so only using parameterized queries. (RH This is a rule at Microsoft and dynamic SQL is considered a high severity bug if found)

    Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application. This vulnerability allows for unauthorized, interactive, logon to a SQL server which may result in the execution of malicious commands leading to the possible modification (or deletion) of Operating System or user data.

    From: the SDL documentation: (and Michael Howard’s blog above)

    “Applications accessing databases should do so only using stored procedures. “


    “Do not use “exec @sql” construct in your stored procedures.

    Using stored procedures helps to mitigate the SQL injection threat to a great extent since type checking is available for parameters. If the attacker supplies input that does not match the type constraints the stored procedures will throw an exception. In the vast majority of the cases, this should be properly handled within the application.

    However, if the stored procedures perform string manipulation in their code and then execute that query using the “exec @sql” construct incorrect handling of user input can produce the same SQL injection vulnerability as would be seen at the application layer.”

    From: (SQL Injection)


    Strong input validation – All user-controllable input must be validated and filtered for illegal characters as well as SQL content. Keywords such as UNION, SELECT or INSERT must be filtered in addition to characters such as a single-quote(‘) or SQL-comments (–) based on the context in which they appear.

    Use of parameterized queries or stored procedures – Parameterization causes the input to be restricted to certain domains, such as strings or integers, and any input outside such domains is considered invalid and the query fails. Note that SQL Injection is possible even in the presence of stored procedures if the eventual query is constructed dynamically.

    From: Stop SQL Injection Attacks Before They Stop You

    “Avoid Dynamic SQL

    The SQL injection attacks I have demonstrated in this article are all dependent on the execution of dynamic SQL—that is, SQL statements constructed by the concatenation of SQL with user-entered values. Using parameterized SQL, however, greatly reduces the hacker’s ability to inject SQL into your code.”


    SQL injection errors occur when:

    1.Data enters a program from an untrusted source.

    2.The data used to dynamically construct a SQL query


    Primary Defenses

    Defense Option 1: Prepared Statements (Parameterized Queries)

    Defense Option 2: Stored Procedures

    Defense Option 3: Escaping All User Supplied Input


    Best Practices when Dealing with Databases

    Use Database stored procedures, but even stored procedures can be vulnerable. Use parameterized queries instead of dynamic SQL statements. Data validate all external input: Ensure that all SQL statements recognize user inputs as variables, and that statements are precompiled before the actual inputs are substituted for the variables in Java.

    Daniel here are some books I’ve read that you may want to take a look at:

    Writing Secure Code“. 2nd Edition. M. Howard and D. LeBlanc. Microsoft. 2003.

    The Database Hacker’s Handbook: Defending Database Servers“. David Litchfield, Chris Anley, John Heasman and Bill Grindlay.  Wiley. 2005-07-14.  

    The Oracle Hacker’s Handbook: Hacking and Defending Oracle“. David Litchfield.  Wiley. 2007-01-30.  

    Gray Hat Hacking, Second Edition: The Ethical Hacker’s Handbook by Shon Harris, Allen Harper, Chris Eagle, and Jonathan Ness (Dec 20, 2007)

    Chained Exploits: Advanced Hacking Attacks from Start to Finish by Andrew Whitaker, Keatron Evans, and Jack B. Voth (Mar 9, 2009)

    The CISSP Prep Guide: Mastering the Ten Domains of Computer Security by Ronald L. Krutz and Russell Dean Vines (Sep 10, 2001)

    Hacking: The Art of Exploitation, 2nd Edition by Jon Erickson (Jan 11, 2008)

    There are many others but these will help you get started.

    Over the past 5 years, I’ve seen a lot of SQL injection vulnerabilities, and properly constructed stored procedures accessed through parameterised queries always fix it. Hopefully we have alleviated your belief that “there is no bigger attach vector than with a parameterized sp”

  7. Ronald says:

    I actually understood Daniel’s comment as: the attack vector for stored procedures, parameterized queries and sp_executesql is equally big.

  8. TheRockyH says:

    Someone else recently mentioned this to me as well. And after reading it in that light, perhaps you are correct.

    However, that sentiment is still incorrect. Securing dynamic SQL is much harder than securing parameterised stored procs.

    All things being equal, there are more likely to be mistakes in implementation and filtering with dynamic sql than there are with parameterised stored procs.

    So based on our team’s 2000+ reviews, and identified bugs, and what we have seen is that dynamic sql is much more prone to error and consequently much more likely to be exploitable. So they are not the same.

    Additionally, along the lines of checking facts, SharePoint does not use dynamic sql. There is no dynamic sql created in the web application tier of SharePoint, and all of the database stored procs are parameterised stored procedures.  The only time you will have dynamic sql in SharePoint is if you create you own web part and you put it in there. SharePoint itself does not have any.

    He may be confused with SPQuery. SPQuery is it’s own type of query for finding objects stored in SharePoint. It, like LDAP or XPATH, has its own type of query system. Again it is not Dynamic SQL because it is not SQL against a database. It is actually a thing called Collaborative Application Markup Language (CAML). Bottom line, you can’t own an SQL box with SPQuery.

    If anyone is interested, here are the links to information about it:

    And the MS SharePoint team blog has a lot of good info too:

  9. Daniel says:

    I think anyone here agrees that stored procedures and parameterized queries are always a superior choice, because they offer "some" built-in validation for free(the stored proc still needs application level validation), are easier to maintain and – depends on the perspective – usually perform no worse.

    Maybe we are not on the same page regarding the definition of "dynamic" sql: Building the parameterized SQL dynamically and still using @Param-substituted parameters WITH the built-in validation, does that constitute "dynamic" SQL or just "parameterized" SQL? To me, the query is still "dynamic".

    But actually, you simply cannot write any database query (even when talking to SQL Server, my favorite relational DB) pre-built (see SharePoint 2007, which uses parameterized, but dynamically built/stitched together queries for some things) or even parameterized. Some of the SQL Server Service Broker statements simply cannot be parameterized when using the ADO.NET interface to call them. You need to use fully "dynamic" SQL here.

    So, telling users to "get a new database" is not a good idea:-p SQL Server is still king of the hill.

Comments are closed.

Skip to main content