Fun with MeekroDB: Using MySQL With PHP The Right Way

So, I’ve been doing a lot of PHP work lately. A lot in the respect that it has overwhelmingly become my main day job. Whilst the language has a lot of quirks that I don’t particularly like, I appreciate the fact that it allows you to get work done very, very quickly and getting a PHP web server set up is just a matter of running “apt-get install lampserver^” which is many magnitudes easier than setting up a Rails or Django server.

One issue with PHP is that working safely with a database can be extremely hard, dangerous and cumbersome. Particularly if you’re eager to avoid having your entire database compromised with an SQLi attack.  It’s also worth noting that when it comes to working with a database it often takes many lines of code just to perform a simple query securely. Gross.

So, here’s where MeekroDB comes in. It’s a single, self-contained library written in pure PHP that makes database interactions absolutely painless. It’s also rare, in the respect that its a fairly well documented, regularly maintained PHP library and the lead developer (a chap by the name of Sergey Tsalkov) is a rather nice guy indeed who is willing to respond to emails.

One of the unique selling points of MeekroDB is that it promises that SQL Injection is 100% impossible. Now, I’ve not independently verified this claim, but I’ve not seen any posts that claim otherwise. If anyone does find a vulnerability with it, however, please email me and I will post a correction.

Anyway, enough waxing lyrical about the delights of this awesome little library. Let’s start playing around with it.

I’m going to assume that you’ve downloaded the library from the website and you’ve copied MeekroDB into your project. Start off by setting it up to communicate with your installation of MySQL To do this, open it up with your text editor of choice (I like Sublime 2) and edit lines 22 to 27 with the correct settings for your database.

Once you’ve done that, you probably want to run a query. Suppose your database has a table called “Albums” and you want to select every album by The Verve Pipe?

Simple, right?

Alright, let’s try something a little bit harder (but only marginally so). Suppose you want to insert a new album into your database? Behold.

Want to remove any trace of your deep-seated love of Nickelback from your album collection? We understand.

I’m guessing you’ve worked out by now that Meekro DB is fairly easy to use. Cool, isn’t it? You can read a lot more about how to use Meekro on their comprehensive, well written, official documentation. And I must be honest, it is rather good indeed. I’m becoming a bit of an evangelist for this useful little library. Here’s why…  At the time of writing this blog post, only one of the top five results for PHP MySQL on Google mentions the need to mitigate against SQLi.

Yes, we’re teaching newcomers to PHP development (I count myself as a one of these newcomers, by the way) to just trust whatever input our applications are given. To not be concerned with security. This is largely because people can’t be bothered to write decent documentation.

Meekro has the advantage of being incredibly easy to use and being incredibly secure. Now, if that’s not the most awesome thing ever, I don’t know what is.

Mono, Linux and MySQL. Getting everything working nicely.

So, you’re a Linux guy who wants to make cool stuff with the Mono version of the .Net framework and MySQL?  Cool story bro. Sadly, there’s no ‘out of the box’ support for MySQL with Mono. That said, it’s reasonably easy to get it working. This blog post will run you through installing the MySQL DLLs so you can start hacking away.

Prerequisites

  • A brain.
  • Hands.
  • 20 minutes
  • A MySQL Server.

Got those? Awesome.

So, first off open up a terminal and download MonoDevelop. This will also download any and all dependencies, including Mono itself. In Ubuntu, it’s sudo apt-get install monodevelop. Depending on what distro you are using, you may be using a different package manager and you’ll use a somewhat different command.

Now go here and download the latest version of the MySQL .Net connector. Make sure you select the Mono version and not the Microsoft Windows one. Once you’ve got that, you’ll probably want to unzip it. Navigate to the directory where you downloaded the zip file and run unzip <name of file>. You’ll now have two folders called “V2” and “V4”. These contain all the libraries you’re going to have to put into your GAC (Global Assembly Cache).

Now, unfortunately you’re going to have to rename each library. Seriously. Whoever uploaded them didn’t exactly realize that this sort of thing is case sensitive. So, go ahead and rename them to the correct naming convention.

mv mysql.data.cf.dll MySql.Data.Cf.dll

As you can see, you’re changing “mysql” to “MySQL” with every other part of the filename that isn’t “.dll” having the first character uppercase. Makes sense? Good.

Right, now copy everything from “V2” into /usr/lib/mono/2.0 and everything from “V4” into /usr/lib/mono/4.0. Done? Cool. You’re almost finished. Now you get to put it into your Global Assembly Cache. This is handled by a rather nice utility called gacutil which should have been installed when you downloaded MonoDevelop.

Syntax is fairly simple here. It’s just gacutil -i <filename>. Do that for each DLL, and you’re sorted. Seriously, there’s nothing more to do now. Promise. Now, reward yourself by making something cool with MySQL and C#.