sql

MySQL in C#

Package Introduction

Database access is a core aspect of many applications. C# can interface with many different databases such as Oracle, Microsoft SQL, and MongoDB. In this case, we will be working with MySQL. The first step is to add the MySQL package to your project.

dotnet add package MySql.Data

Saving Your Credentials

When you run a client program to connect to the MySQL server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. We will store our credentials in a .env file and load them into the environment variables. The code to load .env files is trivial, so we will just add it directly to the project. Add the .env file to your .gitignore so that you don’t upload your credentials to your code repository.

class DotEnv {
    public static void Load() {
        string filepath = Path.Combine(
            Directory.GetCurrentDirectory(),
            ".env"
        );

        DotEnv.Load(filepath);
    }

    public static void Load(string filePath) {
        if (!File.Exists(filePath)) return;

        foreach (var line in File.ReadAllLines(filePath)) {
            var parts = line.Split('=',StringSplitOptions.RemoveEmptyEntries);
            if (parts.Length != 2) continue;
            if (Environment.GetEnvironmentVariable(parts[0]) != null) continue;
            Environment.SetEnvironmentVariable(parts[0], parts[1]);
        }
    }
}

Example .env File

SQL_USER=username
SQL_PW=supersecret
SQL_SERVER=localhost

Creating a Connection

Before you can perform database operations, you need to establish a connection. This is achieved with a MySqlConnection object. We will add the variables from the .env file with string interpolation.

using MySql.Data.MySqlClient;

DotEnv.Load();
var env = Environment.GetEnvironmentVariables();

string cs = @$"
               server={env["SQL_SERVER"]};
               userid={env["SQL_USER"]};
               password={env["SQL_PW"]};
               database={env["SQL_DB"]}
            ";

var con = new MySqlConnection(cs);
con.Open();
Console.WriteLine($"MySQL version : {con.ServerVersion}");
con.Close();

Prepared Statements

After the connection has been established you will typically perform one or more operations, this is enabled by the MySqlCommand object. There are three primary methods that you will usually use:

  • ExecuteReader: Query the database.
  • ExecuteNonQuery: Insert, Update, Delete data.
  • ExecuteScalar: Retrieve a single value.

Although possible to build SQL queries using string interpolation, it is not advisable to put query values directly in the query string. Doing so leaves your database open to injection attacks. Instead of placing the value into the query directly, we will use placeholders, prefixed with ‘@’.

Unsafe

string sql = $"SELECT Name FROM Country WHERE Continent = {continent};

Safe

string sql = "SELECT Name FROM Country WHERE Continent = @continent;

When using parameterized strings the values are added using the Parameters field of the MySqlCommand object.

Update the Database

To insert a new record into the database we use the ExecuteNonQuery method.


MySqlCommand.ExecuteNonQuery()

Executes a SQL statement returning the affected row count. [*]

        string sql = "INSERT INTO users(username, password, email) values (@username, @password, @email)";
        using var cmd = new MySqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@username", username);
        cmd.Parameters.AddWithValue("@password", password);
        cmd.Parameters.AddWithValue("@email", email);
        cmd.ExecuteNonQuery();

Query the Database

Sends the CommandText value to MySqlConnection and builds a MySqlDataReader object.

The Reader object contains the rows that result from the SQL command execution. The Read method advances the reader to the next row, returning true if a row exists. The using keyword ensures that classes that implement the IDisposable interface call their dispose method.

using (MySqlDataReader reader = cmd.ExecuteReader()){
    while (reader.Read()) {
        ...
    }
}

You retrieve values from the current row with the class indexer which can retrieve values by column index or name. There are also several methods to retrieve a value by type, for example, GetInt32. The following statements perform the same action.

var name = reader[0];
var name = reader["name"];
var name = reader.GetString(0);
var name = reader.GetString("name");

External Reading

Official MySQL/Net Tutorial

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html

Nuget MySQL Package

https://www.nuget.org/packages/MySql.Data/

MySQL/Net API

https://dev.mysql.com/doc/dev/connector-net/8.0/

phpMyAdmin

About

The phpMyAdmin tool is an SQL database manager written in PHP. It resides in the web root on your server, making it easily accessed from a web browser.

There are a number important security considerations when using software like phpMyAdmin, since it:

  • Communicates directly with your MySQL installation.
  • Handles authentication using MySQL credentials.
  • Executes and returns results for arbitrary SQL queries.

The phpMyAdmin tool is available for a number of different operating systems. This article will focus solely on the Linux installation.

Manual Installation

While there are a number of ways to install phpMyAdmin, there is an easy quick install method. This involves downloading and extracting it directly to your web root directory. While you will need at least basic bash terminal knowledge, it is relatively trivial to set up. However, you will require sudo privileges or access to the web-user-account.

Following are the condensed steps for the quick install found in the phpMyAdmin documentation.

cd /www
sudo wget https://files.phpmyadmin.net/phpMyAdmin/5.1.1/phpMyAdmin-5.1.1-english.tar.gz -O phpMyAdmin.tar.gz
sudo tar -xvf phpMyAdmin.tar.gz
sudo chown -R wp-user:wp-user phpMyAdmin-5.1.1-english/
sudo cp config.sample.inc.php config.inc.php
  1. Change directory to your web root.
  2. Download the tar file. You can choose from a number of options here. We rename it here with the -O flag
  3. Unpack the tar, it will be placed in it’s own directory.
  4. Give ownership of the directory to the web user account.
  5. Create the configuration file by copying the sample configuration file.

Usage

When a user logs into phpMyAdmin the username and password are sent directly to the SQL database. It is just an interface to the database, and any operation it does can be done on the command line. As such, all users must be valid database users.

Securing

You should use cookie mode authentication so that your user/password pair are not kept in the configuration file. The variable may be set in the example config file as:

$cfg['Servers'][$i]['auth_type'] = 'cookie';

You will need to also add a 'blowfish secret' value to the config file.

$cfg['blowfish_secret'] = 'anyrandomtextyouwant';

Deny access to the temp, libraries, and templates subdirectories. Put the following in the server directive of your nginx enabled sites file.

location /phpmyadmin/libraries { deny all; }
location /phpmyadmin/templates { deny all; }
location /phpmyadmin/tmp { deny all; }

References

https://www.phpmyadmin.net/

https://docs.phpmyadmin.net/en/latest