Now, suppose that you need to get the records from the database, sorted this way – first, show records with due date in ascending order, second, show records with no due date ordered by priority from higher to lower, and finally, show records that do not have due date and priority and sort them by date created. The first thing that comes to mind is to write this query:

SELECT * FROM todo WHERE ... ORDER BY due_date asc, priority desc, create_date asc

It would seem that’s all, the problem is solved, but there’s a catch – the first field (due_date) is not compulsory and that’s what we get as a result of this query:


As you can see we have first records with no due date, but we wanted to show records with most recent due dates first. Obviously reversing sorting order from ASC to DESC will not help. Usually in such cases, a UNION of two SELECT queries is used. In the first query we select only the records with the due date, in the second – on the contrary, only the records with no due date. Something like this:

SELECT * FROM todo WHERE due_date IS NOT NULL ... ORDER BY due_date asc
UNION
SELECT * FROM todo WHERE due_date IS NULL ... ORDER BY priority desc, create_date asc

Does everyone agree with this solution? Don’t you want to look at it one more time? Okay, I’ll put you out of your misery; the query does not work for the simple reason that when you use ORDER BY with UNION it is applied to all records in a result set. In this case, you might say, UNION does not solve our problem, because the result would be no different from the first query and you will be right. However, UNION does gives us the ability to change the result in a simple way. What didn’t we like in the first query? The fact that the records without a due date were at the beginning. Besides, why did they go to the top? Because we sorted in ascending order by column allowing NULLs, and according to documentation NULL values are presented first if you do ORDER BY. What should you do to move these records to the bottom? That’s right, to make sure that we use some other very big value instead of NULL. According to the manual the maximum value of the datetime field is ’9999-12-31 23:59:59′, which we will use. We now have the following query

SELECT priority, description, due_date, due_date AS sort_due_date, create_date FROM todo
WHERE due_date IS NOT NULL ...
UNION
SELECT priority, description, due_date, '9999-12-31 23:59:59' AS sort_due_date, create_date FROM todo
WHERE due_date IS NULL ...
ORDER BY sort_due_date ASC, priority DESC, create_date ASC

and the result looks like this:


This query keeps the original due_date column intact, and sorting is performed by using the synthetic column ‘sort_due_date’. We need this in order not to distort the real data, we do not want to confuse ourselves and show the user the date of the end of the (MySQL) world? The problem is solved. I don’t know about you, but I love the elegant solutions, and this solution is definitely not elegant. Do you want to know what the elegant version of the solution is? Then read on.

In the last few months, we have been working with CodeIgniter and previously we worked mostly with ZendFramework. So, in CI and ZF, and in many other frameworks the ActiveRecord pattern is widely used. This means that the first SQL query we wrote is created this way:

$this->db_app->select('priority, description, due_date, create_date');
$this->db_app->from('todo');
$this->db_app->where( ... );
$this->db_app->order_by('due_date asc, priority desc, create_date asc');

And yet, I have not found the capabilities in CI to build UNION queries using this pattern. If you know how, I would appreciate your input. If a WHERE clause in the query is simple, in principle, we can form the first request and compile it. Then we can create the second request using the same method and compile it, and finally connect them with UNION manually and run it. Often, however, the conditions in the query are formed depending on the parameters passed, and the logic can be quite daunting. As a result, we get a strong code duplication. However, there are no hopeless situations, and here a very handy feature of MySQL helps us – the use of conditional logic in the ORDER BY construction. It works as follows:

SELECT priority, description, due_date, create_date
FROM todo WHERE ...
ORDER BY IFNULL(due_date, '9999-12-31 23:59:59') ASC, priority DESC, create_date ASC

That’s what I call an elegant solution! Those who disagree with me please write in the comments, I will remove them anyway . We do not duplicate the code because we have one request. We just need the right way to create ORDER BY conditions. Unfortunately CI has some problems with it. If you try (and I have tried, no doubt) to set sorting using Active Record this way:

$this->db_app->order_by("IFNULL(due_date, '9999-12-31 23:59:59') ASC, priority DESC, create_date ASC");

Then you, like me, will be disappointed. The super smart framework will split the passed string and will surround the first word with the quotation marks. As a result, a request will be completed with an error. By the way, if anyone knows how to ask CI not to do it, please let me know. Finally, we are left with the only workaround – to add an order condition to a query without using the Active Record, ie manually. Yes, it violates our beautiful code a little, but in my opinion, it is a lesser evil. I did it like this:

$this->db_app->select('priority, description, due_date, create_date');
$this->db_app->from('todo');
$this->db_app->where( ... );
 
$sql = $this->db_app->_compile_select();
$sql .= " ORDER BY IFNULL(due_date, '9999-12-31 23:59:59') ASC, priority DESC, create_date ASC";
$result = $this->db_app->query($sql)->result_array();

The attentive reader will notice one inconsistency here – the method is called _compile_select(), defined in the class CI_DB_active_record as protected. I confess, though I do not like to change the code of any good framework, but I saw no other way to generate a query without executing it but to make this method public. Again – let us know how to do it better. That’s it – we shared what we learned and are looking forward to your comments.

Finally, a little note. Column’s due_date default values is NULL, in our example. However this is not always the case and sometimes you can find, for example, ’0000-00-00′ as a default value. How to form ORDER BY statement in this case? You can use CASE expression this way:

SELECT priority, description, due_date, create_date
FROM todo WHERE ...
ORDER BY
  CASE WHEN due_date ='0000-00-00' THEN '9999-12-31 23:59:59'
    ELSE due_date
  END ASC, priority DESC, create_date ASC

In this particular case I use I installed Fedora and allocated 10GB but later I started using this VM for quite large databases and free space quickly disappeared.

# df -k
Filesystem                    1K-blocks    Used Available Use% Mounted on
devtmpfs                         498132       0    498132   0% /dev
tmpfs                            508060       0    508060   0% /dev/shm
tmpfs                            508060     772    507288   1% /run
/dev/mapper/vg_fedora-lv_root   7641992 6590912    656228  91% /
tmpfs                            508060       0    508060   0% /sys/fs/cgroup
tmpfs                            508060       0    508060   0% /media
/dev/sda1                        487652   84379    377673  19% /boot

# fdisk -l
 
Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders, total 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000e3a8c
 
   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     1026047      512000   83  Linux
/dev/sda2         1026048    20971519     9972736   8e  Linux LVM
 
 
Disk /dev/mapper/vg_fedora-lv_swap: 2113 MB, 2113929216 bytes
255 heads, 63 sectors/track, 257 cylinders, total 4128768 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
 
 
Disk /dev/mapper/vg_fedora-lv_root: 8086 MB, 8086618112 bytes
255 heads, 63 sectors/track, 983 cylinders, total 15794176 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

To grow disk space I went to VMWare console and added one more disk as shown below:
 
 

 
 
Now we can see a new disk in “fdisk -l” command output.

Disk /dev/sdb: 21.5 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders, total 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Now we create partition sdb1 covering all new disk using “fdisk /dev/sdb”.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
Using default value 1
First sector (2048-41943039, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-41943039, default 41943039):
Using default value 41943039
Partition 1 of type Linux and of size 20 GiB is set
 
Command (m for help): v
Remaining 2047 unallocated 512-byte sectors
 
Command (m for help): w
The partition table has been altered!

Finally we just expand our volume group and file system:

pvcreate /dev/sdb1
vgextend vg_fedora /dev/sdb1
lvextend -l +100%FREE /dev/mapper/vg_fedora-lv_root
resize2fs /dev/mapper/vg_fedora-lv_root

Most of all we would not want to turn the Answers announcement into advertising. Instead, we decided to share our thoughts about two kinds of software – subscription based and the one you can pay for once and own, and specifically about web applications. As a rule, web apps are not to be for personal use, and to work in peer groups, like school teachers, employees, software developers. Then we actually explain how and why an idea was born to create some downloadable web applications. You don’t think we made Answers just because we didn’t want to use existing SaaS? Excellent! Then tune in to the philosophical mood (pipe, rocking chairs, a fireplace, and Sherlock Holmes nearby are highly recommended).

Because the whole internet world creates web services with monthly subscriptions (with some variations) and sells these services for a lot of money to the end user, we, at Deep Shift Labs, believe that there are still fans out there who like the concept of buying and owning a piece of software. System administrators are not yet extinct, they are the heroes, who are able to maintain the server software without spending hours on it, because they are professionals in their field and as a result are efficient and valuable.

For some strange reason, even the world of traditional desktop applications now also wants to sign you in. Adobe (picture) and Microsoft (picture) offer you to use their products with a monthly payment. Even AutoDesk (for free, as we understood – the picture) offers to view and edit your drawings in a browser and on mobile devices. Vendors of web applications are moving in the same direction. Products such as Kayako, Atlassian, and even the smaller, not so well known, open source CMS – Matrix (picture) are moving to subscription based services.

Time will show how successful these efforts will be. Perhaps, in the near future, we will all become accustomed to the fact that the software is available only by subscription – by month (subscription), by a user (per seat or named user license), by size (volume license) or their combination. We do not want to believe in it, and even somehow want to resist.

We are not talking about social networks, where they themselves are the product, and exist as a single copy to make sense. We are talking about systems that solve a particular problem; a real problem for your company or your personal one.

The list of web applications which can be purchased and installed is getting smaller and smaller. What types of licenses can we find here? Perpetual – our Answers, as an example – a one-time payment. For the named account – Atlassian and Kayako. Recurring revenue in this case is coming from the sale of support and providing access to new versions of the product for a fraction of the full license fee.

Fig. 1 Kayako

If you do not need support and do not care about upgrades, you pay once for a perpetual license. Enterprises do not do that but small businesses and startups can. With per seat licenses, you only pay when you have crossed the threshold of license restrictions (for example, Kayako and Atlassian license registered accounts).

Kayako Resolve
10 staff $599
20 staff $1099

Atlassian JIRA
10 users $10
25 users $1,200

Splunk gives us an example of volume licensing. “Splunk Enterprise pricing in North America starts at US $ 6,000 for a 500 megabyte-per-day perpetual license, including first year support, or US $ 2,000 per year for a term license including support.” Here we see a rather unusual approach of using term licenses – in essence a subscription for locally installed software. That is, we pay 6K we own Splunk, but only if the volume of logs per day is less than 500MB. When you pay for a subscription, you pay every year, and are also limited to 500MB per day. I think we can find a similar approach amongst the monsters of the server software – Oracle, Microsoft, SAP.

Furthermore Splunk also promotes online service similar to Loggly.

Subscription services want to buy clients by giving them an option not to find/buy hardware, install, make updates, do backups. All updates are available immediately to all their users. Let us try to formulate the arguments of those who creates SaaS and those who do not want to buy or purchases subscription based services in the absence of other alternatives?

On the one side we have SaaS vendors:

- Cash flow comes regularly every month

- Not too much testing is required – release, monitor errors and quickly fix them on the server. No patches and updates to ship and headaches to support their rollover.

- Site hacked, passwords stolen, outage – apologize to users.

- Easy to sell the business. If the new owner will break the product, or close it; what will the end users do? God be with them. Here are just a few services we accidentally found while writing this post – Snip.it, Connotea, Qualaroo.

- Decide to raise prices or change plans – no problem.

- Remove some features and added another ones – well … some clients will be upset, some will even leave. The main idea is that more should come.

- Want to use your data? Learn our API and get what we provide and in the way we provide it. There are a few services which allow you to walk away with your own data. When such option is available – it is often AWS S3 bucket. Is this the only place where you can store the data now? Here are some examples – Loggly and Kissmetrics. In both cases we are talking about the log files. We found these SaaS companies which provide data export – DeployHQ and Sifter. Did you meet SaaS companies who providing database dump? Tell us in the comments.

- Do you have more traffic, customers, and employees? Excellent – you need another subscription plan. Yes – it is more expensive as this is our pricing model. That is, the additional resources that are often worthless are now used as an argument for a greater payment. In fact, you are already using their service, your data is already out there in a strange format and the cost to go to another service/product is significant. Argument is – you use it more – you have to be able to pay more. The real situation does not bother anyone.

- You can sell the impersonal data ( or not) that only you possess – the owners of the service, or use them to add functionality in the premium subscriptions. That is, data is created collectively by all users and sold back to them.

On the other hand the position of those who want to buy and install web applications:

- I want to buy a web app and own it. I see a fixed price and its functionality suits me as is. The purchased product becomes an asset of the company, not an expense.

- If the price depends on the number of users – not a problem. When, for example, the help desk department will have not 2 people, but 22, I am happy to pay the additional license cost as it is a one-time fee again.

- If an update is released and I need it – I can upgrade to it, or even pay 30-50% of the initial price.

- Do not worry that something you desperately need will disappear from a service you use, and something that is not necessary will pop up. You are in control and you decide to upgrade or not.

- My company has a system administrator, and he can install all correctly, make sure all is secure and set a backup. Or … my company is serviced by an on-call system administrator. The same system administrator knows how to provide continuity of critical applications you need – raid, failover cluster. Here’s an example – KissmetricsKissmetrics at the time I wrote this post (possibly analytics collection service worked). Guys from SemaphoreApp (picture) – are still happy to run client sites in Heroku, but migrated their service elsewhere. With their application it is not a problem. The problem is when you cannot pick up your stuff and go, because your data is in this particular application, and businesses cannot wait until you get the data from one provider and find a way to import them to another without loss. It is virtually impossible, and therefore makes you dependent on your provider.
See ScriptRock’s blog post – disruptions in 2012 at AWS, Azure, Skype, Google – companies with multi-million dollar budgets and thousands of employees. More recently in 2013 – GitHub and Office 365 with Outlook.com. People already calling it Office 364 after this incident.

- All the data is in my database – you can do any reports, integrate with other applications – no restrictions.

- Even if a vendor of the system does not promise to supply the source code in the event of business cessation, for some reason, we can always continue to use our web application. When you need to convert data into another application, if it is also not a web service, it is not a problem, because we have access to both databases. If the source code was not protected – even better, because we can make changes and possibly avoid migration to a new vendor.

Of course, there are SaaS that charge for usage. We pay Skype $16, use it and if we do not want to use Skype we will simply not pay any more. By the same principle, we created Nerrvana – no monthly subscription plans. We do not know how much testing time we will need this month and how much in the next, and so we assume that you do not know too. You can buy $50 worth of credit and use it in 3 days or 3 months. When it runs out – buy more credit or set up auto-recharge. Tests Nerrvana runs do not require major changes, which means you are free to leave at any time. There’s nothing holding you, and you do not pay for what you do not use. Both, Skype and our Nerrvana – SaaS. It is almost impossible to assume that you can buy, install and maintain such services yourself. They are both quite easy to replace if you are a business relying on them too. Project management, analytics, technical support systems, bug trackers, feedback collection, questions and answers portals – completely different. All of these systems can be downloadable and installable products.

Of course, if the company does not employ a system administrator, but needs a web service to operate and it is so good that you are willing to pay for it every month for as long as you will use it there is no alternative to SaaS. Somehow, it turned out that most companies do use them, and it is sad.

As you understand – we are primarily talking about things which lock you in and are hard to pick up and move. Moving a server from AWS is easier than an accounting system. If your site is for geeks, a simple loss of data will not be as critical as if you are tight with strict SLA with your clients and the clients may ask you to compensate for their losses.

As Steve Wozniak said: “With the cloud, you don’t own anything. You already signed it away. I want to feel that I own things. A lot of people feel, ‘Oh, everything is really on my computer’, but I say the more we transfer everything onto the web, onto the cloud, the less we’re going to have control over it.”

To summarize we would like to wish:

- SaaS vendors to adopt a standard of providing data export. Not API but the bulk data dump. It seems to us that the export must be in some kind of universal format, for example, XML. It can quite easily be imported into any modern database. We found a few good examples and it would be great if this approach will evolve into a standard for SaaS solutions.

- Downloadable web application vendors to ensure the provision of source code in the event of business termination or closure of the company or a change in business. Unfortunately it is hard to get going since there is no organization promoting this approach, which vendors can just mention they support. No one wants to sound pessimistic.

This will benefit customers but such openness is not yet achieved.

You can douse the fire and tell Sherlock Holmes to go clean his pipe – we finished our philosophical part and now further explain the idea of Startyco, where Answers is a first product.

So, when we were working on Nerrvana, being about half way in 2010, and not obviously knowing that it is only the middle, we wanted to make it easy for our users to collaborate and talk to us. We needed to find out what else we will need for our future users. By that time, we were well aware of the systems we needed, and wrote a series of articles – “Own Web analytics for startups” (the timestamp proves my point – 2010. We started building Nerrvana in 2008 and finished in 2012).

It was already clear that in addition to your product or service you at least need a marketing site, and then depending on the focus of your business, you’ll use a combination of the following systems – a blog (example WordPress), forums (example phpBB), customer support (example ZenDesk), ideas (example GetSatisfaction), questions/answers (example StackOverflow). Each company will use some combination of these systems, if you want your users to feel comfortable and important. It is true that in our articles we talked about web analytics only. It seemed to us, that it is impossible to obtain all the necessary data without integrating these systems and having access to the web analytics log files. Now we can say that our opinion has not changed. Log files access can be provided only by installing such systems on a server you can control. With SaaS solutions you do not have access to log files and even more – you cannot change the format, even if the logs will be provided to you. As a rule, you can add Google Analytics, which you cannot use to track individual visitors. It is even more complex when you use many different SaaS. In order to have a single session you not only need all systems to be on the same server but they should be pluggable. They should become a part of your system.

Logged in Nerrvana users can post feedback or answer questions, even view their own tickets in support system – no more registrations and separate logins. If it is not a Nerrvana user but just a visitor, it is natural to authorize via Twitter or GitHub. Such a visitor may, for example, ask a question on the Q & A site. That is, clients can do everything, visitors – not everything, and with the authorization through the social networks via oAuth, but still without registration.

We will illustrate our point with an example. Visitor (a person who does not have access to our application – www.nerrvana.com) may write an email to our support. Then, if he wants, he will visit the Answers (answers.nerrvana.com) and login via Twitter. If he used the same browser, it will allow us to relate its mailing address with his Twitter’s name. That is, in fact, the information is collected about all visitors and is stored in a single table in the database. For example, access for visitors and users can be expressed this way:

When a visitor registers in Nerrvana, his access is expanding, but we already know from where a user came from, what questions he or she asked using their Twitter or GitHub account. There is much more opportunity for analysis and, as a result, there is a chance to provide a better and more customized service to a particular client.

At first we went the simple way. We used our free time and realized that Ideas and Questions/Answers can be implemented inside the forum (we took phpBB). Then there will be only one step to integrate phpBB with Nerrvana. But if you see the admin panel in phpBB, you will realize that its integration with anything else is possible, but it will be a half-measure. It is too complex for what we need. We managed to implement two special types of forums in phpBB (type is specified when you create a new forum in the admin UI). One type – Ideas, the other – Questions and Answers. In Ideas forum, users can vote up or down, and leave comments. Each forum’s topic is a new idea. The administrator can set the status of the ideas – completed, in progress, duplicate, rejected. In Q/A forum each topic is a question and the posts in the topic are the answers. A person who posted a question (the questioner) can mark one of the posts as the answer. Voting for answers as in StackExchange is not implemented as we wanted to keep it simple. Finally we decided not to launch the Ideas and Questions/Answers in phpBB. It happened because we understood that it will be hard to support and customise.

We also postponed the blog and forum integration, as we had to create a forum and a blog that would be visited first. This is a task much more difficult than integration. The idea of a blog and a forum integration was that Nerrvana’s authorized users can comment in the blog without re-entering email. New blog posts will create a topic in the forum. Forum comments in this thread created automatically when a blog post is published will be visible in the blog on one side and the comments under the blog post will be visible in the forum. A year later, we saw a similar integration with WordPress in VanillaForums.

Around the same time, we needed to install a help desk system to support our big client in Australia. It was clear that we needed a single system to support clients for projects we do and for our own products we were going to launch. We published our requirements in a separate blog post. We looked at ZenDesk with “Multi-brand management”. To make a few portals for different products would cost us $99 a month for a single agent (the agent – the one who responds to requests from clients). We have four team members. Count yourself. 400$ per month. But even with this price tag we will end up with “Remote authentication/SSO” – users will log in with Nerrvana credentials, but they still need to enter a user name and password. We will not have access to web logs for our analytics as ZenDesk does not provide them and all the data is in their database. I will not repeat myself again; same problems. Finally we settled with Kayako as nothing better was found. It took a long time to set and customise, a lot of bugs, not built for easy integration – the user has access to the profile page in Kayako and can change the mailbox and password. There is a SSO option but how would you serve potential Nerrvana users who do not have a Nerrvana account yet? For some reason, every company considers its system to be in the center and do not even think that it can be made pluggable into another higher-level system. We managed to work around some problems, but again, the situation is painfully reminiscent of our attempts to integrate with phpBB.

Thus, our dissatisfaction with the existing state of affairs (as often happens) prompted us to create Startyco – a family of products that are not intended to be dominant, and were originally designed so that they can be easily integrated into your main application. That is, you select only those Startyco components, which are needed and plug them in one hour. Put custom styles so that the applications stylistically become a part of your main application and forget about it. That is – purchase, install, integrate and enjoy. Customers are happy, you do not waste time searching for solutions and integrations. Clients do not authenticate ten times, actively ask questions or talk to each other; the data is in your database. Since Startyco is not open source – bugs should be fixed promptly. You decide if you want to upgrade your Startyco products – check the features and if you can have access to free upgrades. Instead of paying 400$ per month for four staff, you pay 100$ once for as many staff as you need. Startyco Help Desk system, will certainly be the most difficult to implement, and the license will be more expensive than the Answers, but we think that the embedded products can compete with downloadable (Kayako) or services (ZenDesk) for ease of integration, and even functionality.

To fill the gaps between working for clients and creating Nerrvana, we created Answers. They do not have all the functionality we wanted but we are actively working on it. We seem to have achieved the ease of embedding. For example – it only took one hour to embed Nerrvana Answers (answers.nerrvana.com), including CSS customisation. You can register to Nerrvana, go to Answers and see that you also already have authorised in Answers. You can logout from Nerrvana, go to Answers and click ‘login’ – you will end up on the Nerrvana login page. After authorization in Nerrvana you will be redirected to Answers, because you came from there.

For now Answers can be integrated only when the main application is also written in PHP and if your main product is built with MySQL or PostgreSQL. To test, I integrated the Answers with WordPress (MySQL) and Mantis (using PostgreSQL). Given that I had to integrate with products I haven’t built – WordPress and Mantis, integration process took not more time when we integrated Answers with Nerrvana. We assume that it will take less than an hour if the development team which built your main site/service will plug Answers in!

In the next version we will add WYSYWIG, to make it possible to have basic formatting, inserting of code snippets into your posts, inline images and attachments. Also in the new version you will be able to set Answers as a standalone application with own registration and authorization. That is, you can simply create a niche questions and answers site, for example, for Robocode players, and not worrying that your passion will not be drawn in Area51 of StackExchange.

While post was finally finished we actually added three new modes to run your Questions & Answers site. You can now use Answers in four different modes:

- Plug-in (your web app + Answers)

- Plug-in + oAuth (your web app + Answers + participation from visitors logging from social networks)

- Standalone (your niche Q\A site with registrations within)

- Standalone + oAuth (your niche Q\A site with registrations within + participation from visitors logging from social networks)

We will show how it works in a separate post.

Soon after this update we will launch Ideas – customer feedback/ideas collection or roadmap shaper for your main product or application. Then, quite obviously, we will stop using UserVoice for Nerrvana and other products we will have by then.

Tell us what you think!

We decided to translate to English Michail Emel’yanikov’s post as we think it brings some importrant questions many people forget to ask. Did you?

“Over the past week, there seem to be more and more articles and notes about cloud computing – periodical influx of interest to any topic; nothing out of the ordinary. But after reading the latest material, I experienced a little déjà vu. All of this was written a year ago, if not more; worded the same way, and applicable to the same situations. On one hand, purveyors of cloud services try to convince us that their corporate clients are enthusiastically clambering into the clouds. On the other hand – all these years there has not been a single vivid answer to all of the simple, obvious questions. Namely:

1. What happens to the data when its uploaded to the cloud after the completion of any actions specified by the customer (editing, data processing, pressing the Delete key on the user’s computer, working with the cloud’s infrastructure)?

2. How are the access rights of each user maintained in the SaaS/IaaS/PaaS and other *aaS clouds. How was the reliability and safety of these mechanisms guaranteed, and who guaranteed it?

3. What’s with the virtual architecture inside the cloud, attacks on the hypervisor, super-users, and why don’t they just sell our information to our competitors who receive everything on demand right in the same cloud?

4. Who controls the encryption keys used to secure a connection to the cloud? Why isn’t it FSB/BND/Mossad/MI6/CIA (add more as you wish here)?

As you understand, there is practically an infinite amount of questions to be answered, and they are all related to confidentiality. But, as soon as you raise your head to look at those clouds, more questions start to arise. The list grows further:

5. Who exactly is responsible for your data being intact in the cloud? How is this demonstrated?

6. What will you do when you will lose the internet access?

7. What will you do when the provider won’t be able to serve you for one reason or another?

8. What will you do, when your data simply vanishes?

9. What will you do when you decide to find another provider, and migrate to them? How will you transfer your data, if at all?

All these years the cloud services industry has been talking about advantages – brainwashing the customers and denying us any answers. Instead it presents us with an eclectic brew – the universal description of the great experience and responsibility of the developers, about a large amount of specific security solutions which cannot be described now due to “a lack of time in this interview”. Besides, 95% of such interviews and presentations are marketing tales anyway. They are about the inevitable technological progress and the future of outsourcing everything noncore – nothing to do with the actual questions.

But the absence of answers to all the above listed questions made me return to my own post and presentation, created one year ago.

I found no differences when comparing this how things are happening today. For information technology and informational safety, a year is a large period of time, and if there is no reaction to all of these challenges, but there is noticeable growth in aggressive marketing, it is no accident. And I, as a security consultant, have a new question for cloud service providers. How and where customers can see security log events associated with their specific data? Do they have them? And what about targeted attacks, with all new Stuxnet, Flame, Gauss? Clouds are more appealing for hackers than individual sites.

Until these questions are not only answered, but also become a part of the contracts with providers – public clouds will mostly be used for consumer services like social networks, email, file and photo sharing. As for examples of international corporations who already migrated their IT infrastructure (names, for obvious reasons, are not disclosed by providers), we will have to believe the cloud companies statements. I used ‘public’ cloud term, because I still cannot get the difference between a private cloud and an ordinary commercial data center. I have never heard a distinct explanation describing the differences between them. Well, except for the system of payment for services provided, of course.”

Michail Emel’yanikov

Link to an original post in Russian

If Answers is a modern implementation of FAQ, where your users can ask questions and get answers, Ideas is your public roadmap channel, where you and your users can collaborate on your product’s or service’s future.

Administrators of your Ideas portal will be able to create idea statuses like – rejected, in development, implemented etc. and maintain your Ideas portal in an up to date state with them.

All planned new features – WYSIWYG, spam protection, mobile version, widget, oAuth authorization will be added simultaneously to Answers and Ideas.

No doubts it will be a single interface for all Startyco products our clients will buy and plug to the same core product. This is our goal; to create pluggable web apps with a single dashboard to manage them all.

For now we do not have many features (and we do not rush to implement them all), and this is how dashboard can look like to administer users:

We think this would be a better way to share permissions rather than manipulate user IDs from the database and edit a configuration file.

Here is a wireframe of the spam protection part of our dashboard which shows messages which were suspected as spam by, for example, SpamAssassin. You will decide how Startyco products will react, post them, and notify you or delay posting and notify. You will be able to ban and train your own instance of SpamAssassin to make it smarter.

We thought it will be quite important to be able to show up and down votes split. To keep the UI simple you will be able to see it when you hover your mouse over voting arrows or score. It makes sense to show a votes split when there are a few votes in different directions which also means it has to be at least two votes. Maybe we will show votes spit without hover when an answer’s score is 0 with equal amount of votes up and down (‘controversial answer’, I would say).

To let users see what they have voted for, and how they voted; this info will also be displayed. Here are some options I offered guys to look at and comment.

In our next product – ‘Ideas’, voting is essential and can’t be switched off. In here users leave feedback and voting will be only possible for an idea itself but not comments users will be able to leave to improve, clarify and criticize the idea. In here voting will have additional simple analytics you will be able to pre-configure and enjoy using. Once done, you and your team will be able to see the distribution of votes between, for example, guests, staff, demo users and paying clients. Hope you agree with us that an idea to add a feature with 20 votes from paying clients of yours requires more thought and attention than idea with 30 votes up from demo users and 10 votes down from your existing paying customers.

This is how it may look like:

Igor

We have been working on Starty.co Answers for quite a while, and we cannot stop adding new features and hone existing ones.
Finally we decided to release the first version of Answers without such a convenient thing as text markup, like BBCode. However, we did some preparatory work in this area and decided that in the near future it will include the TinyMCE  WYSYWIG and Highlight.js module to highlight code snippets.

Even though we decided not to include a full markup in version one, it was necessary to implement the basic things – to replace the line breaks with <br> and parse http(s):// links in the text and replace them with <a href=”"></a>.

After adding a new line to <br> translation, I started looking how smart people implement link parsing and replacement with <a href=”"></a> in the text. In the end I settled on this approach:

$string = preg_replace('@(https?://([-\w\.]+)+(/([\w/_\.]*(\?\S+)?(#\S+)?)?)?)@',
'<a href="$1">$1</a>',
$string);

But first, as usual, I looked if there was anything suitable on the php.net site. I came across this comment where simple rules are described for preg_replace() to process BBCode. And I thought of adding support of at least a few basic BBCode tags, but we were planning to add TinyMCE with HTML-markup! If I would implement a simple BBCode in the first version, then we will have to support it in a next version as someone will use BBCode and it will suddenly stop working. Or, during an upgrade from version one it will be necessary to look for and change the BBCode in the database. This is not good, I thought, and decided that with the same success I can quickly implement several popular html-tags.

At this point I started reading how to store and display text with html-markup. There are no problems storing it, actually; everything is clear. For databases, it does not matter what is inside the text.
The problem, of course, is with the output. On the one hand, there is a requirement to keep entered HTML-tags, on the other hand, we can’t allow XSS to be dragged in, or just not allow them to break the entire page layout.

You may ask: Why developers of TinyMCE, an advanced WYSIWYG-editor, do not take care of elimination of XSS from HTML code, generated as the output? After all, if we disable the ability to edit HTML code in TinyMCE – the user will not be able to push XSS through TinyMCE.
Unfortunately, we must remember that we cannot trust any data coming from the client. Switched off HTML mode can be enabled on the client side easily as it is JavaScript. And anyway, no one will prevent a hacker from POST-ing data – the server cannot distinguish who prepared the data in the request, TinyMCE or not.
Therefore, although TinyMCE developers did some stuff to prevent attacks, complete protection from XSS is not the task of the editor running on client side.

It would seem, PHP has the tools for XSS prevention – such as strip_tags() and htmlentities(). However, with custom text with HTML-markup they won’t help.
Suppose we want to implement even the simplest tag <b></b>, and parse a custom text user_msg this way:

user_msg_safe = strip_tags (user_msg, '<b>');

Nothing bad can happen right? Wrong. That’s a great discussion on stackoverflow. The following can occur:

<b style="width: expression(alert(document.location));"> XSS </b>

Yes, in most browsers, in all modern and popular browsers, such an attack may not lead to anything. But this is not a reason to relax. And this is just a <b> tag! We would like to implement the usual – <b>, <i>, <s>, <pre>, <img>, <a>!
<img>, for example, with its “src” attribute – simply terrible things can happen:

<img src="javascript:alert('XSS');" alt="" />

And even replacing or cutting substring “javascript:” from user_msg is not helping as

<IMG SRC="jav&#x0A;ascript:alert('XSS');">

has the same bad effect.

There’s a tonne of these examples. Yes, most of the vulnerabilities are closed in modern browsers, but even their diversity allows us to understand that processing of custom text with HTML-markup is not a trivial task.

What to do?
One option – use other markup languages – such as BBCode, Markdown, Wiki markup. With them it is much easier to build HTML-safe design. However they are still vulnerable to XSS.
The second option – still use HTML-markup, but pass user_msg through so-called “HTML sanitizer”. In particular, many folks at stackoverflow are recommending HTML Purifier.
It seems that the first pilot Answers version will not support markup language, but very soon we will return to this question, because understand how important this feature is.

References

- vulnerable tags and examples of XSS attacks
- an interesting scientific view at XSS (Russian), at the end of the article suggestions concerning http headers, which help to avoid XSS
- related questions (first, second, third) on StackOverflow
- HTML sanitizer  HTML Purifier
- HTML sanitizer PHP Input Filter
- more on sanitizers on StackOverflow
- XSS Cheat Sheet (used as a reference inside this post)
- vulnerability tags and XSS attacks examples (used as a reference inside this post)

Dmitry

P.S. While I was preparing my post, containing lots of HTML tags, for a publication in WordPress, I ran out of steam by trying to make it look correctly. Probably, we should seriously think about using an alternative markup designed specifically for text entered by the users themselves.