Category: Other-Tech

Split() function for Microsoft MSSQL

Microsoft MSSQL doesn’t have a split() function (like in Ruby, Perl), so here’s one I wrote. It’s different from the other MSSQL split() functions out there on the intertubes in that it accepts a @pos parameter that specifies which split item to return – easier than trying to tangle with tables inside a calling function when you want the n’th split item.

CREATE FUNCTION [dbo].[split]
(
    @string varchar(MAX),
    @delimiter CHAR(1),
    @pos INT 
)
RETURNS varchar(255)
AS
BEGIN
    DECLARE @start INT, @end INT, @count INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string), @count = 1 
    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0 
            SET @end = LEN(@string) + 1 

        IF @count = @pos
            RETURN SUBSTRING(@string, @start, @end - @start)

        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        SET @count = @count + 1 

    END 
    RETURN '' -- not found
END

select dbo.split('aa,bb,cc', ',', 0)
select dbo.split('aa,bb,cc', ',', 1)
select dbo.split('aa,bb,cc', ',', 2)
select dbo.split('aa,bb,cc', ',', 3)
select dbo.split('aa,bb,cc', ',', 4)

--------------

                   # 0: zero length string
(1 row(s) affected)
--------------
aa                 # 1
(1 row(s) affected)
--------------
bb                 # 2
(1 row(s) affected)
--------------
cc                 # 3
(1 row(s) affected)
--------------
                   # 4: zero length string
(1 row(s) affected)

MySQL SUBSTRING_INDEX function on Microsoft MSSQL

I’m porting some code from MySQL to Microsoft MSSQL, and unfortunately MSSQL doesn’t have a SUBSTRING_INDEX function. Here’s a version I quickly implemented, called “my_substring_index”.

Limitations:

  • only accepts strings up to 255 chars long
  • only works with varchars – we speak ASCII here…
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

CREATE FUNCTION [dbo].[my_substring_index]
(@str VARCHAR (255), @delim VARCHAR (1), @count INT)
RETURNS VARCHAR (255)
AS
BEGIN
    DECLARE @result AS VARCHAR (255), @posn AS INT, @loop AS INT, 
			@found AS INT, @reversed AS INT;
    SET @loop = 0;
    SET @posn = -1;
    SET @found = 0;
    SET @reversed = 0;
    IF @count < 0
        BEGIN
            SET @reversed = 1;
            SET @count = @count * -1;
            SET @str = REVERSE(@str);
        END
    WHILE @loop < @count
        BEGIN
            SET @posn = charindex(@delim, @str, @posn + 1);
            IF @posn > 0
                SET @found = 1;
            ELSE
                IF @found = 1 AND @reversed = 0
                    RETURN @str; -- ie mimic mysql behaviour
                ELSE
                    IF @found = 1 AND @reversed = 1
                        RETURN REVERSE(@str); -- ie mimic mysql behaviour
            SET @loop = @loop + 1;
        END
    IF @posn >= 0 AND @reversed = 0
        RETURN SUBSTRING(@str, 0, @posn);
    ELSE
        IF @posn >= 0 AND @reversed = 1
            RETURN REVERSE(SUBSTRING(@str, 0, @posn));
    RETURN '';
END

SELECT dbo.my_substring_index('www.mysql.com', '.', -1)
--------------------------------------------------------
com
(1 row(s) affected)

Microsoft SQL 2012 SSIS Create Catalog Password

Creating an SSIS Catalogue on Microsoft SQL 2012 seems to have an interface bug – you need to both “enable CLR” and provide an “encryption password”, but the options are mutually exclusive ie when I tried to type in a password, the interface locks the password fields.

Maybe this was some sort of bad magic due to me coming over RDP to the new failing Server 2012 interface (I’m not using a tablet, so why force a tablet interface down my throat? Sort of reminds me of Ubuntu Unity). Anyway, the solution is to first enable CLR using SQL:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Then the password field will be enabled when creating a catalog.

OSX TunnelBlick Multiple OpenVPN Configurations error unable to copy

When setting up TunnelBlick, if you have multiple configurations in your ~/Desktop/foo.tblk folder, you’ll get an error in the console log like:

Unable to copy file foo to /var/private/bar…

The solution comes from the TunnelBlick forum:

There is a way to install multiple configurations all at once: use a .tblk folder that contains only .tblk folders inside it. So you create a .tblk for each configuration file, put a bunch of them into a folder, and rename the folder with a .tblk. When you double-click that “outer” .tblk, you should be asked once if you want to install “n” configurations.

For example:

cd ~/Desktop/foo.tblk
for f in * ; do
  g=`basename $f`
  mkdir $g.tblk
  mv $f $g.tblk
done

Also, the .ovpn files will have “com.apple.quarantine” xattr’s. This may or may not cause problems. I removed them in this way:

for f in * ; do
  echo $f
  xattr -d com.apple.quarantine $f
done

Also, to remove old private configuration files:

cd ~/Library/Application\ Support/Tunnelblick/Configurations
rm -rf *

(or, click on VPN icon top right of screen, select ‘VPN Details’, +/- in the ‘Configurations’ tab).

Change your passwords

I’ve written this post for the non-technical people I do “tech support” for.

Do you use the same password on different websites? Or, the same password with slight variations (for example secret-abc on website abc.com and secret-xyz on website xyz.com). Or, do you make your password “secure” (it’s not) by changing one of the letters for a number or symbol (eg secret becomes s3cret, password becomes p@ssword).

Well, you should change all your passwords right now! None of these methods are secure.

Why not? Hackers (crackers) are regularly breaking into public websites (recently LinkedIn, Last.Fm, Sony, Facebook) and stealing the encrypted password database. Unfortunately, due to computers getting faster and programming errors, hackers are easily “reverse engineer” these passwords, especially if you’ve used a word that appears in a dictionary (any dictionary – English, a foreign language, a slang word).

Well why is that a problem? Let’s imagine you are Fred Smith and you have a username/password fredsmith and s3cret on a website. A hacker breaks into this website, steals all the encrypted passwords. After about 6 hours they’ve cracked your password as it’s based on a dictionary word. The hacker will then automatically try this username/password (and variations) against lots of other websites. Your username/password on Facebook is fsmith and s3cret –  account stolen. Your username/password on Hotmail is fredsmith and secret! – account stolen. Etc, etc.

So what do I do?

Two steps:

  1. use good passwords generated by a password generator or tool
  2. store your passwords in some sort of “password vault”, and protect that vault really well

Using a password generator

Generating good passwords is hard – you need to make sure they contain upper and lower case letters, numbers, and symbols, and are long. But you want them to be easy to remember. You can take the first letters of a song or saying and convert it into a password (for example Sydney rains all the time I’m tired of getting wet becomes SrattItogw). But that’s hard work too – all those different websites to generate passwords for!

So don’t – use a password generation tool instead – for example Strong Password Generator [1]. Or Google for Password Generator. Don’t use sites like this this one that ask for the site name too – duh!

Using a Password Vault

Remembering all these passwords like iI&[-7;F&3@}$4R and T"7c3+-,t,3}}P@ is hard too!

So don't - use a password vault like Lastpass (and install it as a plugin in your browser: Chrome ($free), Firefox ($free), Internet Explorer ($costs)). Make sure your password vault is backed up and use a really strong password for  your vault. And even better, install Google Authenticator on your phone (Android, iPhone) and use it to protect your LastPass vault.

PS

  • [1] ideally you would install a password generator tool on your laptop, but a website is better than nothing
  • LastPass has a tool called “Security Check” – it will check all your passwords, look for duplicates, weak passwords, etc. Great!
Next Page »