Archive

Archive for the ‘Uncategorized’ Category

Generate large random dataset in SQL server

_89716241_thinkstockphotos-523060154

If you would like to learn about SQL server indexes, and SQL server performance in general, but can’t get your hands on a large dataset that really tests the limits of your SQL server, then here is a script that generates a huge dataset, based on random data.

create table Test1
(
id int identity(1,1),
guid varchar(36) default newid(),
dateCreated datetime default getdate(),
number float default rand()
)

while 1=1
Begin
insert into Test1 default values
end

You just let it run for as long as you like, then stop it whenever you have enough rows.

 

Categories: Uncategorized

Regular expression #CLR #UDF in SQL server

1_zW_Q1yUS4BJNVL6ibA6Q5Q

You can do very basic string matching using the LIKE operator in SQL server, but you quickly hit the limits of what you can easily do using the simple operations supported. .NET offers full Regex (Regular expression) support, and you can use this within SQL server by creating a UDF CLR (User defined function).

TL;DR; here is the Github repo: https://github.com/infiniteloopltd/SQLServerRegex

Which defines this one function;

using System.Data.SqlTypes;
using RX = System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Regex(string input, string regex)
{
var match = RX.Regex.Match(input, regex).Groups[1].Value;
return new SqlString (match);
}
}

Which you can install into your SQL server database as follows;

EXEC sp_changedbowner ‘sa’
ALTER DATABASE [<your database here>] SET trustworthy ON

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO

sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

CREATE ASSEMBLY [DbRegex] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030079D4F85D0000000000000000E00022200B0130000008000000060000000000003A260000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000E82500004F00000000400000A002000000000000000000000000000000000000006000000C000000B02400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000040060000002000000008000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E000000000000000000000000000040000042000000000000000000000000000000001C2600000000000048000000020005008C200000240400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002002400000001000011000203280500000A6F0600000A176F0700000A6F0800000A0A06730900000A0B2B00072A2202280A00000A002A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000080010000237E0000EC010000AC01000023537472696E6773000000009803000004000000235553009C030000100000002347554944000000AC0300007800000023426C6F620000000000000002000001471502000900000000FA013300160000010000000C0000000200000002000000020000000A000000040000000100000001000000030000000000A800010000000000060050000E01060070000E0106002700FB000F002E01000006009101BD000A003B00DA000A0098003D010E00A00152010E00A20052010E00C40052010E00D40052010E001F005201000000000100000000000100010001001000710100001500010001005020000000009600A0013A0001008020000000008618F5000600030000000100980100000200A6010900F50001001100F50006001900F5000A003100F50006004100A2001600490086011D005100B400220061008E0028003900F5002C002900F50006002000230072002E000B0041002E0013004A002E001B00690010000480000000000000000000000000000000009E010000040000000000000000000000310016000000000004000000000000000000000031000A00000000000400000000000000000000003100BD00000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200436170747572650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053716C537472696E67004D6174636800446252656765782E646C6C006765745F4974656D0053797374656D0047726F7570436F6C6C656374696F6E0047726F7570004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E546578742E526567756C617245787072657373696F6E730055736572446566696E656446756E6374696F6E73006765745F47726F757073004F626A65637400696E707574004462526567657800726567657800000000000F4DE26091FA75478EA1FCAB7323A50F000420010108032000010520010111110507020E111D06000212250E0E0420001229052001122D080320000E042001010E08B77A5C561934E089060002111D0E0E0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000079D4F85D00000000020000001C010000CC240000CC06000052534453DAF00DA62F5BD1478B583CA3D74FE38B01000000433A5C55736572735C46696163685C736F757263655C7265706F735C446252656765785C446252656765785C6F626A5C44656275675C446252656765782E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001026000000000000000000002A2600000020000000000000000000000000000000000000000000001C260000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000C00010049006E007400650072006E0061006C004E0061006D006500000044006200520065006700650078002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000044006200520065006700650078002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C0000003C3600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE

CREATE FUNCTION [dbo].[regex] (@text [nvarchar](MAX), @regex [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]

Once that’s done, then you can run SQL commands like;

select dbo.regex(‘1234abc123′,’\d+([a-z]+)\d+’)

 

Categories: Uncategorized

#Transliterate #Greek with Microsoft #SQL User Defined Function

9c347bfd4a0924bdf7f238fe78050eca

My disclaimer is that I don’t speak greek, and this code is far from a perfect transliteration from english to greek, but it’s a good starting point I guess.

It transliterates English to Greek; in a SQL UDF

create function Transliterate (@english nvarchar(max))
returns nvarchar(max)
as
begin
set @english = replace(@english,N’th’,N’θ’)
set @english = replace(@english,N’ch’,N’χ’)
set @english = replace(@english,N’ps’,N’ψ’)
set @english = replace(@english,N’q’,N’κο’)
set @english = replace(@english,N’w’,N’ου’)
set @english = replace(@english,N’a’,N’α’)
set @english = replace(@english,N’b’,N’β’)
set @english = replace(@english,N’c’,N’χ’)
set @english = replace(@english,N’v’,N’β’)
set @english = replace(@english,N’g’,N’γ’)
set @english = replace(@english,N’d’,N’δ’)
set @english = replace(@english,N’e’,N’ε’)
set @english = replace(@english,N’z’,N’ζ’)
set @english = replace(@english,N’i’,N’η’)
set @english = replace(@english,N’k’,N’κ’)
set @english = replace(@english,N’l’,N’λ’)
set @english = replace(@english,N’m’,N’μ’)
set @english = replace(@english,N’n’,N’ν’)
set @english = replace(@english,N’x’,N’ξ’)
set @english = replace(@english,N’o’,N’ο’)
set @english = replace(@english,N’p’,N’π’)
set @english = replace(@english,N’r’,N’ρ’)
set @english = replace(@english,N’s’,N’σ’)
set @english = replace(@english,N’t’,N’τ’)
set @english = replace(@english,N’y’,N’υ’)
set @english = replace(@english,N’h’,N”)
set @english = replace(@english,N’j’,N’ι’)
set @english = replace(@english,N’f’,N’φ’)
set @english = replace(@english,N’u’,N’υ’)
return @english
end

 

Categories: Uncategorized

Accessing #AWS #CLI from #SQL Server Agent

FUNGu

The AWS CLI allows you to interact with AWS from the command line, and an obvious use of the tool, is to upload your Database Backups to S3, to keep them safe.

Once you’ve configured AWS to run from the command line on your server, and tested it, then you try the exact same command from xp_cmdshell and it fails with “unable to locate credentials“.

What is happening here, is your remote desktop user, is probably “Adminstrator” but SQL server agent runs under the username MSSQLSERVER

You can verify the username used by SQL server agent by runing the command

xp_cmdshell ‘echo %username%’

The AWS CLI is looking for the configuration file under c:\users\MSSQLSERVER\.aws – but the configuration file is actually under c:\users\Adminstrator\.aws , so you need to copy the configuration file using xp_cmdshell as follows

  1. Using remote desktop, copy the configuration file to an accessible folder, like C:\temp
  2. xp_cmdshell ‘md C:\users\MSSQLSERVER\.aws’

    xp_cmdshell ‘copy c:\temp\credentials c:\users\MSSQLSERVER\.aws’

     

  3. And then test with
    xp_cmdshell ‘”C:\Program Files\Amazon\AWSCLI\bin\aws.exe” s3 ls’
Categories: Uncategorized

Call an #API from within #GoogleSheets

demo

Google sheets is an amazing product, but let’s imagine, you’d like to expand upon Google Sheets to fill in the details of a cell by calling an API.

In this example, we’re using an API that determines the make and model of a car from it’s license plate, If you’d like to follow along, then you can create your own free account at https://www.regcheck.org.uk  – The username and password for this particular API have been removed from the demo code, you’ll need to use your own.

So, Open Google Sheets, and Press Tools -> Script Editor, then enter the following script;

function RegCheck(RegistrationNumber) {
var encode = Utilities.base64Encode(‘***USERNAME***:***PASSWORD***’, Utilities.Charset.UTF_8);
var option = {
headers : {
Authorization: “Basic “+ encode
}
}

var url = “https://www.regcheck.org.uk/api/json.aspx/Check/&#8221; + RegistrationNumber;
var response = UrlFetchApp.fetch(url, option).getContentText()
response = JSON.parse(response);

return response.Description;
}

What this does, is that it first creates the basic authenticatio header from your username and password, which you used to register with the API. And then passes the registration Number into the URL.

Once the URL returns, we extract the Description from the JSON returned, and return it back to Google Sheets.

Now, In google Sheets, all you need to do is enter =RegCheck(“xxxxx”) in a cell in order to call the API.  Where “RegCheck” is the function name

Of course, you can use the same approach to call any API you wanted.

 

 

Categories: Uncategorized

Generate a 1×1 pixel transparent PNG in Node

If you ever need to generate a Uint8Array to represent a 1×1 pixel transparent PNG in Node, here’s the script

var base64 = “iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mNkYAAAAAYAAjCB0C8AAAAASUVORK5CYII=”;
var atob = require(‘atob’);
var raw = atob(base64);
var rawLength = raw.length;
u8 = new Uint8Array(new ArrayBuffer(rawLength));
for(i = 0; i < rawLength; i++) {
u8[i] = raw.charCodeAt(i);
}

 

Categories: Uncategorized

An #API for #Google #Authenticator – add free #2FA to your app.

2fa

Two factor authentication is a way to level up your security, beyond username and password. Using Google Authenticator is also a great way to do this for free, since it doesn’t incur costs such as doing 2FA via SMS.

It does require a basic tech awareness, so if your typical user is elderly, then this is not the way to go.

You can of course use Google Authenticator without using an API, you can implement the crypto code yourself, but using this API at AuthenticatorAPI.com does save you alot of development time, since it’s just two API calls.

So, how does it work?, well first you have to generate a random code. This could be just any random code that comes to your head, or perhaps better, to generate a random code per user, and store this.

You now need to show a QR code to a user, which they scan into the Authenticator App. The QR code is generated using the API, and is just a block of HTML you display on your page. It’s 300×300 pixels in size.

To do this, you call;

https://www.authenticatorApi.com/pair.aspx?AppName=MyApp&AppInfo=John&SecretCode=12345678BXYT

Once the user has paired, and they go to log in, you prompt them for their pin. You then have to send the PIN and the SecretCode from earlier to our API, and it will return either True or False.

By Calling;

https://www.authenticatorApi.com/Validate.aspx?Pin=123456&SecretCode=12345678BXYT

The pin is time dependent, so the same PIN won’t work the following day. This defeats key loggers and replay attacks.

 

 

Categories: Uncategorized