Linux Fu: Databases Are Next-Level File Systems

June 8, 2021 by Al Williams 63 Comments

It is funny how exotic computer technology eventually either fails or becomes commonplace. At one time, having more than one user on a computer at once was high tech, for example. Then there are things that didn’t catch on widely like vector display or content-addressable memory. The use of mass storage — especially disk drives — in computers, though has become very widespread. But at one time it was an exotic technique and wasn’t nearly as simple as it is today.

However, I’m surprised that the filesystem as we know it hasn’t changed much over the years. Sure, compared to, say, the 1960s we have a lot better functionality. And we have lots of improvements surrounding speed, encoding, encryption, compression, and so on. But the fundamental nature of how we store and access files in computer programs is stagnant. But it doesn’t have to be. We know of better ways to organize data, but for some reason, most of us don’t use them in our programs. Turns out, though, it is reasonably simple and I’m going to show you how with a toy application that might be the start of a database for the electronic components in my lab.

You could store a database like this in a comma-delimited file or using something like JSON. But I’m going to use a full-featured SQLite database to avoid having a heavy-weight database server and all the pain that entails. Is it going to replace the database behind the airline reservation system? No. But will it work for most of what you are likely to do? You bet. Continue reading “Linux Fu: Databases Are Next-Level File Systems” →

Get Your SQL Statements Right The First Time With SQL Lint

August 18, 2020 by Sven Gregori 11 Comments

What’s your average success rate of getting a SQL statement right on the first try? In best case, you botched a simple statement without side effects and just have to try again with correct syntax or remove that typo from a table name, but things can easily go wrong fast here. But don’t worry, the days of fixing it on the fly can be over, thanks to [Joe Reynolds] who wrote a linter for SQL.

A linter parses code to tell you where you screwed up. While checking SQL syntax itself is somewhat straightforward, [Joe]’s sql-lint tool will also check the semantics of it by looking up the actual database and performing sanity checks on it. Currently supporting PostgreSQL and MySQL, it can be either run on a single SQL file or a directory of files, or take input directly from the command line. Even better, it also integrates within your editor of choice — assuming it supports external plugins — and the documentation shows how to do that specifically for Vim.

If you can look past the fact that it’s written in TypeScript and consequentially results in a rather large executable (~40 MB), it might serve as an interesting starting point for the language itself, or adds a new perspective on writing this type of analyzer. And if databases aren’t your terrain, how about shell scripts?

Continue reading “Get Your SQL Statements Right The First Time With SQL Lint” →

The Bakery That Runs On Emacs

March 6, 2019 by Rich Hawkes 23 Comments

When it comes to managing ingredients and baking at a professional bakery, we know that most people would turn to an SQL database and emacs. Really, what else do you need? Okay, so maybe there are a few who would think that emacs couldn’t help you with this, so, here’s how [Piers] uses emacs and PostgresSQL to manage the day to day needs at his bakery.

[Piers] had tried a spreadsheet to keep track of things, but didn’t really like it when he had to create a new recipe: “lots of tedious copying, pasting and repetition of formulae” is how he put it. As a ex-professional programmer, [Piers] was familiar with emacs and so set up a daily worksheet in emacs using org-mode. Each morning he runs org-capture to create the template for the day’s work. Some code in the org file (run with org-babel) can run a query on the database. He’s created some code to set up each day’s journal entry and to run the complicated database queries that he needs.

There is a list of things that [Piers] is working on next, including ingredient order management and accounting, but it works for him. And to stop any potential flame wars that might break out, it’s good to mention that the system does just that: It works for him. There are other possibilities. Take a look at Al’s Editor Wars article, or Elliot’s rebuttal, or, ignore the wars and read this article on baking with steam.

Keeping Streets Ice-Free With The Raspberry Pi

April 17, 2018 by Tom Nardi 33 Comments

[Revanth Kailashnath] writes in to tell us about an interesting project he and his team have been working on for their “Real Time Embedded Programming” class at the University of Glasgow. Intended to combat the harsh and dangerous winters in Glasgow, their system uses a Raspberry Pi and a suite of sensors to automatically deploy a brine solution to streets and sidewalks. While the project is still only a proof of concept and hasn’t been deployed, the work the team has done so far runs the gamut from developing their own PCBs to creating a web-based user interface.

The core idea is simple. If the conditions are right for ice to form, spray salt water. Using salt water is a cheap and safe way of clearing and preventing ice as it simply drops the temperature at which water freezes. The end result is that the ice won’t form until it gets down to 10F (-12C) or so. Not a perfect solution, but it can definitely help. Of course, you don’t want to spray people with salt water as they pass by, so there’s a bit more to it than that.

Using the venerable DHT22 sensor the team can get the current temperature and humidity, which allows them to determine when it’s time to start spraying. But to prevent any wet and angry pedestrians, a HC-SR501 PIR motion sensor is used. If the system sees motion it will stop for a while to let the activity quiet down.

Monitoring the sensors and controlling the pump is done by a daemon written in C++, which also logs data to an SQL database, which in turn feeds their PHP web interface. In the video after the break, [Revanth] demonstrates how the system is constantly making decisions based on the input of the various sensors. Environmental data and motion is analysed every few seconds to provide a real-time solution.

We’ve covered a few projects aimed at melting ice and snow by heating concrete, but it’s interesting to see a “smart” approach to this common winter annoyance.

Continue reading “Keeping Streets Ice-Free With The Raspberry Pi” →

Little Bobby Tables Just Registered A Company…

December 29, 2016 by Jenny List 33 Comments

Sometimes along comes a tech story that diverges from our usual hardware subject matter yet which just begs to be shared with you because we think you will find it interesting and entertaining.

XKCD 327, Exploits of a Mom (CC BY-NC 2.5).

You will no doubt be familiar with the XKCD cartoon number 327, entitled “Exploits of a Mom”, but familiarly referred to as “[Bobby Tables]”. In it a teacher is ringing the mother of little [Robert’); DROP TABLE Students; –], whose name has caused the loss of a year’s student records due to a badly sanitized database input. We’ve all raised a chuckle at it, and the joke has appeared in other places such as an improbably long car license plate designed to erase speeding tickets.

It's nice to see that Companies House sanitise their database inputs. — It’s nice to see that Companies House sanitise their database inputs.

Today we have a new twist on the Bobby Tables gag, for someone has registered a British company with the name “; DROP TABLE “COMPANIES”;– LTD“. Amusingly the people at Companies House have allowed the registration to proceed, so either they get the joke too or they are unaware of the nuances of a basic SQL exploit. It’s likely that if this name leaves Her Majesty’s civil servants with egg on their faces it’ll be swiftly withdrawn, so if that turns out to be the case then at least we’ve preserved it with a screenshot.

Of course, the chances of such a simple and well-known exploit having any effect is minimal. There will always be poor software out there somewhere that contains badly sanitized inputs, but we would hope that a vulnerability more suited to 1996 would be vanishingly rare in 2016.

If by some chance you haven’t encountered it before we’d recommend you read about database input sanitization, someday it may save you from an embarrassing bit of code. Meanwhile we salute the owner and creator of this new company for giving us a laugh, and wish them every success in their venture.

Find The Source: WiFi Triangulation

May 29, 2016 by Gerrit Coetzee 80 Comments

[Michael] was playing with his ESP8266. Occasionally he would notice a WiFi access point come up with, what he described as, “a nasty name”. Perhaps curious about the kind of person who would have this sort of access point, or furious about the tarnishing of his formerly pure airspace, he decided to see if he could locate the router in question.

[Michael] built himself a warwalking machine. His ESP8266 went in along with a GPS module interfaced with a PIC micro controller. It was all housed in an off the shelf case with a keypad and OLED screen. He took his construction for a nice calming war walk around the neighborhood and came home with a nice pile of data to sort through. To save time, he placed the data in a SQL database and did the math using queries. After that it was a quick kludge to put together a website with the Google Maps API and some JavaScript to triangulate the computed results.

Sure enough, the person with the questionable WiFi access point shows up on the map.

Use Droid Bionic As A Mobile Hotspot Without Paying Extra

September 13, 2011 by Mike Szczys 46 Comments

Apparently Verizon customers are expected to pay for a second data plan if they want to be allowed to use a cellphone as a mobile hotspot. This means one data plan for the phone, and a second for the tethering. [DroidBionicRoot] thinks this is a little silly since there is already a data cap on the phone’s plan. But he’s found a way around it if you don’t mind rooting the phone to enable free tethering.

Not surprisingly it’s a very simple alteration. The phone is already capable of tethering, to enable the feature without Verizon’s permission just edit one database value. In the video after the break, [DroidBionicRoot] starts the process with a rooted Droid Bionic handset. He purchases an app for $2.99 which allows him to edit SQL databases on the handset. From there he navigates to the ‘Settings Storage’ database and changes the ‘entitlement_check’ key value to 0. Reboot the phone and tethering is now unlocked.

Continue reading “Use Droid Bionic As A Mobile Hotspot Without Paying Extra” →