Wednesday, September 7, 2011

PHP Framework benchmarking

For the past several months I have been in the process of creating a PHP framework. My framework, ZombiePHP, is nearing the point of release, and so I decided I ought to benchmark it against other popular frameworks to see how I stack up in terms of speed. I tested ZombiePHP, Symfony, Yii, CodeIgniter, CakePHP, and DooPHP.

All tests were performed on my laptop. Here are the specs:

Processor: intel i7 2.0Ghz
OS: Ubuntu 11.04
PHP: Version 5.3.5 with APC
Database: MySQL 5.1

My good friend, ab, performed the benchmarking. The same command was used for all benchmarks:

ab -t 30 -c 30 <url>

Test 1:

This test consists of unpacking the framework, getting a hello world application running, and checking the requests per second, stack size, and number of files included.

All of the frameworks, with the exception of DooPHP, came with a default layout. These were all included. This probably gives Doo a slight advantage in speed, but we won't worry about it for this benchmark.

The stack size and files included were checked at the time the view was rendered. These statistics were not calculated during the RPS benchmark.

The stack size and files included are just informational, however, the number of files included relates closely to the performance of the framework.

Hello world with default template

Stack Size Files Included RPS
DooPHP 6 13 4500
ZombiePHP 9 19 3060
CodeIgniter 6 22 2570
Yii 13 21 1080
CakePHP 8 38 520
Symfony 12 81 430

Test 2:

The second test required the framework to retrive the contents of a mysql table and display them on the page. The mysql table consisted of two rows and two columns.

I added the column below showing what percent slower the frameworks are when adding database access. This is kind of important considering that database access is a key element of any web application.

Symfony was the woe of my life for a previous job I had, so I was able to get it working in a few minutes due to previous experience. I does make me want to stick my head in boiling oil every time it wants to drop my database. Who's idea was that? Overall I don't like it.

CodeIgniter appeared to be simple, but the tutorial on the homepage DID NOT WORK. Seriously? Who has a flagship tutorial that doesn't work? I decided not to waste any more time with CodeIgniter.

CakePHP had a plethora of tutorials, but I couldn't find any on getting data from a database. Again, WTH?

Yii came with a nice tool called "Gii", which had all sorts of code generators. It took a minute to figure out and I had to change a bunch of permissions in the yii folder, but overall it was pretty easy and I didn't mind using it.

DooPHP made me do everything myself (no code generator), but has a very clever ORM which only took a couple minutes to figure out. The tutorial was a little vague, and maybe a bit incomplete, but I forgive them on account of it not being hard to figure out.

ZombiePHP is my baby, and it comes with database interaction as a default module, so that was pretty easy.

DB Access

Files Included RPS % Slower
DooPHP 16 3850 14%
ZombiePHP 24 2530 17%
Yii 63 390 63%
Symfony 138 281 34%
CodeIgniter How can your flagship
tutorial not work!?
CakePHP I could not find a simple tutorial
on getting data from a database.


Symfony is slower than a turtle out of Arkansas towing a sailboat up a mountatin. It includes a bajillion files to do anything. I also hate it bitterly and will smite it down in my wrath.....maybe not, but I really don't like it.

CakePHP seems OK, but documentation is lacking. The logo is cool, but doesn't make up for the lack of performance. I don't plan on revisiting it.

CodeIgniter seems like a really cool framework, but I don't have the time or desire to figure out how to use it without proper documentation. It's performance is up there, but I didn't get a DB access benchmark. If they update the tutorials and docs I might come back to it.

Yii, pretty cool. Not blazing fast, but it's still quick. I like the code generator and it's pretty lightweight. It suffered the most from adding database access, dropping 63% in performance. The yii homepage shows a benchmark where it is faster than any other framework. This is obviously false, and considering it's lack of performance with database access, irrelevant.

DooPHP is extremely fast. They aren't kidding when they say it might be the "fastest PHP framework available." Adding database access only slowed it by 14%, very impressive. Documentation isn't great, but not bad either. Will probably revisit this one.

ZombiePHP stood up pretty well to it's competition. It came in second for speed, only behind DooPHP.  It's still young, almost ready for public announcement, so currently there is zero documentation. Considering my frustration with these other frameworks lack of good documentation, I better put some time into that.

If you have some more info on any of these frameworks, would like to contribute to ZombiePHP, or want to see another benchmark up here, let me know.

Until next time,

Wednesday, August 31, 2011

Detecting mobile browsers in PHP

I was recently trying to find some code to detect if a visitor to my website was coming from a mobile browser so I could give a mobile friendly stylesheet, and after quite a bit of searching couldn't find anything for PHP.

I was able to find lists of mobile client user agent strings, but there are hundreds, and you don't want to be checking against hundreds of strings for every request, as it is slightly time consuming. After a bit more searching I came across this website, which gives a python algorithm for detecting mobile browsers.

The website gives a much smarter approach than checking every possible user agent. It checks for the most common mobile browsers, and if it doesn't find a match, checks against common desktop operating systems and web crawler user agents. If it still doesn't have a match it just assumes that it's a mobile browser. I used this algorithm and put together the following PHP code:


function is_mobile($user_agent, $default_to_mobile = true) {
   // Common mobile browsers
   $mobile_regex = "/iphone|ipod|blackberry|android|palm|windows ce|fennec/i";
   if (preg_match($mobile_regex, $user_agent) === 1)
      return true;

   // Desktop and bots
   $desktop_regex = "/windows|linux|os x|solaris|bsd|spider|crawl|slurp|bot/i";
   if (preg_match($desktop_regex, $user_agent) === 1)
      return false;
   // Assume it's an uncommon mobile browser
   // unless $default_to_mobile is false
   return $default_to_mobile;

So all that is needed to be done once the above code is included, is the following:

if (is_mobile($_SERVER['HTTP_USER_AGENT'])) {
   // use mobile template and stylesheet
} else {
   // use desktop template and stylesheet

Hopefully this saves you the time to track down an algorithm and write the code for it. Let me know if you find this useful or can suggest any improvements.

Until next time, peace.

Monday, February 28, 2011

The Zen of jQuery

A few months ago I decided to research jQuery. I had heard about it from time to time, and had often seen it as a job qualification, but didn't really know what it was. I am still somewhat new to it, but I can honestly say that it has done more for me as a web developer over the past few months than several years of writing JavaScript by hand.

jQuery is a JavaScript library that has predefined methods for document traversal, event handling, animating, and ajax handling. Code that would otherwise have taken hours to write, can now be done in a few minutes. In this post I want to give a brief introduction to jQuery and some of it's features. This is not meant to be comprehensive, nor in depth. It's intended to be just enough to jump in and get started.

Although it is good to be able to write the code by hand and to have an understanding of what is going on under the hood, in the end we want to write better applications faster and with more ease, and that is just what jQuery promises: "Write less, do more."

Getting Started

The first thing to do is include the library in the page, it can be anywhere in your html document, as long is it's before you use jQuery. It is just one big javascript file. You can download it from, or use the one hosted by google:
<script src=""></script>


The beginning of almost all jQuery is selectors. jQuery uses a shortcut called $ to make the code more concise. Selectors return an object or an array of DOM elements based on the string or object passed in. There are two main types of selectors, DOM elements and css. Using these we can attach events, animate, remove, or do any number of things with elements of the page.

Here is an example of a few selectors. There is one to select all div tags, one to select all with the class 'bar', another to select the div with id 'foo', and selectors for even, odd, first and last elements. Obviously there is more code at work here, this is just to show you how they work.

<div>content 0</div>
<div>content 1</div>
<div id="foo">content 2</div>
<div class="bar">content 3</div>
<div>content 4</div>
<div class="bar">content 5</div>
<div class="bar">content 6</div>
<div class="bar">content 7</div>
<div>content 8</div>

With the exception of the id and class selectors, those shown on the buttons won't work as they are. The odd selector, for example, would select every odd element in the entire document, starting with the body.  You would want to wrap the items you are wanting to select in a div with an id, and use the knowledge of css you already have: $("#id-of-div div:odd")


Once we have used a jQuery selector to get one or more items we have to do something with it. We can perform an action on it right away, or we can bind events to the selected elements, such as click or hover. In the example above I just showed the selector, but now lets attach an event to it. Let's say we have a list of items that we want to delete as they are clicked on. This turns out to be a trivial task. Let's take the following html code:

      <ol id="odds">
         <li>Mountain Dew</li>
         <li>Dr. Pepper</li>
         <li>Root Beer</li>
 And now we want to delete each item as it is clicked on. All we need to do is add this javascript:

$("#odds li").click(function() {

So let's go over this little script. The $("#odds li") selects all li elements within the element with id "odds".
The .click() function attaches an event to all elements retrieved that is fired whenever they are clicked on. It will attach a separate event for each distinct element. Remember that if this is called multiple times it will attach multiple events instead of replacing any previous events.
An anonymous function is passed into click(), this is the callback that the event fires. You can also pass in a function name.
Inside of the function is $(this) which refers to the object that triggered the event. We simply call the .remove() method and the element is deleted from the DOM.
Three lines of code and we are done.

Here it is in action:
  1. Mountain Dew
  2. Dr. Pepper
  3. Root Beer
  4. Sprite
  5. Sunkist
  6. Pepsi
  7. Coke

Now let's use some of the selectors we learned earlier, and make it a bit more fun. We can color the even rows one color, and the odd rows another. The problem that arises is that if we delete a row, then the even odd color scheme is broken, so we need to fix it after every delete. Let's update our script to look like this:

   function colorize() {
      $("#odds li:even").css({backgroundColor:"#bbf"});
      $("#odds li:odd").css({backgroundColor:"#fbb"});

   $("#odds li").click(function() {

Remember that jQuery is javascript, so we can use any javascript we want with it. Notice that I wrapped everything in the (document).ready function, this prevents anything from being executed until the page is loaded, if we didn't do this then the script would fail if called prior to where the html was placed. If we run this code we get the following effect:
  1. Mountain Dew
  2. Dr. Pepper
  3. Root Beer
  4. Sprite
  5. Sunkist
  6. Pepsi
  7. Coke

There are a number of other predefined events, such as hover, double click, change, focus, keyup, keydown, and dozens more. You can also define your own. They are all bound in the same way as you have just seen the click event bound with jQuery.


One of the best parts of jQuery is the effects that are built in. Here is an example of one of my favorites, the fade effect:

Click Me!

All that is needed to accomplish this is the following line of code:

$("#fade").click(function() { $(this).fadeOut() });

Another one of my favorite effects is the slide. To demonstrate this effect I created an expandable/collapsible tree structure. To build the tree, which represents a small file system, I used nested unordered lists. I set an event to toggle the children of an element when it is clicked on. Check it out:

  • /
    • /home
      • /root
      • /adam
      • /john
    • /etc
      • /init.d
        • apache
        • mail
        • mysql
        • postgresql
      • /apache
        • httpd.conf
      • /php5
        • php.ini
    • /var
      • /www
      • /log
      • /spool

It is noteworthy that I was able to accomplish that effect with only 4 lines of code. Seriously, it was exactly 4 lines. You can barely write a hello world program with 4 lines in most languages. I admit that I had a bug to work out, but jQuery's documentation is so complete that it took only a few minutes of research to get it working properly. I suspect that doing this with regular javascript would have taken a few hours to get the same effect, and a few more hours to get it working properly in internet explorer.


One of the best parts of jQuery is the cross-browser compliant, and very easy to use ajax library.

Because of security restrictions I can't demonstrate ajax within this blog post, but there isn't much visually to show, so I feel an explanation and some example code will be sufficient.
jQuery makes ajax trivially easy to do. Let's say I wanted to load the contents of a file hello.html into a div with the id load. It only takes one line of code to do this:


And that's it.

In spite of the convenience and simplicity of this method we usual want to do a bit more than this. There are several predefined ajax methods, but I usually just use the $.ajax() method. All of the other methods are built from this and it is very powerful and customizable. Let's start with a simple example, if we recreate the above example manually, it would looke something like this:

        success:function(data) {
        error:function(xhr, status) {
           $("#load").html("Error: " + status);

Generally for ajax interactions, as we do here, we call the ajax function and pass in a JSON object of the properties of the request. The above function call is a typical vanilla ajax request, but let's take a slightly more complex example. A while ago I wrote a function to retrieve a news feed for a webpage. It retrieves a JSON object and iterates through each item, adding them to the news feed.

function getNews(isFirst) {
   // retrive the news
           data:{"update" : (isFirst ? "0" : "1")},
           dataType: "json",
           success:function(data) {
                      news =;
                      for (var i = 0; i < news.length; ++i) {
                         addNews("<b>" + news[i].user + "</b><br />" +
news[i].news, isFirst);
           error:function(xmr, type, e) {
              addNews("ERROR: Could not retrive news: " + e, false);

It retrives the following json:

    "news" : [
            "news": "testing eaccelerator",
            "user": "adamb",
            "news_time": "2011-02-14 00:57:06.846938" 
        } ,
            "news": "apache up to almost 2000 rps!",
            "user": "adamb",
            "news_time": "2011-01-19 15:17:11.903671" 

In this example we used the data parameter to pass parameters in the url. We also specify that we are going to receive a JSON object. Usually this is difficult because we have to parse the JSON ourselves, which will require a lot of code and can present security hazards. jQuery does all of this for us, and parses the object for us. jQuery will also handle xml, jsonp, html, javascript, and text.

Go and Code

I hope that this brief introduction has been helpful. My intent was to give you just enough to jump right in and write some code. You will probably want to spend some time on, and read a few manuals.  Another exceptional tool that is built on jquery, is jquery UI, found at It has predefined methods for windows, date pickers, accordians, auto-completes, visual effects, and more.

Go now, and write some code. Become a jquery ninja, write less, do more.

Monday, February 14, 2011

A bit of benchmarking - PHP vs. Java vs. Python

Anyone who has ever taken a stroll down the interblag or spent time searching through internet forums knows that there is no shortage of religious wars being fought. Mac vs PC, Firefox vs IE (obvious winner here), Emacs vs vim, open source vs proprietary, I could go on and on.

One of the recent religious wars I have been a part of (more of an innocent bystander) is the PHP vs Java vs Python vs whatever for web development war. I hear people all over the internet saying that PHP is not scalable and that Java is so much faster for web development and Python is the best and ruby on rails will cure cancer and blah blah blah blah. As a PHP developer I naturally wondered if there was a benefit in switching to another language. While it is obvious that all the popular programming languages are scalable as a number of very large websites have been written in essentially all popular languages (queue angry comments from language fanatics), it is not entirely obvious which is the faster language.

Now almost any benchmark will show that Java has a faster execution time than PHP and Python, which is reasonable and expected. But so far I have not seen any benchmarks for PHP vs. Java vs. Python in a web environment. Also, every benchmark I have seen in this realm is using either an absurdly trivial program (such as hello world), or a program that favors one language over those it is compared to and makes special use of language features not present in every language.

Because of these issues, the bencmarks are completely useless. The process of getting a web page is almost unrelated to running a program from the command line. I want some benchmarks that are relevent to web programming!

Recently for a class I am taking in school I was required to write the same website twice using two languages: PHP and Java. And then for kicks I wrote it a third time using python. The website is a simple survey that will allow users to vote, and then display the results of the survey so far.

I decided that this would be a good opportunity to do some benchmarks, and see what the difference really is.

The Setup

The underlying code is fairly simple and representative of your average web page. All three programs follow a few simple steps in almost the same manner:
  1. Read in a file to open the page (html code)
  2. If the user hasn't voted display the survey (another file)
  3. If the user has voted display the survey results (query the database and process data)
  4. And finally read another file to close the page (more html)
These are operations that are typical of nearly every dynamic web page, so they shouldn't be biased to one languages features over another.

The data set from the database is very small (less than 150 records), and running them from the MySQL command line shows an execution time of 0.00 seconds. So the performance of MySQL won't sway the benchmarks of the languages. But I feel that it is important that we do make a database connection and process results, because that is one of the biggest tasks of web programming. The time that it takes to get the data from the database to the script is a crucial part of execution time. Typically webpages will only retrieve a small amount of data, even if the underlying dataset is huge, so this setup should be perfect.

I recognize that the architecture of the program is not the smartest way of doing it (and honestly is somewhat flawed) but it won't effect performance, so is not relevant for this test. I got an A on this assignment, so I don't care enough to go back and fix it.

The tests will be run on my basement server. Please don't laugh at the specs; I'm poor. Feel free to donate to the "buy Adam a better server fund" if you think this computer is inadequate for testing.

It is running a Pentium III at 930 Mhz with 512 Meg of RAM.
The operating system is Linux Mint (a Ubuntu variation).
All pages are being served through apache 2.2

Yes, my server is a horrible waste of space and should be sent to Estonia to be used as a paper weight by cave trolls, but it's what I have right now, deal with it. If someone has a real server that supports PHP and Java servlets and python, and has way too much time on their hands, please run these programs and benchmark them.

The setup for the languages goes as follows:

  • PHP
    • Connection: Apache with mod_php
    • PHP version: 5.2.6 with eAccelerator
    • MySQL Connector: internal driver
  • Java
    • Connection: tomcat6 behind apache with mod_jk
    • Version - Java: 1.6 OpenJDK
    • Version - Tomcat: 6.0.18
    • MySQL Connector: jdbc
  • Python
    • Connection: Apache with mod_python
    • Version: 2.6.2
    • MySQL Connector: MySQLdb

Our metric will be page requests per second. To test we will be timing a curl script that will request  the page 1000 times. We will run the test for the page that displays the survey, and again for the page that displays the survey results. I anticipate the survey results page will be significantly slower as it requires a database connection and processes data.

A Few Quick Notes

Before giving the results I should make note that these may be slightly biased towards PHP. I am not a great Java programmer and only recently have learned python. Before I got some help from the folks at stackoverflow the Java version was an order of magnitude slower than PHP and the Python was next to worthless. As far as I know I have worked out the serious performance issues with all of these, but I am posting the source so that someone with a bit more experience writing servlets or python can scrutinize my code and point out how to get some more speed from it.

Please do not spam me with a billion comments about how I can save 10 nanoseconds by standing on my head while eating a tangerine and chanting a magical java phrase. I also don't care about the design or elegance of the code, a lot of this was hacked together only to make it work. Elegance and design are topics for another day. I am mostly interested in significant performance issues, this is a benchmark test.

I am running the test from the same computer that is running apache, which has two main consequences:

  • The server resources will be used for the test.

    This means that we will not get as much performance as we could. I feel like this isn't an issue because all of the languages are put on even ground.

  • Network latency will not affect the test results.

    This is the real reason for testing from the server. The requests will go through the loopback interface, so it is still a network request, but doesn't go through the tubes. My server is sitting on a 1.5 Mb connection, which dies at about 20 requests per second, so it wouldn't work from the outside anyway.

It should be noted that these are hits to the server, not real page requests. No css, javascript, or images are being loaded. If we were load testing then these would be important, but we are testing the languages, not the server.

The java source code can be found here. My sweat and blood went into this. Seriously.

The python source can be found here. I didn't implement the code that sets the session or inserts to the database. It's not part of the benchmark so I didn't bother with it.

I don't think I need much assistance with the php code, but I posted it anyway. You can find it here.

The Results

Without further ado, here are the results:

Total Time (1000 requests)

Static Content Database Access
Python 3.78 seconds 6.25 seconds
Java 5.22 seconds 6.63 seconds
PHP 1.22 seconds 1.28 seconds

Requests Per Second

Static Content Database Access
Python 264 requests/sec 160 requests/sec
Java 191 requests/sec 150 requests/sec
PHP 819 requests/sec 718 requests/sec


I hear a lot of bad things about PHP, but according to these tests it blows java and python out of the water. PHP was 2 times faster than python and 3 times faster than java for static content, and about 5 times faster than either of them with database access.

It appears that because PHP was designed for a web environment it works much better in a web environment. Python and java can easily beat PHP for raw execution time, but they were not designed for the web, and as a result they have serious flaws in that environment.

There was a much smaller difference in the difference between PHP serving static content and accessing the database (13% slower), while python and java slowed down by 40% and 22%, respectively. Again, PHP is doing what it was born to do, the others are putting on a hat that doesn't fit them as well.

As I mentioned before I am much better at coding PHP, but I don't want to hear any whining about this fact until someone can point out problems with the python or java versions. I will be happy to rerun the tests with any suggested changes. So no whining!

While the main purpose of this has been to test the speeds of these languages in a web environment, but in the process I have learned a lot about the difficulty of writing in the three languages. Obviously the PHP version was very easy for me to write, but I didn't find the same for the other languages. For both of the others I more or less just copied the PHP over and converted it to the new language, which should be pretty straight forward.....

Writing the python code was surprisingly easy, but I could find little to no documentation for what I was doing. I spent almost as much time searching the net for how to connect to mysql as I spent coding the entire application. My first go at it was horribly slow, about 30 requests per second, and it took me a long time to figure out that request.write() is a very expensive method, which would have been nice to read in a manual, instead of benchmarking my code for over an hour. Overall I felt it was very easy to write the code, but I think I will stray away from mod_python due to the horrible lack of documentation. Perhaps I will check out django or another approach and see if it is a bit more friendly.

Writing the java version was an adventure, and resulted in me commiting suicide. Twice. There is bit more documentation for servelts than for mod_python, but it pales in comparison to PHP. Also the community appears to be smaller, with not as many code examples available. The bigger issue that I *hopefully* won't need to go through again was getting everything setup. I ended up installing two different versions of tomcat about twelve times each before I finally got it to even turn on and give me a welcome page, and then it took hours more to get mod_jk to finally work so I could get tomcat working on port 80, and then several hours more to get a hello world servlet going, and then hours more to find enough documentation to make a database request. I admit that I don't like java. I feel like they are holding me at gunpoint and forcing me to use "good design". I especially hate the exception handling, there's nothing like seeing a stack trace while you are looking at pictures of your cat (I wish I could have a cat in my apartment). But that's a topic for another day, I will try and stay off my soap box for now. A more legitimate complaint is that I get tired of compiling and waiting for tomcat to reload my servlet every time I realized that I forgot a <br /> tag or need to make a trivial change. When developing an application you make thousands of changes, and waiting 30 seconds between every single change gets very frustrating. I had considered switching to java for the supposed performance increase, but it appears to not exist, so forget that idea.

Obviously these tests don't suggest that Java or python are not fit for web development. Both languages have been proven to be good solutions in many cases. For me personally I think I will stick with PHP. It is very fast, and I find it to be very easy to develop with. Even more than the performance I appreciate a huge community and exceptional documentation. I think it is the best option for me.

Tuesday, December 7, 2010

Parameterize It!

My first experience with SQL was back in high school, although high school itself never taught me anything about it. It was really the school of hard knocks where I learned PHP and MySQL. Art, a student that was a year older than me had graduated, and as the new president of the computer club it was my responsibility to maintain the projects that Art had left behind. At that time the only thing I new about SQL injection was from a single line comment that Art put in one of his PHP programs:

// Escape those nasty characters
$input = addslashes($input);

If I had known the paramount importance of that line of code I would have spent hours researching it, but I had no idea what it was about.

My next job dealing with SQL came shortly thereafter when the science department of Snow College hired me to build their web pages while I was attending there. What I lacked in education I made up for with dumb luck, and somehow configured php to have magic quotes on. So all of my input was escaped right from the start, but any experienced php hacker will know that in spite of being amazingly convenient, magic quotes are a bad idea. 

Let me show you an example of why magic quotes will lull you into a false sense of security, and could lead to some dangerous SQL injection. Take the following code for example:


$id = $_GET['id'];
$query = "SELECT username
               , post_title
               , post_date
               , post
          FROM blog
          WHERE post_id = $id";

This seems harmless enough, if a number is used as the input, then the correct blog entry will be returned. Let's see what happens when we use the classic SQL injection attack to get every entry:'%20OR%20'1'='1

yields the query:

SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = \' OR \'1\'=\'1;

which will of course generate an error. The problem is that they can inject without using any quotes, for example:

SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = 1
OR 1=1;

Now you might be thinking "Wow, he just got all of my blog posts. I wish everyone would do that." But there are some serious dangers when someone can do something like this. The problem is that if a hacker can append something to the end of your query, he can append anything. I came up with the following injection: union select uid, username, password, 1 from users where username=0x61646D696E order by username

SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = 1
     , username
     , password
     , 1
FROM users
WHERE username=0x61646D696E
ORDER BY username;

This injection will retrieve the admin password from the database, and display it where the post date would have been. Notice the extra '1' in the select, as we need to match the number of columns as the first select. Or if there are too many in the select we are injecting we can use the concat() function to lower the number of columns.

You might be wondering about the 0x61646D696E in the where clause, which is an important addition. If magic quotes are on we cannot enter in a quote into the query, because it will be escaped and generate an error, but if we want to check for a certain username, such as 'admin', there is a workaround. As it happens you can turn any string into a hex code and pass that to mysql:

mysql> select hex('admin');
| hex('admin') |
| 61646D696E   | 

mysql> select 0x61646D696E;
| 0x61646D696E |
| admin        | 

But suppose we aren't after just one individual password, but we want to get as many from the site as possible, I propose a slightly more devious approach: union select max(uid), group_concat(binary concat_ws(0x2C, username, password) order by username separator 0x3C6272202F3E), max(1) from users

SELECT username
     , post_title
     , post_date
     , post
FROM blog
WHERE post_id = 1
                    CONCAT_WS(0x2C, username, password) 
                    ORDER BY username
                    SEPARATOR 0x3C6272202F3E) awesome
     , MAX(1)
     , MAX(1)
     , MAX(1)
FROM users
ORDER BY awesome;

This query will concatenate as many usernames and passwords as it can and print them out for your viewing pleasure. The max(1) must be used to pad the columns instead of 1 because group_concat is an aggregate function.

So it's obvious that magic quotes won't save you from the wrath of SQL injection, and may lull you into a false sense of security. My solution was a paramaterized SQL function.

Here is an example of what a query may look like:


$query = "SELECT username
               , post_title
               , post_date
               , post
          FROM blog
          WHERE post_id = :id";
$params = array("id" => $_GET['id']);
$result = $sql->exec($query, $params);


The exec function does all of the escaping and places the parameter for the post id in the correct place. I don't have to worry about a thing.

Leave a comment if you have a more devious injection attack than mine. I would love to see how create someone can get. Assume that the query engine only allows one query at a time.

Sunday, September 19, 2010

f1rst p0st

Hello World! I just decided to start a blog, I have no idea what I am going to put in here, but I'm sure it is going to be really exciting. More than likely this is going to be random rants about technology, linux, and how much I hate Microsoft. It's gonna be awesome!