Stub

NetCat

NetCat (nc) is used to read and write to TCP and UDP network connections. It is an excellent tool for diagnosing network connectivity issues.

Installation

sudo apt-get install netcat

Usage

To listen to a specific port.

nc -l -p 8000

To write to a specific port

nc 127.0.0.1 8000
nc -v 127.0.0.1 8000

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/

multipart/form-data

Browser HTML Form

        <form action="/multipart-data?id=x" method="post" enctype="multipart/form-data">
            <b>multipart/form-data</b><br>
            <span>jobid</span>
            <input type="number" name="jobid" value="0"><br>
            <span>jobname</span>
            <input type="text" name="jobname" value=""><br>
            <input type="submit"><br>
        </form>

Server Express Router

import express from "express";
import bodyParser from "body-parser";

const router = express.Router();

router.post("/multipart-data",
    log,
    multer({ dest: 'uploads/' }).none(),
    printBody
);

function log(req, res, next) {
    console.log(req.method + ` ` + req.originalUrl);
    next();
}

function printBody(req, res, next){
    console.log(req.body);
    console.log(req.query);
    next();
}

export default router;

Terminal Output

POST /justdata
{ jobid: '0', jobname: 'asdf' }
{ id: 'x' }

Request Headers

Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
Accept-Encoding: gzip, deflate, br
Accept-Language: en-CA,en-GB;q=0.9,en-US;q=0.8,en;q=0.7
Cache-Control: no-cache
Connection: keep-alive
Content-Length: 234
Content-Type: multipart/form-data; boundary=----WebKitFormBoundaryB2AGWn1yYcOCi0T9

Request Body

------WebKitFormBoundaryB2AGWn1yYcOCi0T9
Content-Disposition: form-data; name="jobid"

0
------WebKitFormBoundaryB2AGWn1yYcOCi0T9
Content-Disposition: form-data; name="jobname"

sdf
------WebKitFormBoundaryB2AGWn1yYcOCi0T9--

application/x-www-form-urlencoded

Browser HTML Form

        <form action="/justdata?id=x" method="post" enctype="application/x-www-form-urlencoded">
            <b>application/x-www-form-urlencoded</b><br>
            <span>jobid</span>
            <input type="number" name="jobid" value="0"><br>
            <span>jobname</span>
            <input type="text" name="jobname" value=""><br>
            <input type="submit"><br>
        </form>

Server Express Router

import express from "express";
import bodyParser from "body-parser";

const router = express.Router();

router.post("/justdata",
    log,
    bodyParser.urlencoded({ extended: true }),
    printBody
);

function log(req, res, next) {
    console.log(req.method + ` ` + req.originalUrl);
    next();
}

function printBody(req, res, next){
    console.log(req.body);
    console.log(req.query);
    next();
}

export default router;

Terminal Output

POST /justdata
{ jobid: '0', jobname: 'asdf' }
{ id: 'x' }

Request Headers

Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
Accept-Encoding: gzip, deflate, br
Accept-Language: en-CA,en-GB;q=0.9,en-US;q=0.8,en;q=0.7
Cache-Control: no-cache
Connection: keep-alive
Content-Length: 20
Content-Type: application/x-www-form-urlencoded

Request Body

jobid=0&jobname=asdf

MySQL Server on Ubuntu 22.04 (WSL)

Install and Setup

sudo apt update
sudo apt upgrade
sudo apt install mysql-server
sudo service mysql start
sudo service mysql status
sudo mysql
sql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'password';
sudo mysql_secure_installation

Add User

mysql -u root -p
sql> CREATE USER 'MyUsername'@'localhost' IDENTIFIED BY 'password';
sql> CREATE DATABASE datebase_name;
sql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON database_name.* TO 'MyUsername'@'localhost';
sql> quit

Access Database

mysql -u MyUsername -p
sql> show databases;
sql> use database_name;
GRANT ALL ON demo.* TO 'demo'@'localhost';
mysql --login-path=demo

https://www.prisma.io/dataguide/mysql/tools/mysql-config-editor#:~:text=information%20in%20the%20.-,mylogin.,client%20%2C%20if%20it%20is%20defined.