Jonathon Bolster

I recently had to create a SQL function to convert latitudes and longitudes from one format to another. The source fields were varchar for each latitude and longitude value held in the format DD MM SS (degrees / minutes / seconds, separated by a space). This needed to be saved into a decimal format.

For clarity’s sake, there’s a lot of DECLARE statements:

IF OBJECT_ID (N'dbo.fnConvertDMSToDecimal', N'FN') IS NOT NULL
    DROP FUNCTION fnConvertDMSToDecimal;
GO
CREATE FUNCTION fnConvertDMSToDecimal(@dms varchar(max))
RETURNS decimal(18,16)
BEGIN
	DECLARE @firstSpaceLocation int = CHARINDEX (' ' ,@dms ,1)
	DECLARE @secondSpaceLocation int = CHARINDEX (' ' ,@dms ,@firstSpaceLocation+1)
 
	DECLARE @degrees decimal(18,15) = CAST(SUBSTRING(@dms , 1, @firstSpaceLocation-1) as real)
	DECLARE @minutes decimal(18,15) = CAST(SUBSTRING(@dms , @firstSpaceLocation+1, @secondSpaceLocation-@firstSpaceLocation) As real)
	DECLARE @seconds decimal(18,15) = CAST(SUBSTRING(@dms , @secondSpaceLocation+1, LEN(@dms) - @secondSpaceLocation-1) as real)
 
	DECLARE @bearing char = RIGHT(@dms, 1)
	DECLARE @multiplier int = 1
 
	IF @bearing = 'W' or @bearing = 'w' or @bearing = 'S' or @bearing = 's'
		SET @multiplier = -1
	
	return (@degrees + (((@seconds/60)+ @minutes) / 60)) * @multiplier
END
GO
 
print dbo.fnConvertDMSToDecimal('36 01 18S')

This is part of my idea to just write a post for all the code snippets I have locked away in gists (especially bookmarklets)

Usually, to pass security requirements we need to disable autocomplete on all the form fields which can get annoying during development.

Here’s a small feature to re-enable autocomplete on all forms and input fields:

[].forEach.call(document.querySelectorAll('form,input'), function(e){ e.removeAttribute('autocomplete')})

And here’s it in link form for clicking and dragging to the bookmarks toolbar: Enable autocomplete

Elsewhere, I had something that would raise a warning about form fields not having autocomplete disabled (hint: it’s more or less the same as above).

Apparently plans change quite a bit!

I am still in Australia and plan to stick around for a lot longer - currently waiting my Permanent Residence! So, my short holiday in Australia has turned into something much bigger.

This blog has definitely been a bit quiet over the last year but I have a load of gists with snippets of code I’ve done and want to write about.

Stay tuned.

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.

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