Showing posts with label Automation. Show all posts
Showing posts with label Automation. Show all posts

Tuesday, 2 March 2021


This article is a step by step tutorial on how to setup an ssh tunnel -reliably ensuring it's running, and automatically restart it when necessary- to forward local traffic to remote destination. This works on servers running Linux and is intended to help people setup an ssh tunnel to connect to external services ie. mysql that's running on remote server. To follow along, a basic knowledge of ssh, Linux shell and cron is highly recommended. 

SSH TUNNEL Linux tutorial local port forwarding


During your programming/IT career, there is a high chance that you've already used SSH. Whether connecting to a remote server or cloning a git repository. Either way, both involving SSH on a basic level. But beyond these two uses-cases, SSH offers some others functionalities and purposes which may boost your workflow and sometimes are necessary. We are talking about SSH tunneling -aka local or remote port forwarding. In this article our main focus is the local port forwarding, especially when trying to connect from your application/script code to an external remote server; as an example, connecting nodejs script to mysql database in a remote server through ssh. Finish talking, let's get the hands dirty!

Discussing possible approaches

Since we want to access a remote service, mysql database from a nodejs script as an example, we can use the tunnel-ssh npm package. This is absolutely doable, especially when you don't have enough access to create a system-level ssh tunnel using the command line. The main problem regarding this approach is that both the software and the platform are tied together. Personally, I don't want them tied and I prefer separating concerns: your code shouldn't know about any ssh tunneling, and your ssh tunneling shouldn't depend on a particular script to be running.


We are assuming that you have two running servers (Server_A acting as the client, Server_B acting as the remote database server, running mysql on the default 3306 port), and you can connect from Server_A to Server_B using your favorite ssh client, i.e simply: ssh -i my_private_key server_b_user@server_b_ip ( Personally I prefer using password-less ssh login using keys, but the same tutorial applies if you want to use ssh password-full login, the main difference is that you will be prompted to enter it)

Having our requirements met, creating an ssh tunnel from Server_A to Server_B is quite simple, its a single command that you have to run:

 ssh -L [local_ip:]local_port:localhost:remote_service_port -N -i my_private_key server_b_user@server_b_ip

  •  -L: here we specify the type of port forwarding ( -L for local, -R for remote and -D for dynamics )
  •  local_port: The local machine IP address and port number. When local_ip is omitted, the ssh client binds on the localhost.
  •  localhost:remote_service_port: The IP/Hostname and the port of the remote server's service.
  • -N: not to execute a remote command
  • -i my_private_key, server_b_user and server_b_ip: the key, the ssh user and the server IP address.
In our specific use case -trying to connect to a remote mysql service running on Server_B- the above command will be (I choose local available port 33066 but you could use any available port bigger than 1024. Ports =< 1024 are root restricted):

 ssh -L 33066:localhost:3306 -N -i my_private_key server_b_user@server_b_ip

This way, when we try to connect to localhost at port 33066, the request will be forwarded to remote server 3306 using the ssh protocol. We can test if we can connect using telnet (you can otherwise use the netcat command):

To connect from your nodejs script or you php file, simply you have to configure a mysql connection to use the local address and port used above, i.e 33066, and then specify the remote mysql user credentials as if you were connecting from withing the remote server: ie localhost:3306:

nodejs example:

php example:

Probably you noticed that our nodejs/php clients don't know about any ssh tunneling, that's what we must always aim at πŸ˜€

Ensuring the tunnel is always up

 This command is useful when you launch it but once you exit the terminal or reboot your computer/server, the tunnel will be down, your connection is broke and you have to launch it again. In order to keep it running and reliably ensure it gets restarted when necessary/not working properly, there is a multitude of choices and you can use the autossh package as it does the job but it requires more setup. My favorite and personal alternative is a simple cronjob that does the job pretty well. It's a cronjob that calls a small bash script every 5 minutes to check if my tunnel is properly running otherwise launch it:


This is the end of this tutorial, we have seen how to create a ssh tunnel, ensure it's always UP with two examples on how we can connect to remote mysql database using nodejs/php. I hope this was helpful and if you encounter any kinds of problems or you do have suggestions for improvement, your comment is highly welcome. The most important thing to remember here is that there is always light at the end of the tunnelπŸ˜ƒ


Sunday, 10 January 2021

Do you have a google sheets that contains thousands of urls that you want to get the short version of? Do you want to get it done in a matter of seconds? Do you want to implement this without a big hustle?  If so keep reading.


This is a step by step guide to implement bulk urls shortening directly from Google Sheets, based on the TinyURL shortening service and using google apps script (even though, the process is relatively similar to any other url shortening services like Bitly or ). I will briefly describe the backstory but feel free to jump straight to the process.


I have a friend who owns a relatively small website and he had a blackfriday campaign to launch. During this campaign, he wanted to share the urls that display his products on social media and additionally, he wanted to mail them to his customers The problem was that these urls are spam-my-looking .ie: . Since I was the computer guy, he asked me to handle the bulk shortening.

Before jumping to the steps, I would like to define the technologies being used and specify the requirement for this tutorial.


  • Google Sheets: is an online spreadsheet app that lets users create and format spreadsheets and simultaneously work with other people.
  • Google Apps Script: is a rapid application development platform that makes it fast and easy to create business applications that integrate with Google Workspace using JavaScript
  • TinyURL: is a URL shortening web service, which provides short aliases for redirection of long URLs


A Google Sheet with two columns: one that contains urls to be shortened, the second -an empty one- will contain the end result.

sample spreed sheet for url shortening

Since GAS is a JavaScript platform, a familiarity with J is recommended. Finish the talk, let's get to business ⚔️.


1-Access the script editor:

Action: click on "Tools > Script editor":


Explanation: Here we will access the GAS editor. By default, it opens a text file. This file contains a generic function called 'myFunction' that we don't need, and you can either keep it there or remove it entirely. The key point here is that this is tied to our sheet so the function we will be developing is usable from inside the sheet like any other formulas. 



2-Implement the url shortening function:

Action: create a new function called 'tinyurl_getShortLink' with the following code:

Explanation: This code snippet might be pretty self explanatory to JavaScript guys, but I would like to explain it to those without a web programming background. This code snippet takes the url that we want to shorten, referenced by the variable name 'url'. Since we are using TinyURL service, we will be using an endpoint which it exposes to generate a short url from a long one. This is the HTTP endpoint that we will be using: (free to use πŸ˜ƒ)

πŸ’‘  An important note here is that we need to properly encode the url (Line 6) to eliminate eventual problems related to the url's structure.

  Once the url is properly encoded, we do the HTTP request to TinyURL to get the short version. This is done using the  UrlFetchApp service (which is exposed by Google Apps Script environment), and get the result: If the request was successful (we got a 200 as result code), we return the shortened url, otherwise, we return that "An error occurred", as simple as that. Once finished, we hit save, give the project a name of our choice and we are good. Here is my azaytek_short_url project's screenshot:

url shortening code snippet - Google App Scripts

3-Apply our new formulas, i.e function to the sheet's cells:

Action: apply the new formulas .i.e: tinyurl_getShortLink to the sheet.

Apply url shortening Formulas - Google App Scripts

Explanation: In the previous step, we created our proper formulas to shorten urls using the Google Apps Script and it became usable from inside our Google Sheet, similar to any native formulas. Now, we will be applying it to the cells of our choice. We have to select the cell that should contain the sort version, type: =tinyurl_getShortLink(LONG_URL_CELL), and hit ENTER. YADA! We get the short version right in there! 

πŸ’‘  The 'LONG_URL_CELL' placeholder is a reference to cell that contains the origin url.


If we would like to apply the formulas to all the rows, we only have to select the cell that was already generated, mouse button clicked and scroll down until we reach the end of the file. We will have a loading in place (This is our function being executed) :




This is what it take to implement a bulk url-shortenering from inside Google Sheet using Google Apps Scripts and TinyURL service. If you found this tutorial helpful, feel free to share it. Thank you and stay tuned for my next article: Web scrape like a pro using Google App Scripts and get IP rotation for free!