Security

SQL Interface (no account security)

User accounts and passwords are currently unsupported. Normally, a database is behind the middleware / proxy / API tool which provides the external security.

No real authentication or authorization (always guest mode with admin rights) - fixed in the next release of the dependencies https://github.com/go-mysql-org/go-mysql/pull/1129

API Interface (account security available)

The API uses state-of-the-art encrypted passwords and JWT-tokens.

You cannot login with the root user via API or user interface Crystal. Use the CLI mode. Register a new user in Crystal or create a user via the root user in the CLI.

Authorizer and grant and revoke rights

The root user have to renamed + password changed to a secure password based on NIST SP800-63b:

15 characters long or more
Strong and complex
Random
Not breached

You can change the enviroment variables

ROOTUSERNAME=root
ROOTPASSWORD=test

Swap JWT_SECRET with also a random string

JWT_SECRET=435jlfadslfjlw243jajsdf

! The authorizer creates new users and provides the access rights. The newly created user cannot have more rights than the authorizer.

Avoiding Injections

The database currently does not support prepared statements or nested queries. Ensure that all inputs as values are validated by an API before you feed it into the database.

<?php
// Sample user input (e.g., from a form)
$username = $_POST['username'] ?? '';
$email = $_POST['email'] ?? '';
$age = $_POST['age'] ?? '';

// Step 1: Sanitize inputs
$sanitizedUsername = trim($username);
$sanitizedEmail = filter_var(trim($email), FILTER_SANITIZE_EMAIL);
$sanitizedAge = filter_var($age, FILTER_SANITIZE_NUMBER_INT);

// Step 2: Validate inputs
$errors = [];

// Validate username
if (empty($sanitizedUsername)) {
    $errors[] = "Username is required.";
} elseif (strlen($sanitizedUsername) < 3 || strlen($sanitizedUsername) > 50) {
    $errors[] = "Username must be between 3 and 50 characters.";
} elseif (!preg_match('/^[a-zA-Z0-9_]+$/', $sanitizedUsername)) {
    $errors[] = "Username can only contain letters, numbers, and underscores.";
}

// Validate email
if (empty($sanitizedEmail)) {
    $errors[] = "Email is required.";
} elseif (!filter_var($sanitizedEmail, FILTER_VALIDATE_EMAIL)) {
    $errors[] = "Invalid email format.";
}

// Validate age
if (empty($sanitizedAge)) {
    $errors[] = "Age is required.";
} elseif (!filter_var($sanitizedAge, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1, 'max_range' => 120]])) {
    $errors[] = "Age must be between 1 and 120.";
}

// If there are errors, display them
if (!empty($errors)) {
    foreach ($errors as $error) {
        echo "<p>$error</p>";
    }
    die();
}

// Step 3: Escape the sanitized input for SQL (MySQLi)
$servername = "localhost";
$serverUsername = "your_username";
$serverPassword = "your_password";
$dbname = "your_database";

$conn = new mysqli($servername, $serverUsername, $serverPassword, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Escape the inputs to prevent SQL injection (basic protection)
$escapedUsername = $conn->real_escape_string($sanitizedUsername);
$escapedEmail = $conn->real_escape_string($sanitizedEmail);
$escapedAge = $conn->real_escape_string($sanitizedAge);

// Construct the SQL query (NOT RECOMMENDED for production)
$sql = "INSERT INTO users (username, email, age) VALUES ('$escapedUsername', '$escapedEmail', '$escapedAge')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Open Registration

Everyone can register itself without a extended verification for a new user + database when the API / UI is running. The new usere cannot access other databases.

VGwort

Last updated: