Strange as it might seem, some people publish, usually unwittingly, detailed information about the structure of their databases by revealing SQL (Structured Query Language) code.

I don't mean in error messages (which should of course never be displayed to web site users):

Nor in the generated web page source code (you wouldn't do that would you?):

Nor even when it's in a URL (I won't ask):

No, what I mean is when the code is simply indexed and appears on the site's own web pages (often as search result listings), and which then sometimes subsequently picked up by Google, Bing and so on:

So what's going on here? Without more information, I can only surmise, but I think these web sites are using catalogues (pre-built registers or collections) to index the web pages. However some of the pages have static content and others are dynamically built using database queries. So the indexing tool is recording the text from the scripting language rather than what is generated "at run time" to the web site user. These scripts should not be indexed, and thus leaked, in this way; instead the static results need to be merged and ranked with appropriate links to dynamically-created pages. If I search a dictionary web site for "query", I don't want a link to a page that has this in the code, I want the actual pages that define or reference the word "query".

The danger of automated indexing, is that it can include all types of unforeseen files in its catalogue, including backup and old copies of files, unless the indexing strategy is considered carefully:

Having the SQL displayed in this manner makes it much easier for someone to compromise the data, damage the site or its users.

The poor apostrophe gets such bad press. It's implicated in so many SQL Injection problems and now it has been banned by some local councils in the UK.

Yes Birmingham City Council and Wakefield Council have been so concerned about punctuation usage they have banned apostrophes in street names and thus on road signs, according to recent news reports such as in the Daily Mail and Yorkshire Evening Post.

I wasn't able to find confirmation on the councils' own web sites—I was also hoping perhaps the council leader or spokesperson might have had an apostrophe in their own family name. Actually no members from either council have apostrophes, or any other "unusual" characters, in their names. Birmingham's web site search kindly refused to tell me anything about the apostrophe:

This has got many people bothered by the dumbing down, especially those in the Apostrophe Protection Society.

I don't think we'll be able to get away with banning characters in the web application world to prevent issues like SQL injection. Yes there are many web apps that don't allow apostrophes in submitted data rather than tackling the root cause of the weakness. The use of database commands with bound parameters combined with appropriate validation, decoding, encoding and escaping are the answers.

PS It's not just apostrophes (or single quotation marks) you have to worry about.

The next Open Web Application Security Project (OWASP) London meeting is this week.

The OWASP local chapter meeting in London is on Thursday 12 March 2009. Everyone is welcome, but you need to register (free) first.

After an introduction from the new chapter leader Justin Clarke, there will be talks on advanced SQL Injection techniques and the Software Assurance Maturity Model (SAMM), including perhaps discussion of the recently launched Building Security In Maturity Model (BSIMM). I will also be speaking briefly about the OWASP Global Industry Committee.

See you there.

Update 11th March 2009: The tutorials and conference programme have been released for the biggest European application security event of the year OWASP AppSec Europe 2009 in May. The Keynote speakers are Ross Anderson, Professor in Security Engineering, University of Cambridge and Bruce Schneier, Chief Security Technology Officer, BT.

A Valentine's Day special (yes a day early, I know, but better than a Friday 13th item). What data type should the "sex" attribute be in your database and what values should your form fields have?

No jokes such as the phrases (strings) "yes please" or "no thank you, we're British"... well, I guess a single character for gender of either "M" or "F" is often chosen. You might need to allow null for when this isn't known, or not divulged. Other people might use an alternative coding scheme.

Why not use a standard scheme?

You might be surprised to learn that there is an International Organization for Standardization (ISO) standard ISO/IEC 5218 on "Codes for the Representation of Human Sexes". And interestingly the technical committee have selected a one-character numeric code, with four (yes 4) valid options:

• 0 = Not Known
• 1 = Male
• 2 = Female
• 9 = Not Applicable

The standard points out (see the ISO 5218 entry on Wikipedia) the ordering is not meant to be significant, but UFO watchers and others will be pleased by the spare slots 3-8.

The current four values have also been incorporated as the sex data attribute in the P5 Guidelines for Electronic Text Encoding and Interchange by the Text Encoding Initiative (TEI) who develop and maintain a standard for the representation of texts in digital form. So, for a positive security models where the values passed to and from user input screens and in processing are the same as in the database, the acceptable values (also known as a "white list") is a set of integers: {0,1,2,9}.

The answer is therefore a small positive integer.

The question "what is SQL injection" is often asked since it's become such a common exploitation of web coding vulnerabilities.

Well a pre-requisite is some form of database being used by the web site or web application. SQL (Structured Query Language) is programming language which can be used to view, add, modify and delete the content of databases. Whilst the exact SQL syntax will vary slightly between database products, there are many common aspects and relatively few database products used.

SQL injection is the modification of intended code to make some other action occur. Often, this is to add other content, such as links to other sites, to your own web content.

The Web Application Security Consortium Web Security Glossary defines SQL Injection as:

There's a nice summary at 2008 - The Year of the SQL Injection Attack by David Rook on BlogInfoSec.com. Even after correcting the vulnerbaility and cleansing contaminated data, SQL injection can also have a longer lasting effect - see SQL Injection Poses Search Engine Optimisation Threat.

Version 3 of the Open Web Application Security Project (OWASP) Testing Guide has been released after a 6-month period of addition, enhancement and review.

The OWASP Testing Guide is an ideal reference for both developers and testers—version 2 was fantastic, and this new version is even better. The testing framework now covers 66 controls and, like in the previous version, each control has a brief summary and is described in detail followed by black box (no additional knowledge) and grey/gray box (partial knowledge) testing methods and examples where appropriate.

The controls and testing methods are fully referenced to provide additional guidance and explanation.

The controls are grouped into ten categories, including new separate categories "Authorization" and "Configuration Management". I'm especially pleased to see the latter broken out on its own, since even a perfectly coded application can have vulnerabilities introduced during deployment and changes to the application.

The OWASP Testing Guide now also includes a "best practice" penetration testing framework and a "low level" penetration testing guide that describes techniques for testing most common web application and web service security issues. More information is available on the Testing Project pages.

Last Thursday I attended the latest OWASP London meeting to hear two excellent speakers.

Justin and Adam from Gotham Digital Science presented demonstrations of a potential SQL injection worm and their Secure Parameter Filter (SPF) for IIS either side of a round-up from Dinis of the OWASP EU Summit 2008 outcomes.

SPF looks like a promising quick-patch tool for vulnerable web sites (written in any programming language) that are served by Microsoft Internet Information Server version 7 (IIS7) or could be served via an IIS7 proxy - if the site's written in ASP.NET, it's definitely worth serious consideration, even on IIS6. The main benefit is protection from tampering of parameter values, URL manipulation and replay attacks, combined with some blacklisting of cross-site attack code in user-supplied input. There are potentially some usability issues relating to restricting application entry points and having token time outs, but the tool of course needs to be configured to suit each site. Do take a look.

There are a pair of identical trial web sites available (from the page linked above) with and without the SPF tool installed - having seen the demo I'm looking forward to trying this on some test sites.

Input data validation is a fundamental practice that web applications need to get right to protect users, their data, your data and your web site. But free text entry fields in web forms can be problematic - we all make mistakes, misread the instructions, type in the wrong fields, avoid filling in the mandatory items and so on. But how should the web application respond to these?

Users can submit data to web applications in many ways - file uploads, page requests, URL parameters, form parameters, file uploads, request headers, cookies and so on, but it is in conventional forms where we need to be somewhat lenient in how we respond to data validation issues.

Form data must be checked for integrity, business rules and validity of the following:

• Required (whether the field must be submitted, not null)
• Type (e.g. positive integer, text string, date, true/false boolean)
• Length/Range (e.g. numerical limits, number of decimal places, length of text, applicability of a date)
• Format (e.g. DD/MMM/YYYY for a date, international format for telephone numbers, allowed/disallowed characters)
• Character encoding/character set.

HTML forms allow radio buttons, checkboxes and drop down lists where the value(s) meant to be submitted are predetermined. In these cases it is reasonable that some of these failures are likely to be caused by malicious users tampering with values, or some fault in the application itself.

But what about text fields where users can enter any value in free format? I mentioned in Separate the Text from the Code a posting elsewhere about the display of form entry error messages. At what point to you reject the user input completely as malicious?

You may have mechanisms looking for dangerous input such as custom application code, an application filter module, security settings in your web server software or a web application firewall. If a user accidentally or without malice types text that could be construed as malicious because it matches a signature for something like cross site scripting or SQL injection, when they submit the form they could be presented with something much less friendly. This could occur if the control points are set to intervene rather than simply monitor, and so the 'friendly' message will never appear and they will see something else, like these three examples:

So be wary about validation and active blocking. In some cases, user data should be accepted and then sanitised before explaining the problem and possibly re-displaying their data appropriately encoded. The problem might be as simple as putting an item of text in a date field, or using an "incorrect" date format. If the filtering mechanisms are too strict, the usability of the application will be much degraded.

For each item of user input, try to identify:

1. What is allowed to be received by the web application for each field
2. Exact validation rules before the application can use, store or transmit the data value
3. Which control points will be checking and enforcing these
4. The sensitivity of the values
5. How the data will be used subsequently
6. How the data values should be encoded when output.

In some cases nos. 1 and 2 will be the same, but more often they will be different. Build these into your application test cases.

After all, some people live near "Alert Street" and "Script Drive":

For more information on integrity checks, validation and business rules, see data validation from OWASP's Development Guide. I'll discuss client-side validation, whitelisting and blacklisting in future posts.