|07-Jan-2014||Posted by Sonia Hamilton under 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)
|06-Jan-2014||Posted by Sonia Hamilton under MSSQL, MySQL|
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”.
- 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)
|24-Dec-2013||Posted by Sonia Hamilton under MSSQL|
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.
|22-Dec-2013||Posted by Sonia Hamilton under OSX, ovpn, tunnelblick|
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.
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).
|13-Jun-2012||Posted by Sonia Hamilton under Passwords, Security|
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?
- use good passwords generated by a password generator or tool
- 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 . 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.
-  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!