October 28, 2017

Google Sheets with SQL

The Google App Suite ( or G-Suite) of products is a powerful and free alternative to the traditional Office Suite and includes among others Docs, Sheets and Slides. Google Sheets offers full spreadsheet functionality and in this post and sample, we show how data stored in Google Sheets spreadsheets can be accessed with SQL. This post assumes that the reader is familar with standard spreadsheet terms like sheet, range and functions.

Google Script

The primary programming language used here is Google App Script, a javascript look alike that can be used to enhance and extend G-Suite products. Google script can also be interleaved with traditional HTML / CSS / JavaScript to create an elegant user interface as is shown in this example.
A Google Script project sits in the Google Drive just like any other Sheet, Doc or Slides and consists of code in a code.gs file along with html files for the interface. Google Script functionality can be built into other Google products but in this example, we build a web-app that can be called independently with its own URL or can be embedded into an iframe :

So how does one start with this new technology?

The developers guide to web apps is a good place to start and build a traditional Hello World application that creates and serves an HTML file. Then things turned a little difficult because it is not directly possible to make pure SQL calls from Google Script to Google Sheets.

A quick search through and post on Stackoverflow threw up two possible ways of addressing this problem and both these two approaches have been demonstrated in this application. However there were more challenges to overcome! How to navigate from one HTML page to another, how to store global variables that will carry data across functions and pages and how to embed the app inside an iframe. After sorting out all this the web app was up and running but it was looking rather clumsy and so there was a need for some cute CSS for the input form and the output report. But all this CSS had made the code almost unreadable and so there was a need to remove the CSS and Javascript code into external files and have them included in the HTML.

Before the app can be used it is nice to keep the following points in mind:
  1. For the googleViz route, the underlying spreadsheet must have public access, though this does not seem to be necessary for the query formula route
  2. For gViz, the name of the worksheet is adequate but for the query route the worksheet name followed by the cell range is necessary
  3. The spreadsheet document needs to be identified by the document ID that is a part of the URL that leads to the sheet. However the URL to the sheet can also be used by calling a different function that was tried but then commented out.
The source code for this web app -- with all script code, html, javascript and CSS -- is available in G-drive. You would need to login with your Gmail id to inspect it and use it if you like this approach.

Some known Issues :
  1. The worksheet names should not have spaces
  2. Sometimes, the last displayed report shows up instead of the new one. Reloading the page, fixes the problem 
  3. Multi-table joins are of course not supported! That is where you need a real RDBMS

October 04, 2017

Quantum Computers

My presentation on Quantum Computers at the Cypher 2017 conference in Bangalore on 21 September 2017

more information about Quantum Computers is available in an earlier blog post.

October 02, 2017

A Central Cyber Defence Authority for Digital India

The weekend of 8th - 10th July 2017 was a little different from most other weekends. First, late on Friday night, the Airtel network in the NCR region went down because of a data corruption in one of their critical computers. Then, and the exact time is not known, someone hacked, or illegally accessed, the Jio customer database, retrieved confidential identity data about customers, including phone and Aadhaar numbers, and published the same on a public website. Finally, and again the exact time is not known, something unpleasant happened to the National Stock Exchange computers so that when the market opened on Monday morning, nobody could trade for almost the entire day.

It is possible that these three events were independent, random events but as Goldfinger says, in Ian Fleming’s eponymous novel, “Once is happenstance. Twice is coincidence. The third time it's enemy action." So let us not have any delusions about incompetence or equipment malfunction. This was a cyber attack.

Who could the enemy be?

Obviously we do not know, as yet, but consider three more facts. First, this was when India was in a tense stand-off in the Sikkim sector of the Indo-Tibetan border where the Indian army had a significant situational advantage. Second, China has publicly warned India that 2017 will not be the same as 1962. Finally, in 2014, five officers of the the Chinese People’s Liberation Army Unit 61398, operating out a 12 storey building in the outskirts of Shanghai, were indicted by a US Federal grand jury “on charges of theft of confidential business information and intellectual property from U.S. commercial firms and of planting malware on their computers” leading to a tense stand-off between the United States and China over state-backed cyber espionage.

While it is true that no bullets, shells or rockets have been fired across the Himalayan border, at least not till this article was written, could it be that something else is being fired?

Most of us civilians, living far away from the LoC or International borders have not seen guns being fired in anger but thanks to television and social media coverage we have a fair idea of what happens there. But what does a cyber attack look like?   

Consider these two screenshots from two well known cyber security companies that show live cyber attacks in real time :

Watch live cyber attacks at : http://map.norsecorp.com/#/

Watch live cyber attacks at : https://cybermap.kaspersky.com/

This is only a small subset of actual cyber criminal activities that security companies can track and have chosen to make public -- like an excerpt from the register of FIRs that is maintained in every police thana in India. As in real life, most crimes are neither recorded nor publicized unless they reach epidemic or pandemic proportions like the WannaCry virus that disabled thousands of computers by encrypting critical data.

Now that we know how pervasive and ubiquitous cyber attacks are, what should we be doing to counter them? Some of us use anti-virus and anti-malware software on our personal machines and many technology savvy companies use firewalls to protect their internal networks -- that connect both users’ personal machines and company servers containing operational databases -- from hostile external access. But is this adequate?

While technology exists to stop almost every kind of cyber attack, not all end users have the knowledge, the ability and most importantly the determination to use it effectively. Consider a small or medium size company that uses a billing or financial accounting software. In the past, these would be on stand alone machines and hence inherently safe because it was “air gapped” -- or physically disconnected -- from the big, bad external world. But with more and more bills, invoices and money receipts being exchanged over mail this is no longer possible. So is the case with electronic filing of various tax returns and GST in particular. It is now impossible for any useful computer to be isolated from the internet and hence  be safe from hostile attacks from anyone, anywhere in the world. Are the computers that form the backbone of our central and state governments safe? Unfortunately, the answer is NO. So what if “non-state” hackers shut down the computers that control Power Grid’s electricity distribution network in India as was the case of the National Stock Exchange? The damage would be worse than a bomb exploding  in Howrah Station!

The challenge is less about ability and more about the attitude towards security. We know that our homes, offices and factories face threats from thieves and robbers but do we all learn martial arts and purchase guns? No, we hire security guards or outsource the security to specialised security agencies who have the expertise to handle thugs and thieves. Can our software programmers and IT staff not protect our computer systems? In principle they can, and in many companies they do keep hackers at bay but most software programmers, have expertise in a completely different area -- meeting customer and business requirements in an efficient and economical manner. Security for them is more often than not an afterthought, not the core competence. On the other hand, the durwan at the gate does not care two hoots about how and what is being produced in the factory but only knows that neither should anything go out or nor should anyone enter the premises without an approval from an authorised person. That security mindset lacking in most of our IT installations.

Which is why we have the police in towns, the CISF in factories and airports, the RPF at railway stations, the BSF and the ITBP on the borders and of course the Army as specialist agencies of the state whose only job is to ensure the security of our citizens, our factories, our infrastructure and hence of the country itself. Where is equivalent agency that guards our cyber assets? Critical machines in the GST network, the bank ATM network, the telephone network, computers that control the generation and distribution of power, computers that store Aadhaar and voter information are at the moment being guarded, if at all, by people who know little about cyber security and certainly do not have the “police” mindset that anticipates crime and thwarts threats. CERT-IN, the Indian Computer Emergency Response Team, under the Ministry of Electronics and Information is merely a technical body, not a security agency, whose responsibility is limited to collecting and disseminating information on threats and offering advice to anyone who chooses to listen. They do have the mandate to intervene during or, as is usually the case, after an attack but do not have the executive or operational responsibility to actually to prevent attacks, as is the case of the CISF or the BSF. The so called “cyber cells” of the metropolitan police are hardly any better -- all that they can do is track down mischief makers who put up politically inconvenient Facebook posts.

Going forward what we need is to separate the operational roles from security roles. Just as the security of an industrial plant is not the responsibility of the production manager but instead, is handled by a separate security department, so should be the case of security for our government installations. Those who operate IT systems should not have the additional responsibility of ensuring their security. This is not because local IT staff may not be competent enough, but because we need a consistent and comprehensive security stance at all possible threat points. It is not enough for some installations to be secure. Since all systems are interconnected, a breach anywhere is a threat everywhere and that is why we need consistent security everywhere. Hence the cyber security team should not be a part of the  local IT management but should be a part of a central organisation, the Central Cyber Defence Authority, CCDA -- analogous to CISF or BSF --  reporting directly into the security establishment in the Home Ministry.

In fact, CCDA should be an organisation on par with any other central security agency like CISF, CRPF, BSF, ITBP and like them should be headed by a person from a police, or crime prevention, background with a rank equivalent to that of the head of existing central forces. While CCDA should be responsible for government and public assets, private companies, unless they create their own separate cyber-security organisations, could outsource their cyber-security requirements to professional security companies, for whom this will be an additional line of business above and beyond their their normal fire and crime prevention services.

But while our security establishments, the Army, police, CISF, CRPF etc, may have the psychological mindset, the security stance, to anticipate criminal behaviour and prevent crime,  they would not have the technical skills to do so. Cybersecurity is not a part of the curriculum either at the Indian Military Academy or the National Police Academy and it is unlikely that it will ever be so. Even if some basic training is imparted, it will never have the technical depth required to defeat the sophisticated hacker. However the Manhattan Project, to build the atom bomb, was run by the US Army Corp of Engineers under General Leslie Groves but he had the best nuclear scientists like Robert Oppenheimer and Nobel Laureates like Richard Feynman working for him. So should be the case of the CCDA -- led by people from a police background, with an aptitude in computers and an interest in cyber security,  but staffed with people who have the deep technical knowledge, recruited laterally, or on lien,  from the IT industry.

Just as the CISF reports to the Home Ministry but is deployed in airports that report to the Aviation Ministry, the CCDA should report to the Home Ministry but should be deployed across all computer installations in all government departments, power generation and distribution companies and other critical utilities like roads, railways, telecom, ATMs. In these deployments, CCDA should be THE executive body, not be an advisory one and should have both the responsibility and the authority to ensure security. For example, it should be CCDA technicians who should have passwords for the firewall servers -- that protect government computers on, for example, the GST network  or the power transmission network -- and should be responsible for  configuring the security settings on the same.  This will be analogous to the CISF -- not DGCA, AAI or airline staff -- being the custodian of the door keys, frisking passengers and operating the X-ray scanners at the airport.

In fact, CCDA, like the Army, should also acquire offensive, or “Strike”, capabilities in addition to its professed defensive, or “Holding”, capabilities. Building offensive capabilities is a good way to test its own defenses and sometimes, offense is often the best form of defence!  

But unlike other central forces, the CCDA need not physically relocate its expert staff to distant locations even when it is deployed to protect dispersed digital assets. Just as the attacker can attack from anywhere in the world, so too can the defender protect from one or two central locations because all activity -- both offensive and defensive -- can and will be carried out over the same networks.

The HBO network was recently hacked by people who demand a multi-million dollar ransom in untraceable bitcoins to refrain from leaking episodes of the billion dollar Game of Thrones serial. What would happen if someone were to hold the Government of India to ransom with a similar hack? Just as we need to have the BSF jawan with his INSAS rifle at the LoC or the CISF jawan with his X-ray scanner at airports, we also need the CCDA jawan -- or in this case, the CCDA technician -- with his “hardened” firewall to stand guard on the digital assets that are connected to the web. The arrival of nuclear technology in the battlefield, led India to set up the Nuclear Command Authority. With the emergence of Digital India, we need the CCDA to protect the core digital assets that are critical for safety and security of the country.

Originally published in Swarajya, the magazine that reads India right!