So everybody and their dog hopefully knows about SQL injection attacks these days. Most people should have also heard someone telling them that using prepared statements is the magic super fix to all of these issues. People slightly more in the know will have read that prepared statements lead to all sorts of issues. Some of which can be fixed with hacks (or eventually at the source). Some of which can only be solved of the source also exist of course. Some others can only be fixed with certain assumptions (like using the first set of data for generating the …

On my current project my team had to develop a portlet interface. Users can load portlets and organize them in multiple tabs with 3 columns per tab. They can reorganize the order of their tabs and move portlets within a tab an also move them to new tabs. Portlets are always placed at the top left when they get added or moved to a tab. Furthermore portlets and tabs can be removed, though the last delete operation can always be undone. All of this essentially required me to devise a plan for how to manage ordered lists inside an RDBMS.

Note that while this was written for MySQL (it makes heavy use of MySQL session user variables), I am using the Oracle style named placeholder support that PDO emulates for MySQL. So do not get confused by ":foo" in the SQL statements. This is just like the "?" you should know if you ever used prepared statements with MySQL. Furthermore I am using pseudo code control logic around the SQL. I think it should be …

Do they exist? Do they make you money (or in the case of Firebird meaningfully extend your community)? Would you be unhappy if support would be dropped in PHP 5.3, in PHP 6.0? Do you have ressources to prevent this from happening, by taking ownership of the code in question? Are you interested in ensuring the availability of solid support in PDO? While I do not think support will be dropped in PHP 5.3 (well for FrontBaseSQL I have a hard time standing up in defense like I did for the other 3), there is a good chance this will happen in PHP 6.0. Without PDO support your users will be left more or less in the dust. So if you work for one of these vendors, please talk to who ever who can prevent this. If you know …

There are only two kinds of storage devices - those that have failed, and those that are about to fail. That's the view most datacenters have about the traditionally mechanical devices pejoratively referred to as "spinning rust." All disk drives fail, cheap drives fail faster.

If the average time to fail is five years, you and your laptop can make do with the occasional backup. But when an average enterprise has 100, or 1,000, or increasingly 10,000 or 100,000 individual disk drives, failure is a daily, if not hourly occurrence. Mechanical devices fail.

And with failure comes the potential for losing data - using commodity disks to save your boss $500,000 does her no good if she's fined $50,000,000 for violating data retention regulations. Stock transactions, medical images or feature length movies - take your pick, some data has to be perfect. Not a decimal point or pixel out of place.

That's exactly why, years …

This is going to be an unusual blog post, because I will continuously update it with features that MySQL still has on top of PostgreSQL, which is generally considered to be more feature rich. Some of these missing features can however hurt a lot. I am including MySQL 5.1 in here, since eventhough its not yet released as GA, more and more people have started to use it in production. At the same time I am also including 8.4. So in a way I am talking about what MySQL has on top of PostgreSQL by the end of the year. I am not going to include stuff like auto increment if there is something that is more or less equivalent with SERIAL. I am also not including features I consider unwise (like REPLACE). Finally I am skipping XML support, because I know both are working on improving the support, but I have not yet looked at it so closely. So here it goes:

• Multiple charsets/collations in the same DB (all the way down to the column level)
…

De facto standards are the only ones that matter.

That's a bit of a truism in the technology world - well intentioned standards bodies and departments of justice can do their best, but at the end of the day, volume deployment is the only setter of standards. Ubiquity trumps policy, just about every time.

To that point, I was on a panel recently, discussing the impact of technology on the world's more rapidly developing economies (what's often referred to as "BRICA," or Brazil, Russia, India, China and Africa).

One of the speakers referenced an interesting shift in the traditional media industry: western companies were turning their attention toward the developing world. GDP growth wasn't drawing their attention - as much as demographics. Teenagers and those in their early twenties represent the biggest media buyers in …

PostgreSQL is a sleeping giant that is waking up. And instead of wondering around sleepy, they seemed to be jumping forwards in what seems leaps to the other guys, but are just natural steps for them. Heh, I seem to be in a dramatic poetic mood today, but I just wanted to get across with how impressed I am with what is going on with the PostgreSQL community, ever since version 8.0. To me PostgreSQL 8.0 was so critical since with it one of the key obstacles to more wide spread adoption was removed: There was finally a native easy to install version of PostgreSQL for windows. Not that I know many people that deploy on windows, but I do know a ton that develop on windows (which included me back then).

Anyways, since then they have been adding more and more features at a pace that is mind boggling compared to what MySQL seems to be doing with much greater ressources. They are opening up as the same time too, which only seems to be accelerating …

A few years ago a backdoor was found in Firebird, the open source fork of Interbase, that already existed in the original Interbase product and was still in the version of Interbase that was sold at the time. Nowadays this is fixed, but it was kind of scary that a company would add a backdoor and then totally forget about it, why else would they not have removed it before open sourcing (after all a backdoor only works through obscurity)? Anyways the other day another security issue (this sort of thing happens to the best of them) in Interbase that was fixed in January in Firebird already.

The security issue has been long disclosed. Now why on earth would the Interbase …

Last week I was working with a client that rediscovered a bug where setting expire_logs_days and issuing a flush logs causes the server to crash. It’s MySQL Bug #17733 if you want to have a look. Seeing MySQL crash was enough inspiration to fix something that I and others have wanted to fix in MySQL for years.

Currently a flush logs command tries to flush all of the following logs in order:

• General Log
• Slow Query Log
• Binary Log
• Relay Log
• Store Engine Logs (If available)
• Error Log

The reason I wanted to fix this is because my client was issuing a flush logs to rotate the error log on a server with no replication. The crash was caused by replication. With individual flush logs it’s less likely for this to happen again in the future. People can simply issue a query for the …

So today Roman, one of the developers on the Doctrine project, pointed me to an article that was discussing the misuse of DISTINCT. This article went so far as to say that "A SELECT DISTINCT query is frequently a "code smell". The article pointed to another article hosted at onlamp.com. That author was advocating the use of subqueries to more efficiently filter out redundant rows. Immediately I began to wonder if this is really a feasible approach since MySQL's subquery handling is very slow. I could imagine this being faster on RDBMS where the subquery implementation is more mature. That being said comments in the onlamp.com article point to the fact that even on Oracle things get slower with the …