One place for hosting & domains

      January 2021

      How To Install and Secure phpMyAdmin with Nginx on an Ubuntu 20.04 Server


      Introduction

      When developing a website or web application, many users need the functionality of a database system like MySQL. However, interacting with the system solely from the MySQL command-line client requires familiarity with Structured Query Language — more commonly referred to as SQL — which can present a major hurdle for some users.

      phpMyAdmin was created to allow users to interact with MySQL through an intuitive web interface, running alongside a PHP development environment. This guide will walk you through installing phpMyAdmin on top of an Nginx server.

      Note: phpMyAdmin runs on a database server, handles database credentials, and allows users to execute SQL statements on the database. Combined with the fact that it’s a widely-deployed PHP application, this means that phpMyAdmin is frequently targeted for attack. If you install and configure phpMyAdmin without taking the proper steps to secure it from malicious actors, you run the risk of your data being lost or stolen.

      In addition to installing the application, this tutorial will go over several measures you can take to harden your phpMyAdmin installation’s security. It will also explain each measure in detail so that you can make informed decisions and protect your system.

      Prerequisites

      In order to complete this guide, you will need:

      Additionally, because phpMyAdmin handles authentication using MySQL credentials, we strongly recommend that you install an SSL/TLS certificate to enable encrypted traffic between server and client. If you do not have an existing domain configured with a valid certificate, follow this guide on securing Nginx with Let’s Encrypt on Ubuntu 20.04 to set this up.

      Warning: If you don’t have an SSL/TLS certificate installed on the server and you still want to proceed, please consider enforcing access via SSH Tunnels as explained in Step 5 of this guide.

      Once you have these prerequisites in place, you can begin following Step 1 of this guide.

      Step 1 — Installing phpMyAdmin

      You can install phpMyAdmin by using APT to download the phpmyadmin package from the default Ubuntu repositories.

      Begin by updating the server’s package index:

      Now you can install phpMyAdmin by running the following command:

      • sudo apt install phpmyadmin

      During the installation process, you will be prompted to choose a web server (either Apache or Lighttpd) to configure. phpMyAdmin can automatically make a number of configuration changes to ensure that it works correctly with either of these web servers upon installation. However, because you are using Nginx as a web server you shouldn’t choose either of these options. Instead, press TAB to highlight the <Ok> and then press ENTER to continue the installation process.

      Next, you’ll be prompted whether to use dbconfig-common for configuring the application database. Select <Yes>. This will set up the internal database and administrative user for phpMyAdmin. You will be asked to define a new password for the phpmyadmin MySQL user, but because this isn’t a password you need to remember you can leave it blank and let phpMyAdmin randomly create a password.

      Note: Assuming you installed MySQL by following Step 2 of the prerequisite LAMP stack tutorial, you may have decided to enable the Validate Password plugin. As of this writing, enabling this component will trigger an error when the phpMyAdmin installation process attempts to set a password for the phpmyadmin user:

      phpMyAdmin password validation error

      To resolve this, select the abort option to stop the installation process. Then, open up your MySQL prompt:

      Or, if you enabled password authentication for the root MySQL user, run this command and then enter your password when prompted:

      From the MySQL prompt, run the following command to disable the Validate Password component. Note that this won’t actually uninstall it, but just stop the component from being loaded on your MySQL server:

      • UNINSTALL COMPONENT "file://component_validate_password";

      Following that, you can close the MySQL client:

      Then try installing the phpmyadmin package again and it will work as expected:

      • sudo apt install phpmyadmin

      Once phpMyAdmin is installed, you can open the MySQL prompt once again with sudo mysql or mysql -u root -p and then run the following command to re-enable the Validate Password component:

      • INSTALL COMPONENT "file://component_validate_password";

      Once the apt install command completes, phpMyAdmin will be fully installed. However, for the Nginx web server to find and serve the phpMyAdmin files correctly, you’ll need to create a symbolic link from the installation files to Nginx’s document root directory. If you followed the prerequisite LEMP stack tutorial, your Nginx installation’s document root is /var/www/your_domain/

      • sudo ln -s /usr/share/phpmyadmin /var/www/your_domain/phpmyadmin

      Your phpMyAdmin installation is now operational. To access the interface, go to your server’s domain name or public IP address followed by /phpmyadmin in your web browser:

      https://server_domain_or_IP/phpmyadmin
      

      phpMyAdmin login screen

      As mentioned before, phpMyAdmin handles authentication using MySQL credentials. This means that to log into phpMyAdmin, you use the same username and password you would normally use to connect to the database using the command line or with an API. If you need help creating MySQL users, check out this guide on How To Manage an SQL Database.

      Note: Logging into phpMyAdmin as the root MySQL user is discouraged because it represents a significant security risk. This guide will outline how to disable logins as the root MySQL user in Step 3 of this guide.

      Your phpMyAdmin installation is completely functional at this point. However, by installing a web interface, you’ve exposed your MySQL database server to the outside world. Because of phpMyAdmin’s popularity, and the potential for it to provide access to large amounts of sensitive data, installations like these are common targets for attacks. In the following sections of this guide, we’ll go over a few different methods by which you can make your phpMyAdmin installation more secure.

      Step 2 — Changing phpMyAdmin’s Default Location

      One way to protect your phpMyAdmin installation is by making it harder to find. Bots will scan for common paths, like /phpmyadmin, /pma, /admin, /mysql, and other similar names. Changing the interface’s URL from /phpmyadmin to something non-standard will make it much harder for automated scripts to find your phpMyAdmin installation and attempt brute-force attacks.

      In the previous step, you created a symbolic link in your Nginx web document root pointing to /usr/share/phpmyadmin, where the actual phpMyAdmin application files are located. You can rename this symbolic link to change phpMyAdmin’s interface URL.

      To do this, navigate to the Nginx document root directory:

      Then run the following ls command to list the files in the document root directory to get a better sense of the change you’ll make. This command includes the -l option, which tells the command to use the “long listing” format. This will instruct ls to return more information than it would otherwise:

      Your output will contain a line like the following:

      Output

      . . . lrwxrwxrwx 1 root root 22 Jan 15 21:09 phpmyadmin -> /usr/share/phpmyadmin/ . . .

      This line indicates that you have a symbolic link named phpmyadmin in this directory. You can change this link name to whatever you’d like, and doing so will in turn change the URL where you can access phpMyAdmin. This will help to obscure the endpoint from bots performing automated searches of common endpoint names.

      Choose a name that hides the purpose of the endpoint. This guide will name the endpoint /hiddenlink and use this name in examples throughout, but you should choose an alternate name.

      Rename the symbolic link with the mv command:

      • sudo mv phpmyadmin hiddenlink

      After running this command, run the ls -l command again to confirm that the symbolic link was renamed correctly:

      This time, the output will indicate that the listing for the symbolic link has been updated with its new name:

      Output

      total 8 . . . lrwxrwxrwx 1 root root 22 Jan 15 21:09 hiddenlink -> /usr/share/phpmyadmin/ . . .

      Now when you go to the URL you previously used to access phpMyAdmin, you’ll get a 404 error:

      https://server_domain_or_IP/phpmyadmin
      

      phpMyAdmin 404 error

      You can instead access your phpMyAdmin interface at the new URL you just configured:

      https://server_domain_or_IP/hiddenlink
      

      phpMyAdmin login screen

      By obscuring phpMyAdmin’s real location on the server, you’re securing its interface against automated scans and manual brute-force attempts.

      Step 3 — Disabling Root Login

      On MySQL, as well as within regular Linux systems, the root account is a special administrative account with unrestricted access to the system. In addition to being a privileged account, it’s a known login name, which makes it an obvious target for brute-force attacks. To minimize these risks, this step will outline how to configure phpMyAdmin to deny any login attempts coming from the root MySQL user. This way, even if you provide valid credentials for the user root, you’ll still get an Access denied! error and won’t be allowed to log in.

      Because you selected dbconfig-common to configure and store phpMyAdmin settings, the application’s default configuration is currently stored within your MySQL database. You’ll need to create a new config.inc.php file in phpMyAdmin’s configuration directory to define your custom settings. Even though phpMyAdmin’s PHP scripts are located inside the /usr/share/phpmyadmin directory, the application’s configuration files are located in /etc/phpmyadmin.

      Create a new custom settings file inside the /etc/phpmyadmin/conf.d directory and name it pma_secure.php:

      • sudo nano /etc/phpmyadmin/conf.d/pma_secure.php

      Then add the following content to the new file:

      /etc/phpmyadmin/conf.d/pma_secure.php

      <?php
      
      # PhpMyAdmin Settings
      # This should be set to a random string of at least 32 chars
      $cfg['blowfish_secret'] = 'CHANGE_THIS_TO_A_STRING_OF_32_RANDOM_CHARACTERS';
      
      $i=0;
      $i++;
      
      $cfg['Servers'][$i]['auth_type'] = 'cookie';
      $cfg['Servers'][$i]['AllowNoPassword'] = false;
      $cfg['Servers'][$i]['AllowRoot'] = false;
      
      ?>
      

      By including the AllowNoPassword and AllowRoot directives and setting both of them to false, this configuration file disables passwordless logins and logins by the root MySQL user, respectively.

      Note that the auth_type setting configures phpMyAdmin to use the cookie authentication method. phpMyAdmin uses the cookie authentication method by default, which allows you to log in to phpMyAdmin as any valid MySQL user with the help of cookies. With this method, the MySQL user password is stored and encrypted with the Advanced Encryption Standard (AES) algorithm in a temporary cookie.

      Historically, phpMyAdmin instead used the Blowfish algorithm for this purpose. However, it still looks for a directive named blowfish_secret, which points to passphrase to be used internally by the AES algorithm. This isn’t a passphrase you need to remember, so any string containing 32 random characters will work here.

      Update the line that reads 'CHANGE_THIS_TO_A_STRING_OF_32_RANDOM_CHARACTERS' to a random string containing at least 32 characters.

      Note: If the passphrase you enter here is shorter than 32 characters in length, it will result in the encrypted cookies being less secure. Entering a string longer than 32 characters, though, won’t cause any harm.

      To generate a truly random string of characters, you can install and use the pwgen program with APT:

      By default, pwgen creates easily pronounceable, though less secure, passwords. However, by including the -s flag, as in the following command, you can create a completely random, difficult-to-memorize password. Note the final two arguments to this command: 32, which dictates how long the password string pwgen will generate should be; and 1 which tells pwgen how many strings it should generate:

      Copy this command’s resulting output and add it to the pma_secure.php file, replacing 'CHANGE_THIS_TO_A_STRING_OF_32_RANDOM_CHARACTERS'.

      Save and close the file when you’re done editing it. If you used nano, do so by pressing CTRL + X, Y to confirm the changes, and then ENTER to return to the bash prompt.

      The changes will apply automatically. If you reload the login page now and try to log in as root, you will get an Access denied! error:

      access denied

      Logins by the root MySQL user are now prohibited on your phpMyAdmin installation. This security measure will block brute-force scripts from trying to guess the root database user’s password on your server. Moreover, it will enforce the usage of less-privileged MySQL accounts for accessing phpMyAdmin’s web interface, which by itself is an important security practice.

      Step 4 — Creating an Authentication Gateway

      Hiding your phpMyAdmin installation in an unusual location might sidestep some automated bots scanning the network, but it’s useless against targeted attacks. To better protect a web application with restricted access, it’s generally more effective to stop attackers before they can even reach the application. This way, they’ll be unable to use generic exploits and brute-force attacks to guess access credentials.

      In the specific case of phpMyAdmin, it’s even more important to keep the login interface locked away. By keeping it open to the world, you’re offering a brute-force platform for attackers to guess your database credentials.

      This step outlines how to add an extra authentication layer to your phpMyAdmin installation so as to increase the security of your MySQL databases. Most web servers, including Nginx, provide this capability natively. By completing this step, anyone who tries to access your phpMyAdmin installation’s login screen will first be required to pass through an HTTP authentication prompt by entering a valid username and password.

      To set this up, you first need to create a password file to store the authentication credentials. Nginx requires that passwords be encrypted using the crypt() function. The OpenSSL suite, which should be installed on your Ubuntu server by default, includes this functionality.

      To create an encrypted password, type:

      You will be prompted to enter and confirm the password that you wish to use. The utility will then display an encrypted version of the password that will look something like this:

      Output

      9YHV.p60.Cg6I

      Copy this value, as you will need to include it in the authentication file you are about to create.

      Now, create an authentication file. For the purposes of this guide, we’ll call this file pma_pass and place it in the Nginx configuration directory:

      • sudo nano /etc/nginx/pma_pass

      In this file, specify the username you would like to use, followed by a colon (:) and then the encrypted version of the password you received from the openssl passwd utility.

      In this example the user is named sammy, but you can choose any username you’d like. This doesn’t need to be the name of an existing user profile on your Ubuntu server or that of a MySQL user.

      After adding your chosen username and the encrypted password you copied earlier, the file will look like this:

      /etc/nginx/pma_pass

      sammy:9YHV.p60.Cg6I
      

      Save and close the file when finished.

      Next, you’ll need to modify the Nginx configuration file. Again, this guide follows the conventions established in the prerequisite LEMP tutorial, so the configuration file used in the following examples is /etc/nginx/sites-available/your_domain. Be sure that you use the relevant Nginx configuration file for the web location where your phpMyAdmin installation is currently hosted.

      Open your Nginx configuration file in your preferred text editor to get started:

      • sudo nano /etc/nginx/sites-available/your_domain

      Locate the server block, and the location / section within it. You need to create a new location section below this location / block to match phpMyAdmin’s current path on the server.

      Recall that in Step 2 of this guide you changed the name of phpMyAdmin’s location by renaming the symbolic link (hiddenlink in our example). Here, you need to enter the name you used for this symbolic link. You don’t need to include the full file path, just the name of the symbolic link relative to the Nginx document root directory:

      /etc/nginx/sites-available/your_domain

      server {
              . . .
      
              location / {
                      try_files $uri $uri/ =404;
              }
      
              location ^~ /hiddenlink {
      
              }
      
              . . .
      }
      

      Within this block, set up two directives: auth_basic, which defines the message that will be displayed on the authentication prompt, and auth_basic_user_file, pointing to the authentication file you just created. Add both of these directives to the new location section:

      /etc/nginx/sites-available/your_domain

      server {
              . . .
              location / {
                      try_files $uri $uri/ =404;
              }
      
              location ^~ /hiddenlink {
                      auth_basic "Admin Login";
                      auth_basic_user_file /etc/nginx/pma_pass;
              }
              . . .
      }
      

      Lastly, notice that this block has a ^~ selector before the new location definition. This is to make sure Nginx won’t bypass your access rules when it matches the rule for PHP files, which are typically defined as a regular expression in order to catch all .php files. In Nginx configuration files, regular expression definitions have a higher precedence over standard location definitions. This means that if you we don’t use the ^~ selector at the beginning of the location, users will still be able to bypass the authentication prompt by navigating to http://server_domain_or_ip/hiddenlink/index.php in their browser.

      The ^~ selector at the beginning of the location definition tells Nginx to ignore other matches when it finds a match for this location. This means that any subdirectories or files within /hiddenlink/ will be matched with this rule. However, because the definition to parse PHP files will be skipped as a result of the ^~ selector usage, we’ll need to include a new PHP location block inside the /hiddenlink definition. This will make sure PHP files inside this location are properly parsed; otherwise they will be sent to the browser as download content.

      Add the following highlighted lines within the location block you just added:

      /etc/nginx/sites-available/your_domain

      server {
              . . .
      
              location / {
                      try_files $uri $uri/ =404;
              }
      
              location ^~ /hiddenlink/ {
                      auth_basic "Admin Login";
                      auth_basic_user_file /etc/nginx/pma_pass;
      
                      location ~ .php$ {
                              include snippets/fastcgi-php.conf;
                              fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
                      }
              }
          . . .
      }
      

      Remember to replace hiddenlink with the actual path where phpMyAdmin can be found. You should also double check the location of your PHP-FPM socket file, which will vary depending on which version of PHP you currently have installed. In this example, we use php7.4-fpm.sock which is valid for PHP 7.4, the version that is installed on Ubuntu 20.04 via the default APT repositories.

      Save and close the file when you’re done. To check whether the configuration file is valid, run the following command:

      The following output indicates that the configuration file’s syntax is valid:

      Output

      nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful

      To activate the new authentication gate, reload Nginx:

      • sudo systemctl reload nginx

      Now when you visit the phpMyAdmin URL in your web browser, you will be prompted for the username and password you added to the pma_pass file:

      https://server_domain_or_IP/hiddenlink
      

      Nginx authentication popup

      Once you enter your credentials, you’ll be taken to the standard phpMyAdmin login page.

      Note: The authentication prompt may not appear if you have accessed phpMyAdmin recently. To force the prompt to appear, you may have to refresh the page, clear your cache, or open a different browser session.

      In addition to providing an extra layer of security, this gateway will help keep your MySQL logs clean of spammy authentication attempts.

      Step 5 — Setting Up Access via Encrypted Tunnels

      For increased security, it is possible to lock down your phpMyAdmin installation to authorized hosts only. You can limit access to phpMyAdmin by specifying individual authorized hosts in your Nginx configuration file. This way, any request coming from an IP address that is not on the list will be denied.

      Even though this feature alone can be enough in some use cases, it’s not always the best long-term solution, mainly due to the fact that most people don’t access the internet from static IP addresses. As soon as you get a new IP address from your internet provider, you’ll be unable to get to the phpMyAdmin interface until you update the Nginx configuration file with your new IP address.

      For a more robust long-term solution, you can use IP-based access control to create a setup in which users will only have access to your phpMyAdmin interface if they’re accessing from either an authorized IP address or localhost via SSH tunneling. We’ll go over how to set up both of these access controls in the sections below.

      Combining IP-based access control with SSH tunneling greatly increases security because it fully blocks access coming from the public internet (except for authorized IPs), in addition to providing a secure channel between the user and the server through the use of encrypted tunnels.

      Setting Up IP-Based Access Control on Nginx

      On Nginx, IP-based access control can be defined in the corresponding location block of a given site, using the directives allow and deny. For instance, if you want to only allow requests coming from a given host, you would include the following two lines, in this order, inside the relevant location block for the site you would like to protect:

      allow hostname_or_IP;
      deny all;
      

      You can allow as many hosts as you want, and you only need to include one allow line for each authorized host/IP inside the respective location block for the site you’re protecting. The directives will be evaluated in the same order as they are listed until a match is found or the request is finally denied due to the deny all directive.

      In this step, you’ll configure Nginx to only allow requests coming from localhost or your current IP address. First, you’ll need to know the current public IP address your local machine is using to connect to the internet. There are various ways to obtain this information; for simplicity, this guide will use the service provided by ipinfo.io. You can either open the URL https://ipinfo.io/ip in your browser, or run the following command from your local machine:

      • curl https://ipinfo.io/ip

      This command will return an IP address, like this:

      Output

      203.0.113.0

      The value returned by this command is your local machine’s current public IP address. You’ll configure phpMyAdmin’s location block to only allow requests coming from that IP or locally from the server itself.

      To do this, once again open your site’s Nginx configuration file using your preferred text editor:

      • sudo nano /etc/nginx/sites-available/your_domain

      Because you already have an access rule within your current configuration, you need to combine it with IP-based access control using the directive satisfy all. This way, you can keep the current HTTP authentication prompt for increased security.

      Add the following highlighted lines to your phpMyAdmin configuration’s location block:

      /etc/nginx/sites-available/your_domain

      server {
              . . .
      
              location ^~ /hiddenlink/ {
                      satisfy all; #requires both conditions
      
                      allow 203.0.113.0; #allow your IP
                      allow 127.0.0.1; #allow localhost via SSH tunnels
                      deny all; #deny all other sources
      
                      auth_basic "Admin Login";
                      auth_basic_user_file /etc/nginx/pma_pass;
      
                      location ~ .php$ {
                              include snippets/fastcgi-php.conf;
                              fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
                      }
              }
      
              . . .
      }
      

      This is how the file will look after adding these new directives. Remember to replace hiddenlink with the actual path where phpMyAdmin can be found, and the highlighted IP address with your local machine’s current public IP address:

      /etc/nginx/sites-available/your_domain

      server {
              . . .
      
              location ^~ /hiddenlink/ {
                      satisfy all; #requires both conditions
      
                      allow 203.0.113.0; #allow your IP
                      allow 127.0.0.1; #allow localhost via SSH tunnels
                      deny all; #deny all other sources
      
                      auth_basic "Admin Login";
                      auth_basic_user_file /etc/nginx/pma_pass;
      
                      location ~ .php$ {
                              include snippets/fastcgi-php.conf;
                              fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
                      }
              }
      
              . . .
      }
      

      Save and close the file when you’re done. To check if the configuration file is valid, you can run:

      The following output is expected:

      Output

      nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful

      Now reload the web server so the changes take effect:

      • sudo systemctl reload nginx

      Because your IP address is explicitly listed as an authorized host, your access shouldn’t be disturbed. Anyone else trying to access your phpMyAdmin installation, however, will now get a 403 Forbidden error:

      https://server_domain_or_IP/hiddenlink
      

      Nginx 403 error

      The next subsection of this guide will provide details on how to use SSH tunneling to access the web server through local requests. This way, you’ll still be able to access phpMyAdmin’s interface even when your IP address changes.

      Accessing phpMyAdmin Through an Encrypted Tunnel

      SSH tunneling works as a way of redirecting network traffic through encrypted channels. By running an ssh command similar to what you would use to log into a server, you can create a secure “tunnel” between your local machine and that server. After establishing a tunnel, all traffic coming in on a given local port can be redirected through the encrypted tunnel, using the remote server as a proxy before reaching out to the internet. This is similar to what happens when you use a virtual private network (VPN), but SSH tunnels generally require less configuration to set up.

      You can use SSH tunneling to proxy your requests to the remote web server running phpMyAdmin. By creating a tunnel between your local machine and the server where phpMyAdmin is installed, you can redirect local requests to the remote web server. More importantly, traffic will be encrypted and requests will reach Nginx as if they’re coming from localhost. This way, no matter what IP address you’re connecting from, you’ll be able to securely access phpMyAdmin’s interface.

      Because the traffic between your local machine and the remote web server will be encrypted, this is a safe alternative for situations where you can’t have an SSL/TLS certificate installed on the web server running phpMyAdmin.

      From your local machine, run this command whenever you need access to phpMyAdmin:

      Let’s examine each part of the command:

      • user: the Ubuntu user profile to connect to on the server where phpMyAdmin is running
      • server_domain_or_IP: SSH host where phpMyAdmin is running
      • -L 8000:localhost:80 redirects HTTP traffic on port 8000
      • -L 8443:localhost:443 redirects HTTPS traffic on port 8443
      • -N: prevents the execution of remote commands

      Note: This command will block the terminal until you interrupt it by pressing CTRL+C, in which case it will end the SSH connection and stop the packet redirection. If you’d prefer to run this command in background mode, you can include the SSH option -f.

      Now, go to your browser and replace server_domain_or_IP with localhost:PORT, where PORT is either 8000 for HTTP or 8443 for HTTPS:

      http://localhost:8000/hiddenlink
      
      https://localhost:8443/hiddenlink
      

      phpMyAdmin login screen

      Note: If you’re accessing phpMyAdmin via HTTPS, you might get an alert message questioning the security of the SSL certificate. This happens because the domain name you’re using (localhost) doesn’t match the address registered within the certificate (that is, the domain where phpMyAdmin is actually being served). Rest assured that it is safe to proceed.

      Also, be aware that you may need to refresh your browser session or double check the URL if you’ve set up any redirects in your Nginx configuration file.

      All requests on localhost:8000 (HTTP) and localhost:8443 (HTTPS) are now being redirected through a secure tunnel to your remote phpMyAdmin application. Not only have you increased security by disabling public access to your phpMyAdmin, you also protected all traffic between your local computer and the remote server by using an encrypted tunnel to send and receive data.

      If you’d like to enforce the usage of SSH tunneling to anyone who wants access to your phpMyAdmin interface (including you), you can do that by removing any other authorized IPs from the Nginx configuration file, leaving 127.0.0.1 as the only host allowed to access that location. Considering nobody will be able to make direct requests to phpMyAdmin, it is safe to remove HTTP authentication in order to simplify your setup. This is how your configuration file would look like in such a scenario:

      /etc/nginx/sites-available/your_domain

      server {
              . . .
      
              location ^~ /hiddenlink/ {      
                      allow 127.0.0.1; #allow localhost via SSH tunnels
                      deny all; #deny all other sources
      
                      location ~ .php$ {
                              include snippets/fastcgi-php.conf;
                              fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
                      }
              }
      
              . . .
      }
      

      Once you reload Nginx’s configuration with sudo systemctl reload nginx, your phpMyAdmin installation will be locked down and users will be required to use SSH tunnels in order to access phpMyAdmin’s interface via redirected requests.

      Conclusion

      By following this tutorial, you installed phpMyAdmin on Ubuntu 20.04 running Nginx as the web server. You also learned about several advanced methods to secure a phpMyAdmin installation on Ubuntu, such as disabling root login, creating an extra layer of authentication, and using SSH tunneling to access a phpMyAdmin installation via local requests only.

      After completing this tutorial, you can manage your MySQL databases from a reasonably secure web interface. This user interface exposes most of the functionality available via the MySQL command line. You can browse databases and schema, execute queries, and create new data sets and structures.

      If you’d like to learn more about working with MySQL, we encourage you to check out this introduction to queries in MySQL. For a deeper understanding of SQL beyond just queries, you may also be interested in our How To Use SQL tutorial series.



      Source link

      How To Configure WebDAV Access with Apache on Ubuntu 18.04


      Introduction

      WebDAV is an extension of the HTTP protocol that allows users to manage files on remote servers. There are many ways to use a WebDAV server, you can, for example, share Word or Excel documents with your colleagues by uploading them to your WebDAV server. You can also share your music collection with your family and friends by simply giving them a URL. All of this can be achieved without them installing any additional software as everything is built right into their operating system.

      In this article, you’ll configure an Apache web server to enable WebDAV access from Windows, Mac, and Linux with SSL and password authentication.

      Prerequisites

      Before you begin this guide, you will need the following:

      WebDAV requires very few server resources, so any sized virtual machine will be enough to get your WebDAV server up and running.

      Log in to your server as the sudo-enabled, non-root user to start the first step.

      Step 1 — Enabling the WebDAV Apache Modules

      The Apache web server provides a lot of functionality as optional modules. You can enable and disable these modules to add and remove their functionality from Apache. Its WebDAV functionality is included in a module that you installed along with Apache, but is not enabled by default.

      You enable the WebDAV module for Apache using the a2enmod utility. The following two commands will enable the WebDAV modules:

      • sudo a2enmod dav
      • sudo a2enmod dav_fs

      Now, restart Apache to load the new modules:

      • sudo systemctl restart apache2.service

      You’ve now loaded and started the WebDAV module. In the next step, you will configure Apache to serve your files via WebDAV.

      Step 2 — Configuring Apache

      In this step, you will create all the configurations that Apache needs to implement a WebDAV server.

      First, create the WebDAV root folder at /var/www/webdav that will hold the files you want to make available over WebDAV:

      • sudo mkdir /var/www/webdav

      Then, set Apache’s user, www-data, to be the owner of the WebDAV directory:

      • sudo chown www-data:www-data /var/www/webdav

      Next, you need to create a location for the database file that Apache uses to manage and lock the files that WebDAV users are accessing. This file needs to be readable and writable by Apache, but must not be available from the website as this can leak sensitive information.

      Create a new directory with the mkdir utility for the database file at /usr/local/apache/var/:

      • sudo mkdir -p /usr/local/apache/var/

      The -p option tells the mkdir utility to create all the directories in the path you specified if they don’t exist.

      Next, set the owner and group of the new directory to Apache’s user and group with the chown utility:

      • sudo chown www-data:www-data /usr/local/apache/var

      Now, you need to edit the VirtualHost file that holds the Apache configuration about your domain name. This file is located in /etc/apache2/sites-enabled/ and ends in le-ssl.conf if you used Certbot to register the SSL certificate.

      Open the VirtualHost file with a text editor:

      • sudo nano /etc/apache2/sites-enabled/your_domain-le-ssl.conf

      On the first line, add the DavLockDB directive:

      /etc/apache2/sites-enabled/your_domain-le-ssl.conf

      DavLockDB /usr/local/apache/var/DavLock
      . . .
      

      Next, add the following Alias and Directory directives inside the <VirtualHost> tags following all the other directives:

      /etc/apache2/sites-enabled/your_domain-le-ssl.conf

      . . .
      Alias /webdav /var/www/webdav
      
      <Directory /var/www/webdav>
          DAV On
      </Directory>
      

      The Alias directive maps requests to http://your.server/webdav to the /var/www/webdav folder.

      The Directory directive tells Apache to enable WebDAV for the /var/www/webdav folder. You can find out more about mod_dav from the Apache docs.

      Your final VirtualHost file will be as follows, which includes the DavLockDB, Alias, and Directory directives in the correct locations:

      /etc/apache2/sites-enabled/your_domain-le-ssl.conf

      DavLockDB /usr/local/apache/var/DavLock
      <IfModule mod_ssl.c>
      <VirtualHost *:443>
          ServerAdmin [email protected]_domain
              ServerName your_domain
              ServerAlias your_domain
              DocumentRoot /var/www/your_domain/public_html
              ErrorLog ${APACHE_LOG_DIR}/error.log
              CustomLog ${APACHE_LOG_DIR}/access.log combined
      
              SSLCertificateFile /etc/letsencrypt/live/your_domain/fullchain.pem
              SSLCertificateKeyFile /etc/letsencrypt/live/your_domain/privkey.pem
              Include /etc/letsencrypt/options-ssl-apache.conf
      
              Alias /webdav /var/www/webdav
      
              <Directory /var/www/webdav>
                  DAV On
              </Directory>
      
      </VirtualHost>
      </IfModule>
      

      If you make any syntax errors while you are editing Apache’s configuration it will refuse to start. It’s a good practice to check your Apache configuration before restarting Apache.

      Use the apachectl utility to check the configuration:

      • sudo apachectl configtest

      If your configuration is error free, apachectl will print Syntax OK. When you receive this, it is safe to restart Apache to load the new configuration:

      • sudo systemctl restart apache2.service

      You’ve now configured Apache as a WebDAV server to serve files from /var/www/webdav. However, you don’t yet have authentication configured or enabled so anyone that can access your server will be able to read, write, and edit your files. In the next section, you will enable and configure WebDAV authentication.

      Step 3 — Adding Authentication to WebDAV

      The authentication method that you will use is called digest authentication. Digest authentication is the more secure method of WebDAV authentication, especially when coupled with HTTPS.

      Digest authentication works with a file that stores the usernames and passwords of users that are allowed to access the WebDAV server. Just as with the DavLockDB the digest file needs to be stored in a location that Apache can read and write to and that cannot be served from your website.

      As you already created /usr/local/apache/var/ for this purpose, you will place the digest file there as well.

      First, create an empty file called users.password at /usr/local/apache/var/ with the touch utility:

      • sudo touch /usr/local/apache/var/users.password

      Then change the owner and group to www-data so Apache can read and write to it:

      • sudo chown www-data:www-data /usr/local/apache/var/users.password

      New users are added to WebDAV using the htdigest utility. The following command adds the user sammy:

      • sudo htdigest /usr/local/apache/var/users.password webdav sammy

      The webdav in this command is the realm and should be thought of as the group you are adding the new user to. It is also the text displayed to users as they enter their username and password when they access your WebDAV server. You can choose whatever realm best describes your use case.

      It will prompt you to enter a password and confirm it when you run the htdigest command:

      Output

      Adding user sammy in realm webdav New password: Re-type new password:

      Next, you’ll tell Apache to require authentication for WebDAV access and to use the users.password file.

      Open your VirtualHost file:

      • sudo nano /etc/apache2/sites-enabled/your_domain-le-ssl.conf

      Then, add the following lines inside the Directory directive block:

      /etc/apache2/sites-enabled/your_domain-le-ssl.conf

      AuthType Digest
      AuthName "webdav"
      AuthUserFile /usr/local/apache/var/users.password
      Require valid-user
      

      These directives do the following:

      • AuthType Digest: Use the digest authentication method.
      • AuthName "webdav": Only allow users from the webdav realm.
      • AuthUserFile /usr/local/apache/var/users.password: Use the usernames and passwords contained in /usr/local/apache/var/users.password.
      • Require valid-user: Allow access to any user listed in the users.password file that supplied the correct password.

      Your <Directory> directive will be as follows:

      /etc/apache2/sites-enabled/your_domain-le-ssl.conf

      <Directory /var/www/webdav>
        DAV On
        AuthType Digest
        AuthName "webdav"
        AuthUserFile /usr/local/apache/var/users.password
        Require valid-user
      </Directory>
      

      Next, enable the auth_digest Apache module so that Apache knows how to use the digest authentication method:

      Finally, restart Apache to load all the new configuration:

      • sudo systemctl restart apache2.service

      You’ve now configured your WebDAV server to use HTTPS and digest authentication. It is ready to start serving files to your users. In the next section, you’ll access a WebDAV server from either Windows, Linux, or macOS.

      Step 4 — Accessing WebDAV

      In this step, you’ll access a WebDAV server with the native file browsers of macOS, Windows, and Linux (KDE and GNOME).

      Before you get started accessing your WebDAV server you should put a file into the WebDAV folder, so you have a file to test.

      Open a new file with a text editor:

      • sudo nano /var/www/webdav/webdav-testfile.txt

      Add some text then save and exit. Now, set the owner and group of this file to www-data:

      • sudo chown www-data:www-data /var/www/webdav/webdav-testfile.txt

      You are now ready to start accessing and testing your WebDAV server.

      Linux KDE

      First, open the KDE Dolphin file manager. Then edit the address bar with a URL that has the following form:

      webdavs://your_domain/webdav
      

      image showing WebDAV link in the Dolphin address bar

      When you hit ENTER you will be prompted to enter a username and password.

      image showing the username and password dialog box

      Check the Remember password option if you want Dolphin to retain your password. Then click OK to continue. It will now present you with the contents of the /var/www/webdav/ directory, which you can manipulate as if they were on your local system.

      Bookmark your WebDAV server by grabbing the folder icon in the address bar and dragging it under the Remote section in the left-hand navigation panel.

      Image showing the WebDAV server in the Dolphin Remote locations

      Linux GNOME

      First, open the Files application by clicking on its icon on the right-hand side of the desktop.

      Image showing Finder icon

      When Files opens do the following:

      1. Click on + Other Locations.
      2. Enter the URL of your WebDAV instance with the following form:
      davs://your_domain/webdav
      

      Image showing the Files application

      Then, click on Connect. It will then prompt you with a username and password dialog box.

      Image showing the username and password dialog

      Enter your username and password then click Connect to log in to your WebDAV server. Check the Remember forever option if you do not want to enter your password every time you access your files.

      Your WebDAV folder will now be available in Files where you can manage your files:

      Image showing the WebDAV server in the Files application

      macOS

      First, open the Finder application. Next, click on the Go menu and then on Connect to server.

      Image showing the Go menu in the Finder application

      You will now find a new dialog box where you enter the URL of the WebDAV server. This URL must have the following form:

      https://your_domain/webdav
      

      Image showing the URL entry dialog box

      Click on the Connect button to continue. It will prompt you to enter a username and password.

      Image showing the username and password dialog

      Click on Connect to complete adding your WebDAV server to your system.

      You will now find your WebDAV server in Finder under the Locations section.

      Image showing the WebDAV share in Finder

      Windows

      First, from the Start Menu, open the File Explorer application. When this opens select This PC from the left-hand navigation panel.

      Image showing This PC in the navigation panel

      Next, click on the Map network drive icon in the top navigation bar.

      Image showing the Map network drive icon in top navigation panel

      Enter the URL of your WebDAV server with a URL of the following form:

      https://your_domain/webdav
      

      Image showing the URL entry dialog

      Click Finish to connect to your WebDAV server. It will prompt you to enter a username and password.

      Image showing username and password entry dialog

      Enter your username and password and click OK to log in to your server. Check the Remember my credentials option if you do not want to enter your password every time you access your files.

      Your WebDAV will now appear as a location under the This PC section of the File Explorer left-hand navigation panel.

      Image showing the WebDAV share in File Explorer

      Conclusion

      You have now set up and configured a secure WebDAV server to serve your files to your users. No matter what operating system your users have on their local system they will be able to access and manage the files in your WebDAV server.



      Source link

      How To Make a URL Shortener with Flask and SQLite


      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      Flask is a framework for building web applications using Python and SQLite is a database engine that you can use with Python to store application data.

      In this tutorial, you will build a URL shortener, a service that takes any URL and generates a shorter, more readable version like bit.ly.

      Hashids is a library that generates a short unique ID from integers. For example, you can use it to convert a number like 12 to a unique string like 1XcId. You will use Hashids to generate unique strings for URL IDs.

      You can use unique strings to generate IDs for videos on a video-sharing site or IDs for images on a service to upload images. This unique string gives you unpredictable IDs; therefore, if a user can access an image at your_domain/image/J32Fr, they can’t predict the location of other images. This is not possible if you use integer IDs in a URL shortener—for example, your_domain/image/33 would allow users to predict the location of other images. Unpredictable URLs add a form of privacy to your service because they prevent users from working out different URLs shortened by other users.

      You will use Flask, SQLite, and the Hashids library to build your URL shortener. Your application will allow users to enter a URL and generate a shorter version, in addition to a statistics page where users can view the number of times a URL has been clicked. You’ll use the Bootstrap toolkit to style your application.

      Prerequisites

      Step 1 — Setting Up Dependencies

      In this step, you will activate your Python environment and install Flask and the Hashids library using the pip package installer. Then you’ll create the database you will use to store URLs.

      First, activate your programming environment if you haven’t already:

      Once you have activated your programming environment, install Flask and the Hashids library using the following command:

      • pip install flask hashids

      Then create a database schema file called schema.sql, containing SQL commands to create a urls table. Open a file called schema.sql inside your flask_shortener directory:

      Type the following SQL commands inside this file:

      flask_shortener/schema.sql

      DROP TABLE IF EXISTS urls;
      
      CREATE TABLE urls (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          original_url TEXT NOT NULL,
          clicks INTEGER NOT NULL DEFAULT 0
      );
      

      In the schema file, you first delete the urls table if it already exists. This avoids the possibility of another table named urls existing, which might result in confusing behavior; for example, if it has different columns. Note that this will delete all of the existing data whenever the schema file executes.

      You then create the table with the following columns:

      • id: The ID of the URL, this will be a unique integer value for each URL entry. You will use it to get the original URL from a hash string.
      • created: The date the URL was shortened.
      • original_url: The original long URL to which you will redirect users.
      • clicks: The number of times a URL has been clicked. The initial value will be 0, which will increment with each redirect.

      Save and close the file.

      To execute the schema.sql file to create the urls table, open a file named init_db.py inside your flask_shortener directory:

      Then add the following code:

      flask_shortener/init_db.py

      import sqlite3
      
      connection = sqlite3.connect('database.db')
      
      with open('schema.sql') as f:
          connection.executescript(f.read())
      
      connection.commit()
      connection.close()
      

      Here you connect to a file called database.db that your program will create once you execute this program. This file is the database that will hold all of your application’s data. You then open the schema.sql file and run it using the executescript() method that executes multiple SQL statements at once. This will create the urls table. Finally, you commit the changes and close the connection.

      Save and close the file.

      Run the program:

      After execution, a new file called database.db will appear in your flask_shortener directory.

      With this, you’ve installed Flask and the Hashids library, created the database schema, and created the SQLite database with a table called urls to store the URL shortener’s original URLs. Next, you’ll use Flask to create the index page where your users can enter a URL to generate a short URL.

      Step 2 — Creating the Index Page for Shortening URLs

      In this step, you will create a Flask route for the index page, which will allow users to enter a URL that you then save into the database. Your route will use the ID of the URL to generate a short string hash with the Hashids library, construct the short URL, and then render it as a result.

      First, open a file named app.py inside your flask_shortener directory. This is the main Flask application file:

      Add the following code to the file:

      flask_shortener/app.py

      import sqlite3
      from hashids import Hashids
      from flask import Flask, render_template, request, flash, redirect, url_for
      
      
      def get_db_connection():
          conn = sqlite3.connect('database.db')
          conn.row_factory = sqlite3.Row
          return conn
      

      In this code, you first import the sqlite3 module, the Hashids class from the hashids library, and Flask helpers.

      The get_db_connection() function opens a connection to the database.db database file and then sets the row_factory attribute to sqlite3.Row. As a result, you can have name-based access to columns; the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn connection object you’ll be using to access the database.

      Next, add the following:

      flask_shortener/app.py

      . . .
      app = Flask(__name__)
      app.config['SECRET_KEY'] = 'this should be a secret random string'
      
      hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])
      
      

      You create the Flask application object and set a secret key to secure sessions. Since the secret key is a secret random string, you’ll also use it to specify a salt for the Hashids library; this will ensure the hashes are unpredictable since every time the salt changes, the hashes also change.

      Note: A salt is a random string that is provided to the hashing function (that is, hashids.encode()) so that the resulting hash is shuffled based on the salt. This process ensures the hash you get is specific to your salt so that the hash is unique and unpredictable, like a secret password that only you can use to encode and decode hashes. Remember to keep it secret for security purposes (which is why you use the application’s secret key).

      You create a hashids object specifying that a hash should be at least 4 characters long by passing a value to the min_length parameter. You use the application’s secret key as a salt.

      Next, add the following code to the end of your file:

      flask_shortener/app.py

      . . .
      @app.route('/', methods=('GET', 'POST'))
      def index():
          conn = get_db_connection()
      
          if request.method == 'POST':
              url = request.form['url']
      
              if not url:
                  flash('The URL is required!')
                  return redirect(url_for('index'))
      
              url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
                                      (url,))
              conn.commit()
              conn.close()
      
              url_id = url_data.lastrowid
              hashid = hashids.encode(url_id)
              short_url = request.host_url + hashid
      
              return render_template('index.html', short_url=short_url)
      
          return render_template('index.html')
      

      The index() functions is a Flask view function, which is a function decorated using the special @app.route decorator. Its return value gets converted into an HTTP response that an HTTP client, such as a web browser, displays.

      Inside the index() view function, you accept both GET and POST requests by passing methods=('GET', 'POST') to the app.route() decorator. You open a database connection.

      Then if the request is a GET request, it skips the if request.method == 'POST' condition until the last line. This is where you render a template called index.html, which will contain a form for users to enter a URL to shorten.

      If the request is a POST request, the if request.method == 'POST' condition is true, which means a user has submitted a URL. You store the URL in the url variable; if the user has submitted an empty form, you flash the message The URL is required! and redirect to the index page.

      If the user has submitted a URL, you use the INSERT INTO SQL statement to store the submitted URL in the urls table. You include the ? placeholder in the execute() method and pass a tuple containing the submitted URL to insert data safely into the database. Then you commit the transaction and close the connection.

      In a variable called url_id, you store the ID of the URL you inserted into the database. You can access the ID of the URL using the lastrowid attribute, which provides the row ID of the last inserted row.

      You construct a hash using the hashids.encode() method, passing it the URL ID; you save the result in a variable called hashid. As an example, the call hashids.encode(1) might result in a unique hash like KJ34 depending on the salt you use.

      You then construct the short URL using request.host_url, which is an attribute that Flask’s request object provides to access the URL of the application’s host. This will be http://127.0.0.1:5000/ in a development environment and your_domain if you deploy your application. For example, the short_url variable will have a value like http://127.0.0.1:5000/KJ34, which is the short URL that will redirect your users to the original URL stored in the database with the ID that matches the hash KJ34.

      Lastly, you render the index.html template passing the short_url variable to it.

      After all the additions, the file will be as follows:

      flask_shortener/app.py

      import sqlite3
      from hashids import Hashids
      from flask import Flask, render_template, request, flash, redirect, url_for
      
      
      def get_db_connection():
          conn = sqlite3.connect('database.db')
          conn.row_factory = sqlite3.Row
          return conn
      
      
      app = Flask(__name__)
      app.config['SECRET_KEY'] = 'this should be a secret random string'
      
      hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])
      
      
      @app.route('/', methods=('GET', 'POST'))
      def index():
          conn = get_db_connection()
      
          if request.method == 'POST':
              url = request.form['url']
      
              if not url:
                  flash('The URL is required!')
                  return redirect(url_for('index'))
      
              url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
                                      (url,))
              conn.commit()
              conn.close()
      
              url_id = url_data.lastrowid
              hashid = hashids.encode(url_id)
              short_url = request.host_url + hashid
      
              return render_template('index.html', short_url=short_url)
      
          return render_template('index.html')
      

      Save and close the file.

      Next, you’ll create a base template and the index.html template file.

      In your flask_shortener directory, create a templates directory and open a file called base.html inside it:

      • mkdir templates
      • nano templates/base.html

      Add the following code inside base.html. Note that, for styling, you’re using Bootstrap here too. If you are not familiar with HTML templates in Flask, see Step 3 of How To Make a Web Application Using Flask in Python 3:

      flask_shortener/templates/base.html

      <!doctype html>
      <html lang="en">
        <head>
          <!-- Required meta tags -->
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      
          <!-- Bootstrap CSS -->
          <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
      
          <title>{% block title %} {% endblock %}</title>
        </head>
        <body>
          <nav class="navbar navbar-expand-md navbar-light bg-light">
              <a class="navbar-brand" href="https://www.digitalocean.com/community/tutorials/{{ url_for("index')}}">FlaskShortener</a>
              <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                  <span class="navbar-toggler-icon"></span>
              </button>
              <div class="collapse navbar-collapse" id="navbarNav">
                  <ul class="navbar-nav">
                  <li class="nav-item active">
                      <a class="nav-link" href="#">About</a>
                  </li>
                  </ul>
              </div>
          </nav>
          <div class="container">
              {% for message in get_flashed_messages() %}
                  <div class="alert alert-danger">{{ message }}</div>
              {% endfor %}
              {% block content %} {% endblock %}
          </div>
      
          <!-- Optional JavaScript -->
          <!-- jQuery first, then Popper.js, then Bootstrap JS -->
          <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
          <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        </body>
      </html>
      

      Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta> tags provide information for the web browser, the <link> tag links the Bootstrap CSS files, and the <script> tags are links to JavaScript code that allows some additional Bootstrap features. Check out the Bootstrap documentation for more information.

      The <title>{% block title %} {% endblock %}</title> tag allows the inheriting templates to define a custom title. You use the for message in get_flashed_messages() loop to display the flashed messages (warnings, alerts, and so on). The {% block content %} {% endblock %} placeholder is where inheriting templates place the content so that all templates have access to this base template, which avoids repetition.

      Save and close the file.

      Next, create the index.html file that will extend this base.html file:

      • nano templates/index.html

      Add the following code to it:

      flask_shortener/templates/index.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Welcome to FlaskShortener {% endblock %}</h1>
          <form method="post">
          <div class="form-group">
              <label for="url">URL</label>
              <input type="text" name="url"
                     placeholder="URL to shorten" class="form-control"
                     value="{{ request.form['url'] }}" autofocus></input>
          </div>
      
          <div class="form-group">
              <button type="submit" class="btn btn-primary">Submit</button>
          </div>
          </form>
      
          {% if short_url %}
          <hr>
          <span>{{ short_url }}</span>
          {% endif %}
      {% endblock %}
      

      Here you extend base.html, define a title, and create a form with an input named url. The url input will allow users to enter URLs to shorten. It has a value of request.form['url'], which stores data in cases of submission failure; that is if the user provides no URL. You also add a submit button.

      Then you check if the short_url variable has any value—this is true if the form submits and the short URL generates successfully. If the condition is true, you display the short URL under the form.

      Set the environment variables Flask needs and run the application using the following commands:

      • export FLASK_APP=app
      • export FLASK_ENV=development
      • flask run

      The FLASK_APP environment variable specifies the application you want to run (the app.py file). The FLASK_ENV environment variable specifies the mode. development means that the application will run in development mode with the debugger running. Remember to avoid using this mode in production. You run the application using the flask run command.

      Open a browser and type in the URL http://127.0.0.1:5000/. You will find a Welcome to FlaskShortener page.

      Flask Shortener Index page

      Submit a URL, and you will receive a short URL.

      Flask Shortened URL displayed beneath the URL input box

      You created a Flask application with a page that accepts URLs and generates shorter ones, but the URLs don’t do anything yet. In the next step, you’ll add a route that extracts the hash from the short URL, finds the original URL, and redirects users to it.

      Step 3 — Adding the Redirect Route

      In this step, you will add a new route that takes the short hash the application generates and decodes the hash into its integer value, which is the original URL’s ID. Your new route will also use the integer ID to fetch the original URL and increment the clicks value. Finally, you will redirect users to the original URL.

      First, open the app.py to add a new route:

      Add the following code to the end of the file:

      flask_shortener/app.py

      . . .
      
      @app.route('/<id>')
      def url_redirect(id):
          conn = get_db_connection()
      
          original_id = hashids.decode(id)
          if original_id:
              original_id = original_id[0]
              url_data = conn.execute('SELECT original_url, clicks FROM urls'
                                      ' WHERE id = (?)', (original_id,)
                                      ).fetchone()
              original_url = url_data['original_url']
              clicks = url_data['clicks']
      
              conn.execute('UPDATE urls SET clicks = ? WHERE id = ?',
                           (clicks+1, original_id))
      
              conn.commit()
              conn.close()
              return redirect(original_url)
          else:
              flash('Invalid URL')
              return redirect(url_for('index'))
      

      This new route accepts a value id through the URL and passes it to the url_redirect() view function. For example, visiting http://127.0.0.1:5000/KJ34 would pass the string 'KJ34' to the id parameter.

      Inside the view function, you first open a database connection. Then you use the decode() method of the hashids object to convert the hash to its original integer value and store it in the original_id variable. You check that the original_id has a value—meaning decoding the hash was successful. If it has a value, you extract the ID from it. As the decode() method returns a tuple, you fetch the first value in the tuple with original_id[0], which is the original ID.

      You then use the SELECT SQL statement to fetch the original URL and its number of clicks from the urls table, where the ID of the URL matches the original ID you extracted from the hash. You fetch the URL data with the fetchone() method. Next, you extract the data into the two original_url and clicks variables.

      You then increment the number of clicks of the URL with the UPDATE SQL statement.

      You commit the transaction and close the connection, and redirect to the original URL using the redirect() Flask helper function.

      If decoding the hash fails, you flash a message to inform the user that the URL is invalid, and redirect them to the index page.

      Save and close the file.

      Run your development server:

      Use your browser to go to http://127.0.0.1:5000/. Enter a URL and visit the resulting short URL; your application will redirect you to the original URL.

      You created a new route that redirects users from the short URL to the original URL. Next, you’ll add a page to show how many times each URL has been visited.

      Step 4 — Adding a Statistics Page

      In this step, you’ll add a new route for a statistics page that displays how many times each URL has been clicked. You’ll also add a button that links to the page on the navigation bar.

      Allowing users to see the number of visits each shortened link has received will provide visibility into each URL’s popularity, which is useful for projects, like marketing ad campaigns. You can also use this workflow as an example of adding a feature to an existing Flask application.

      Open app.py to add a new route for a statistics page:

      Add the following code to the end of the file:

      flask_shortener/app.py

      . . .
      
      @app.route('/stats')
      def stats():
          conn = get_db_connection()
          db_urls = conn.execute('SELECT id, created, original_url, clicks FROM urls'
                                 ).fetchall()
          conn.close()
      
          urls = []
          for url in db_urls:
              url = dict(url)
              url['short_url'] = request.host_url + hashids.encode(url['id'])
              urls.append(url)
      
          return render_template('stats.html', urls=urls)
      

      In this view function, you open a database connection. Then you fetch the ID, the creation date, the original URL, and the number of clicks for all of the entries in the urls table. You use the fetchall() method to get a list of all the rows. You then save this data in the db_urls variable and close the connection.

      To display the short URL for each entry, you will need to construct it and add it to each item in the list of the URLs you fetched from the database (db_urls). You create an empty list called urls and loop through the db_urls list with for url in db_urls.

      You use the dict() Python function to convert the sqlite3.Row object to a dictionary to allow assignment. You add a new key called short_url to the dictionary with the value request.host_url + hashids.encode(url['id']), which is what you used before to construct short URLs in the index view function. You append this dictionary to the urls list.

      Finally, you render a template file called stats.html, passing the urls list to it.

      Save and close the file.

      Next, create the new stats.html template file:

      • nano templates/stats.html

      Type the following code into it:

      flask_shortener/templates/stats.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} FlaskShortener Statistics {% endblock %}</h1>
          <table class="table">
              <thead>
                  <tr>
                  <th scope="col">#</th>
                  <th scope="col">Short</th>
                  <th scope="col">Original</th>
                  <th scope="col">Clicks</th>
                  <th scope="col">Creation Date</th>
                  </tr>
              </thead>
              <tbody>
                  {% for url in urls %}
                      <tr>
                          <th scope="row">{{ url['id'] }}</th>
                          <td>{{ url['short_url'] }}</td>
                          <td>{{ url['original_url'] }}</td>
                          <td>{{ url['clicks'] }}</td>
                          <td>{{ url['created'] }}</td>
                      </tr>
                  {% endfor %}
              </tbody>
          </table>
      
      {% endblock %}
      

      Here you extend the base.html base template by specifying a title and defining a table with the following columns:

      • #: The ID of the URL.
      • Short: The short URL.
      • Original: The original URL.
      • Clicks: The number of times a short URL has been visited.
      • Creation Date: The creation date of the short URL.

      Each row is filled using a for loop that goes through the urls list and displays the value of each column for each URL.

      Run the development server with the following:

      Use your browser to go to http://127.0.0.1:5000/stats. You will find all the URLs in a table.

      Statistics page with list of URLs and number of clicks

      Next, add a Stats button to the navigation bar. Open the base.html file:

      Edit the file as per the following highlighted lines:

      flask_shortener/templates/base.html

      <!doctype html>
      <html lang="en">
        <head>
          <!-- Required meta tags -->
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      
          <!-- Bootstrap CSS -->
          <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
      
          <title>{% block title %} {% endblock %}</title>
        </head>
        <body>
          <nav class="navbar navbar-expand-md navbar-light bg-light">
              <a class="navbar-brand" href="https://www.digitalocean.com/community/tutorials/{{ url_for("index')}}">FlaskTodo</a>
              <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                  <span class="navbar-toggler-icon"></span>
              </button>
              <div class="collapse navbar-collapse" id="navbarNav">
                  <ul class="navbar-nav">
                  <li class="nav-item active">
                      <a class="nav-link" href="#">About</a>
                  </li>
      
                  <li class="nav-item active">
                      <a class="nav-link" href="https://www.digitalocean.com/community/tutorials/{{ url_for("stats')}}">Stats</a>
                  </li>
                  </ul>
              </div>
          </nav>
          <div class="container">
              {% for message in get_flashed_messages() %}
                  <div class="alert alert-danger">{{ message }}</div>
              {% endfor %}
              {% block content %} {% endblock %}
          </div>
      
          <!-- Optional JavaScript -->
          <!-- jQuery first, then Popper.js, then Bootstrap JS -->
          <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
          <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        </body>
      </html>
      

      Here you incorporate a new <li> item to the navigation bar. You use the url_for() function to link to the stats() view function. You can now access the statistics page from the navigation bar.

      Your statistics page shows information about each URL, including its shorter equivalent and how many times it has been visited.

      You can reuse this code for monitoring number of clicks in other contexts, such as keeping track of how many times a post has been liked or updated on a social media site or how many times a photo/video has been viewed.

      You can access the full code for the application from this repository.

      Conclusion

      You have created a Flask application that allows users to enter a long URL and generate a shorter version. You have transformed integers into short string hashes, redirected users from one link to another, and set up a page for statistics so you can monitor shortened URLs. For further projects and tutorials on working with Flask, check out the following tutorials:



      Source link