Archive

Archive for the ‘Uncategorized’ Category

#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/” + 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

Car License plate search #API for #Latvia

latvia-photo

Latvia is a small baltic country, with a population of 1.92 million, and a car ownership rate of 342 per 1,000 – so an estimated car count of 650,000 vehicles. Today, we have expanded our API coverage to include Latvia, you can read more about this at our Latvian website http://www.autoapi.lv 

Car registration plates in Latvia use the /CheckLatvia  endpoint and return the following information:

  • Make / Model
  • Age
  • VIN number
  • Fuel
  • Engine size
  • Representative image

Sample Registration Number: 

GZ3425

Sample Json:

{

  “Description”: “VW BORA”,

  “Variant”: “BORA (1J2) (98-13)”,

  “RegistrationYear”: 1998,

  “CarMake”: {

    “CurrentTextValue”: “VW”

  },

  “CarModel”: {

    “CurrentTextValue”: “BORA”

  },

  “MakeDescription”: {

    “CurrentTextValue”: “VW”

  },

  “ModelDescription”: {

    “CurrentTextValue”: “BORA”

  },

  “VehicleIdentificationNumber”: “WVWZZZ1JZXW273563”,

  “EngineSize”: 2324,

  “Power”: 110,

  “FuelType”: “Benzīns”,

  “ImageUrl”: “http://autoapi.lv/image.aspx/@VlcgQk9SQQ==&#8221;

}

 

Categories: Uncategorized

Get the board members of a UK #Company using the Companies House #API in C#

2Crmrq84_400x400

The UK have a very open attitude to their Company registration data, they have bulk data downloads, and a very open API that allows you to read lots of public domain data about UK companies. This is an example in C# that uses this API to return a list of officers (Board memebers) of a UK company, given a UK company number.

private static List GetOfficers(string companyNumber)
{
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
var strUrl = “https://api.companieshouse.gov.uk/company/{0}/officers”;
strUrl = string.Format(strUrl, companyNumber);
var credentials = Convert.ToBase64String(Encoding.ASCII.GetBytes(“xxxxxxxx:”));
var wc = new WebClient();
wc.Headers[HttpRequestHeader.Authorization] = string.Format(“Basic {0}”, credentials);
try
{
var strResult = wc.DownloadString(strUrl);
var jResult = JObject.Parse(strResult);
return jResult[“items”].Select(jOfficer => new Officer(jOfficer)).ToList();
}
catch (WebException ex)
{
var resp = new StreamReader(ex.Response.GetResponseStream()).ReadToEnd();
Console.WriteLine(resp);
return null;
}
}

You will need your own API key, and change the “xxxxxxxx” in the code above. Also, you’ll need to implement your own Officer class, which is outside the scope of this example.

Categories: Uncategorized