30 October 2009

SQL

Posts relating to the category tag "SQL" are listed below.

30 October 2009

Don't Publish Your SQL

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

Partial screen capture showing obfuscated details of a MySQL database query appearing as an error message from a web page script

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

Partial screen capture showing obfuscated HTML source code from a web page with a DIV element of class 'debug' with the full database SQL string including all the parameters

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

Partial screen capture showing obfuscated browser address bar with the full database SQL string as a URL parameter

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:

Partial screen capture from a web site's search results page - the first result shows a large block of SQL, the second many XML output assignment statements and the third JavaScript code comments Partial screen capture from another web site's search results page - the last two results on the first page display database SQL code Partial screen capture showing obfuscated Google search result with the page summary containing SQL code using to generate the content of the dynamic web page

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:

Partial screen capture of a search results page with five identical results to an 'Edit submission' script, with different filenames such as appended with 'old' and '_bck'

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

Posted on: 30 October 2009 at 08:36 hrs

Comments Comments (1) | Permalink | Send Send

20 March 2009

Do Apostrophes Receive Too Much Bad Press?

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:

Partial screen capture of a Birmingham City Council web page showing the search form containing the word 'apostrophe' and the message 'A problem has occurred with this search.' that appears after submitting the form

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.

Posted on: 20 March 2009 at 08:19 hrs

Comments Comments (0) | Permalink | Send Send

10 March 2009

OWASP London - This Thursday

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.

Posted on: 10 March 2009 at 08:52 hrs

Comments Comments (0) | Permalink | Send Send

13 February 2009

Sex?

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?

Partial view of the ISO/IEC 5218 cover sheet showing the title

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.

Posted on: 13 February 2009 at 08:05 hrs

Comments Comments (0) | Permalink | Send Send

13 January 2009

What is SQL Injection?

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:

An attack technique used to exploit web sites by altering backend SQL statements through manipulating application input.

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.

Posted on: 13 January 2009 at 10:19 hrs

Comments Comments (0) | Permalink | Send Send

19 December 2008

New OWASP Testing Guide

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.

Partial view of a page from the OWASP Testing Guide V3.0 showing 'Brief summary', 'Description of issue' and 'Black box testing and examples' headings for a control.

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

Partial view of a page from the OWASP Testing Guide V3.0 showing 'References - whitepapers' and 'References - tools' headings for a control.

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.

Posted on: 19 December 2008 at 09:43 hrs

Comments Comments (0) | Permalink | Send Send

09 December 2008

Parameter Filtering

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.

Posted on: 09 December 2008 at 09:49 hrs

Comments Comments (0) | Permalink | Send Send

21 November 2008

Free Text Form Field Data Validation

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:

Partial screen capture of page showing message 'Error: Invalid Request' when some potentially malicious input was submitted in a form field. Partial screen capture of page showing message 'Forbidden - You do not have permission to access this document' when some potentially malicious input was submitted in a form field. Partial screen capture of page showing message 'Request validation error' when some potentially malicious input was submitted in a form field - the page also includes details of the script path, ASP.NET version, a full stack trace and information for the developers on how to suppress the message.

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":

Partial screen capture of M&S store finder search results page showing two stores for a search term including script tags - one on Alert Street and one on 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.

Posted on: 21 November 2008 at 12:55 hrs

Comments Comments (0) | Permalink | Send Send

19 August 2008

SQL Injection Poses Search Engine Optimisation Threat

Web sites that have been compromised by the recent wave of SQL injection attacks on ASP and PHP based sites have plenty to deal with already, but it seems a threat to their search engine ranking may be a longer lasting problem.

SQL injection is an attack where nasty input (you might hear this referred to as "maliciously crafted parameters or names") is sent to web applications - typically in page addresses or form submissions. If the application doesn't validate the data correctly, it can be used to compromise information in an associated database. This may cause loss, destruction or alteration of data. Recent attacks have added malicious code into page content with the aim of compromising people visiting the hacked sites, if the page output is not validated and encoded correctly.

You can find compromised web sites by searching for the JavaScript code embedded in the content after it has been re-indexed by search engine crawlers (robots). However, it looks like some of these are being removed from search engine catalogues, meaning the site will suffer from a significant reduction in traffic from people using search engines (natural searches).

Site owners should of course ensure their sites are not liable to SQL injection attacks, but I think they should also monitor changes in search engine rankings and visitor traffic patterns.

Has anyone had this affect them?

Posted on: 19 August 2008 at 09:16 hrs

Comments Comments (0) | Permalink | Send Send

SQL : Web Security, Usability and Design
http://www.clerkendweller.com/sql
ISO/IEC 18004:2006 QR code for http://clerkendweller.com

Page http://www.clerkendweller.com/sql
Requested by 38.107.191.115 on Friday, 12 March 2010 at 21:52 hrs (London date/time)

Please read our terms of use and obtain professional advice before undertaking any actions based on the opinions, suggestions and generic guidance presented here. Your organisation's situation will be unique and all practices and controls need to be assessed with consideration of your own business context.

Terms of use http://www.clerkendweller.com/page/terms
Privacy statement http://www.clerkendweller.com/page/privacy
© 2008-2010 clerkendweller.com