Somebody asked whether it’s a good idea to encrypt database columns in a web application. My answer: “It probably won’t help much.” Why? Figure out your threat model Best practice: figure out your threat model before you spend time and money securing your system. If you build complex security measures without a clear idea of your threat model, you’ll trick… Read more →
Category: MySQL
Time Zones in MySQL
Suppose you’re working on a web application to support users in multiple time zones. How do you handle dates and times? If your users all live in one time zone, it’s easy. Simply store important date and time data in DATETIME or TIMESTAMP columns, and be done with it. But, what application has users in just one time zone? Maybe a municipal… Read more →
Test data for FULLTEXT searching
MySQL’s FULLTEXT search feature works best when it’s used on a large corpus of text. That is, for best results you need many rows. When the text corpus is too small, full text searching often returns strange results: the indexing process tries to work out common words. If the amount of text being indexed is too small, some words that… Read more →
Sample text in many languages
Have you been around long enough to remember the Kermit terminal program? It was a very important way of getting online in the late 1980s and early 1990s. Frank da Cruz, Kermit’s creator, also has an interest in world languages and Unicode. He put together a terrific web page containing samples of text in many languages. If you’re looking for short… Read more →
Unique Numbers in Oracle and MySQL
It’s common in database work to need unique integers. They get used in id columns in tables, and for other purposes. This note compares and contrasts the Oracle and MySQL approaches to doing this. MySQL handles this need with autoincrement columns, in tables and Oracle handles it with database objects called sequences. In MySQL, when you have an autoincrement column… Read more →
Fast nearest-location finder for SQL (MySQL, PostgreSQL, SQL Server)
I’ve spent enough time goofing around with location-finder software that it’s worth writing up how to do it. Of course, finding distances on the surface of the earth means using Great Circle distances, worked out with the Haversine formula, also called the Spherical Cosine Law formula. The problem is this: Given a table of locations with latitudes and longitudes,… Read more →
Computing an initial bearing
This MySQL Stored Function computes the initial bearing — the compass heading to follow — when moving from one point to another on the surface of the earth. Here’s a web site describing this. http://www.movable-type.co.uk/scripts/latlong.html Read more →
Filling in missing data with sequences of cardinal integers
When you’re extracting summaries of information from a database, sometimes rows are missing. For example, if you are keeping track of number of customers by day, you might use a query like this: View the code on Gist. You might get a result set like this: 2014-03-07 122 2014-03-08 355 2014-03-10 234 2014-03-11 119 Notice that the row for 2014-03-09… Read more →
WordPress finally starts to retire the old php / mysql extension
Finally! Automattic and WordPress are starting to retire the old mysql data-access API. This is going to foul up WordPress themes and plugins that use those APIs. It shouldn’t be hard to switch. For a while there’s been a global instance of the WPDB class that developers can use. Gary Pendergast has a good article on what to do. Code… Read more →
Numerical Data Types Matter
Check this out: Disasters resulting from bad numerical computing. Be careful when messing around with FLOAT, DOUBLE, and INTEGER data. For want of a bit … Read more →