Excel: VBA

I needed to do stuff with strings in Excel, specifically, to get the initials from a full name (first name, last name, optional middle name), to make that the basis of an (non-unique) identifier. I had not previously written VBA (sure, there are other ways), so this was new, and dare I say, quite amusing.

This is an example of the input (B2 down) and the output (C2 down):

First point is how did I get the Column B data? I grabbed it from the Adventureworks example database (look elsewhere in the blog for that), and then ran this SQL over it:

 ;with names as 
 (select p.BusinessEntityID, p.FirstName + ' ' + ISNULL(p.MiddleName,'') + ' ' + p.LastName as FullName 
 from [AdventureWorks2016].[Person].[Person] p)
 select * from names
 order by BusinessEntityID

, which returns you this, for a very useful 3,000 rows or so of test/dummy data (btw):

Here is the code for the function that gets the initials from the full name:

But when I first opened  Excel for Office 365, I could see no way of editing vba. I happened to know I needed the Developer tab to be visible, and it is not visible, by default. The steps to enable the Developer tab are:

ExcelVba04

ExcelVba05

ExcelVba06

Once you have ticked the Developer check box, the Developer tab will appear, and you can then start editing (you will be prompted to enable macros, which you must accept):

ExcelVba07

Gist for the SQL, Gist for the VBA.

So going back to the first screenshot, the way you turn [Ken J Sánchez] into [KJS] is type [GetInitials(B2)] into the cell C2, then copy down. Now obviously I could have done this in a 1-liner, but that would have been ugly and hard to test. A bonus of this way is that you get a step-in debugger.


A further trivial example. Gist.

ExcelVba01

 

Advertisements

Azure ARM: naïve single VM deployment

The aim is to get a real simple VM deployment working using a json template, and in doing that to sacrifice any guidelines on authoring best practice, so we can focus on getting familiar with the nuts and bolts of ARM deployment.

Download the template and PowerShell helper from here.

Assume you need Windows 10 Anniversary edition PowerShell versions for these client/deployment actions.

Open a PowerShell admin prompt in the download location, and run [.\deploy.ps1].

You will be prompted for your Azure login, a resource group and location (e.g. “uksouth”), and an admin password for the VM.

If all goes fine, then you will be able to rdp to the VM, and be able to start up SSMS.

Finally, a couple of pictures I took to focus the mind when trying to reduce the template to the bare minimum to get a working VM:

The next day… in fact I have started to remove the literals as I get more familiar. Again, I refer you the MS Azure docs above.

SQL Server Management Studio (SSMS)

This is now a) free, b) no longer part of the SQL Server media. Get it here.

ssms01.PNG

(Right now WordPress image upload seems to have  a glitch where it uploads in reverse order of capture, so read these pictures bottom to top)

SqlServer: tally table performance


drop table #t;
create table #t (n int);
select 'Step 1', GETDATE();
;with [seed](n) as (
select 1 UNION ALL SELECT 1),
l01(n) as (
select 1 from [seed] a cross apply [seed] b),
l02(n) as (
select 1 from l01 a cross apply l01 b),
l03(n) as (
select 1 from l02 a cross apply l02 b),
l04(n) as (
select 1 from l03 a cross apply l03 b),
l05(n) as (
select 1 from l04 a cross apply l04 b),
myTally(n) as (select row_number() over (order by n) from l05)
select count(1) from myTally
where n < 10000000;
select 'Step 2', GETDATE();
-- that took about 5 seconds
;with [seed](n) as (
select 1 UNION ALL SELECT 1),
l01(n) as (
select 1 from [seed] a cross apply [seed] b),
l02(n) as (
select 1 from l01 a cross apply l01 b),
l03(n) as (
select 1 from l02 a cross apply l02 b),
l04(n) as (
select 1 from l03 a cross apply l03 b),
l05(n) as (
select 1 from l04 a cross apply l04 b),
myTally(n) as (select row_number() over (order by n) from l05)
insert into #t (n)
select n from myTally
where n < 10000000
-- that took over a minute (but not apples and apples)
select 'Step 3', GETDATE();

SqlTally01

SqlServer: grouping a date(time) column by month/year

Using AdventureWorks – search elsewhere in my blog for references to that.

The reference to the US locale is that I should be using an explicit conversion – this style is certainly bad practice, but not important for the example. The focus on the 141 value is to show that this was the smallest number of sales in a month/year. The grouping tells me this was for June 2011. So to prove my logic is right, I then select all the rows for June 2011… and they total 141. QED.

-- count sales for each month/year, ordering by 
-- highest sales in a month/year, descending

with salesByMonthYear as (
select 
 DATEPART(month,modifiedDate) salesDateMonth ,
 DATEPART(year,modifiedDate) salesDateYear ,
 count(1) SalesForThisMonthYear
from [AdventureWorks2014].[Sales].[SalesOrderDetail]
group by DATEPART(month,modifiedDate) ,
 DATEPART(year,modifiedDate) 
)
select * from salesByMonthYear
order by SalesForThisMonthYear desc

-- (assuming US locale... bad practice...)
SELECT *
 FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] where 
 ModifiedDate between '6/1/2011' and '06/30/2011'

SQL Injection: hex and blacklisting

I was given a list of SQL keywords. The idea was that a hacker trying to break the database via a UI or Fiddler/server would be defeated because that (black)list would contain every SQLServer keyword short of ‘select’ (that’s another story). So it would be something like (‘drop’,’delete’,’truncate’) etc. And of course the example assumes that there is no stored procedure with parameters, or ORM, or whilelisting to protect the database. Just effectively plain old unfiltered SQL hitting the database and being allowed to execute dynamic sql, with no parameter binding. So I was being asked to validate the list as being comprehensive.

Well, no blacklist in the world is going to be comprehensive enough, I think. That is because you can obfuscate your intention by passing a hex string as a parameter to e.g.

convert(varchar(max)...

or somesuch. (I guess you could blacklist [convert] etc.)

Anyway, pressing on with my (flawed 🙂 ) evil call…

Suppose you could pass this in your textbox or Fiddler call…

SqlInjection01

Well, you clearly couldn’t say it was harmless… as you just don’t know. In fact this is the result when executed against SQLServer 2014:

SqlInjection02

This is how I built the hex string:

SqlInjection03

, and this is the full picture:

SqlInjection04