Jonathon Bolster

web developer, programmer, geek

Where in the world am I?

I've been fairly quiet over the last few months with no blog posts and very little in the way of Twitter messages.

I'm in Australia! After resigning from my previous role, I picked up my passport and decided to venture half way across the world for a bit of travelling. So far I've been away from the UK for 4 months and in that time I've done a lot of amazing things - hand-feeding dolphins and diving in the Great Barrier Reef being among some of my favourites.

For the meantime, I've decided to try and gain some employment so that my passport pages can get a few more stamps before I return home! If you happen to know of any development work in the sunny country, please let me know. Otherwise, keep an eye on my github page to see what I'm up to.

Outputting an MSSQL database as a JSON string

Why would I want to do this at all? Surely this is madness, I hear you say!

I wanted to be evil for a little bit (side note: don't be evil) and I was playing with Troy Hunt's invitation to hack his site. Well, not his personal site but one he set up for a course he's running.

I noticed that there was a SQL injection attack on one of the pages and wanted to see just how much information about the database I could get from that.

Then I created this (well, it was slightly modified to hack Troy's site):

select '{database_name:"' + db_name() +'", tables: [' +
STUFF((
    SELECT ',{'+ti.tableInfo +'}'
    FROM
    (
        select 'table_name: "'+ t.name + '", columns: [' +
        STUFF(
                            (
                                SELECT  ',{column_name: "' + CAST(c.column_name AS VARCHAR) 
                                + '", type: "' + cast(c.data_type as varchar) 
                                + '", char_max_length: "' + cast(c.CHARACTER_MAXIMUM_LENGTH as varchar)
                                + '", char_octet_length: "' + cast(c.CHARACTER_OCTET_LENGTH as varchar) 
                                +'", nullable: '+ CASE c.is_nullable WHEN 'YES' THEN 'true' ELSE 'false' END + '}'
                                FROM information_schema.columns c
                                where c.table_name = t.name
                                FOR XML PATH('')
                            )
                        ,   1
                        ,   1
                        ,   ''
                    ) + ']' as [tableInfo]
        FROM sys.Tables t
    ) as ti
    FOR XML PATH('')
), 1, 1 ,'') + ']}'

What the above piece of code does is create a JSON version of your database schema. It uses some hackery of using STUFF a couple of times to pull information about the columns and the tables (well, I say hackery because it looked hideous but works).

A tiny bit of HTML, JS, and Knockout and I have a lovely little jsbin to describe the schema: http://jsbin.com/uZibesO/1

Of course, this was all a bit of learning for me and I don't actually intend to hack a website so just in case people didn't see the note above: DON'T BE EVIL.

I also recommend Troy's course on PluralSight (which goes with his 'Hack yourself first' site).

Grabbing image data from an external source to stuff into a canvas element

I was playing with Remy Sharp's collaborative drawing example after he posted it on Twitter.

Drew a couple of pictures and realised that I'm not as handy at drawing with the mouse as I used to be - so then I figured it would be better if I just put a random picture of a unicorn in. So I whacked open Developer Tools and tried to directly draw an image on the canvas element:

var img = document.createElement("img");
img.src = "http://somewhere-off-site.com/unicorn.jpg";
ctx.drawImage(img,0,0);

Can you see the problem? Pub-quiz points if you correctly guessed 'Cross Domain Security'. It was perfectly fine to chuck the picture into the canvas without a problem cropping up. The issue happens when the code tried to call 'getDataURL' for transmission. That's the no no.

So what did I do? I cheated a little bit - browsing to the picture I wanted, getting the dataURL and copying it across to the original browser window.

Voilá:

var canvas = document.createElement("canvas");
canvas.width = 50;
canvas.height= 50;

// Random image from Google Image search for unicorn - don't judge me!
var img = document.createElement("img");
img.src = "http://media.avclub.com/images/articles/article/62/62295/GLE_302-Sc9_2191_jpg_627x325_crop_upscale_q85.jpg";

var ctx = canvas.getContext("2d");
ctx.drawImage(img,-450,100);

var dataStr = canvas.toDataURL("image/png");
window.location=dataStr;

The window URL changes to give me my data URL (which I've put as the src attribute for the image tag below:

Base 64 encoded image of a girl from glee

Last thing to do is set the encoded data as the src rather than the external image URL in the code above:

var img = document.createElement("img");
img.src = "data:image/jpeg;base64,/9j/4AAQSkZJR....";
ctx.drawImage(img,0,0);

No more security problems - but we did cheat! It's a far from perfect solution (and far from perfect coding) but it works for what I wanted to do. I wanted to manually put external image data on to a canvas element and do it manually.

Finding values of XML data from a non XML column in MSSQL

I had a little bit of an issue at work when I had to create a query that returned values that were stored in an XML column. Normally you can use the XML value method to get these. My problem was that the column I wanted had XML stored in it but wasn't actually defined as an XML type.

This requires a conversion into XML. For anyone looking at the same problem, this is what I did

SELECT 
    tbl.ColumnName
    ref.value('FirstName[1]', 'nvarchar(100)') as [FirstName],
    ref.value('LastName[1]', 'nvarchar(100)') as [LastName],
FROM myTable tbl 
CROSS APPLY (SELECT CONVERT(xml, tbl.MyNonXmlCol)) as T(X)
CROSS APPLY T.X.nodes('//AuthorDetails[1]') R(ref)

Using MemberExpression to return a property name as a string

This is a snippet of code I used because I absolutely hate using magic strings in code.

We were using a third party tool (I'm looking at you, NHibernate Events) and it required the property name to be passed as a string. In comes this method!

    public static string PropertyName<T>(Expression<Func<T, object>> expression)
    {
        var body = expression.Body as MemberExpression;

        if (body == null)
        {
            body = ((UnaryExpression)expression.Body).Operand as MemberExpression;
        }

        return body.Member.Name;
    }

If you run it like PropertyName<MyType>(x=> x.MyPropertyName) then you get back MyPropertyName as a string.

Yes, I understand that this adds a little bit of complexity to what you're trying to do. However, we were running code that was constantly being changed and having the property name as a string in a random class had already caused some problems. This seemed like a better option.