MariaDB | Setup & Integration *

/ CHEAT SHEETS, CODING, KEEPERS, SprUng, TOOLS / 2 minutes of reading

Yoti MariaDB Setup & Integration

🔥 Do We Even Need Cookies?

(Discussion on whether cookies are necessary or if the database is a better solution.)

📌 Setting Up MariaDB

Creating the Database

CREATE DATABASE yoti_tracking;

Creating a Database User

CREATE USER 'yoti_user'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON yoti_tracking.* TO 'yoti_user'@'localhost';
FLUSH PRIVILEGES;

Checking Users in MariaDB

SELECT user, host FROM mysql.user;

Logging in with the New User

mysql -u yoti_user -p

📌 Creating the yoti_sessions Table

CREATE TABLE yoti_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_session_id VARCHAR(255) NOT NULL,
    yoti_session_id VARCHAR(255) DEFAULT NULL,
    status ENUM('PENDING', 'IN_PROGRESS', 'COMPLETE', 'FAIL', 'ERROR') DEFAULT 'PENDING',
    return_url TEXT NOT NULL,
    avs_required TINYINT(1) DEFAULT 0,
    nats_code VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    callback_url VARCHAR(500) NOT NULL,
    cancel_url VARCHAR(500) NOT NULL,
    notification_url VARCHAR(500) NOT NULL,
    extra_segment VARCHAR(255) DEFAULT NULL,
    site_version VARCHAR(255) DEFAULT NULL,
    base_url VARCHAR(500) NOT NULL,
    root_domain VARCHAR(255) NOT NULL,
    rule_id VARCHAR(255) NOT NULL,
    yoti_reference_id VARCHAR(255) NOT NULL,
    method VARCHAR(50) DEFAULT NULL,
    age INT DEFAULT NULL,
    evidence_id VARCHAR(100) DEFAULT NULL,
    biometric_consent_required BOOLEAN DEFAULT 0,
    biometric_consent_given_at DATETIME NULL,
    retry_enabled BOOLEAN DEFAULT 0,
    resume_enabled BOOLEAN DEFAULT 0,
    yav_cookie_value TEXT DEFAULT NULL,
    plain_join_link VARCHAR(500) DEFAULT NULL
);

📌 Inserting Test Data

INSERT INTO yoti_sessions (user_session_id, yoti_session_id, status, return_url, avs_required, nats_code, created_at, updated_at)
VALUES ('test_user_456', 'yoti_test_789', 'PENDING', 'https://tubes.nakedsword.com/yoti-token/', 1, 'MTA1OTUyLjYzLjEuMTY3LjAuMC4wLjAuMA', NOW(), NOW());

📌 Retrieving Data

SELECT * FROM yoti_sessions;

📌 Integrating with PHP

Database Connection (db.php)

<?php
$dsn = 'mysql:host=localhost;dbname=yoti_tracking;charset=utf8mb4';
$username = 'yoti_user';
$password = 'your_strong_password';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];
try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    die('❌ Database connection failed: ' . $e->getMessage());
}
?>

Inserting Data via PHP

<?php
require '/home/httpd/html/tubes.nakedsword.com/php_includes/db.php';

$query = "INSERT INTO yoti_sessions (user_session_id, yoti_session_id, status, return_url, avs_required, nats_code, created_at, updated_at)
          VALUES (:user_session_id, :yoti_session_id, :status, :return_url, :avs_required, :nats_code, NOW(), NOW())";

$stmt = $pdo->prepare($query);
$stmt->execute([
    'user_session_id' => 'test_user_auto',
    'yoti_session_id' => 'yoti_auto_123',
    'status' => 'PENDING',
    'return_url' => 'https://tubes.nakedsword.com/yoti-token/',
    'avs_required' => 1,
    'nats_code' => 'MTA1OTUyLjYzLjEuMTY3LjAuMC4wLjAuMA'
]);

echo "✅ Test data inserted successfully!";
?>

Fetching Data via PHP

<?php
require '/home/httpd/html/tubes.nakedsword.com/php_includes/db.php';
$query = "SELECT * FROM yoti_sessions";
$stmt = $pdo->query($query);
$result = $stmt->fetchAll();
print_r($result);
?>

📌 Notes & Next Steps

  • Verify session handling.
  • Ensure all expected values persist in the database.
  • Evaluate whether cookies are still required.
  • Continue testing with real user interactions.
Scroll to Top