Jonathon Bolster

web developer, programmer, geek

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).