iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article
📝

Creating a Login System with Nginx, PHP, and MariaDB

に公開
MySQL
PHP
nginx
MariaDB
tech

I will try to add a login function to the bulletin board created in the previous article.

Preparation

Add a table to the database.

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 157
Server version: 10.3.29-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE bbs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [bbs]> SHOW TABLES;  # Created last time
+---------------+
| Tables_in_bbs |
+---------------+
| kakiko        |
+---------------+
2 rows in set (0.000 sec)

MariaDB [bbs]> CREATE TABLE user (  # Creating a user management table
    -> name varchar(64) NOT NULL PRIMARY KEY,
    -> hash varchar(256) NOT NULL,
    -> sid varchar(256)
    -> );
Query OK, 0 rows affected (0.024 sec)

MariaDB [bbs]> DESC user;  # Checking
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(64)  | NO   | PRI | NULL    |       |
| hash  | varchar(256) | NO   |     | NULL    |       |
| sid   | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [bbs]> # Granting permissions to the user
MariaDB [bbs]> GRANT SELECT,INSERT,UPDATE ON user TO bbsuser@localhost IDENTIFIED BY 'bbspasswd';
Query OK, 0 rows affected (0.001 sec)

MariaDB [bbs]> QUIT
Bye

Creating and Editing Scripts

Edit each script. In the previous article, the bulletin board's posting process was split into a separate script, but this time they are consolidated into one. Also, the contributor's name will be the one entered during login.

$ sudo vim /var/www/html/bbs/index.php
$ sudo vim /var/www/html/bbs/signin.php
$ sudo vim /var/www/html/bbs/signup.php
$ sudo vim /var/www/html/bbs/signout.php
/var/www/html/bbs/index.php
<?php
const dsn = 'mysql:host=localhost;dbname=bbs';
const dbuser = 'bbsuser';
const dbpass = 'bbspasswd';
const recvsql = 'INSERT INTO kakiko (name,msg,date) VALUES (:name,:msg,:date)';
const dispsql = 'SELECT id,name,msg,date FROM kakiko';
const namesql = 'SELECT name FROM user WHERE sid=?';
const sname = 'BBSLOGIN';

session_name(sname);
session_start();

# If not logged in, go to ./signin.php
if (empty($_SESSION['sid'])) {
  header('HTTP/1.1 303 See Other');
  header('Location: ./signin.php');
  exit();
}

# Get user name
try {
  $pdo = new PDO(dsn, dbuser, dbpass);
  $stmt = $pdo->prepare(namesql);
  $stmt->execute([$_SESSION['sid']]);
} catch (PDOException $e) {
  $error = $e->getMessage();
  die("Something Wrong ($error)");
}
$user = $stmt->fetchColumn();

# If POST (= post received), process writing
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  if (empty($_POST['msg']))
    goto endp;

  # Write the post
  try {
    $pdo = new PDO(dsn, dbuser, dbpass);
    $stmt = $pdo->prepare(recvsql);
    $stmt->bindValue(':name', $user, PDO::PARAM_STR);
    $stmt->bindValue(':msg', $_POST['msg'], PDO::PARAM_STR);
    $stmt->bindValue(':date', date('Y-m-d H:i:s'), PDO::PARAM_STR);
    $stmt->execute();
  } catch (PDOException $e) {
    $error = $e->getMessage();
    die("Something Wrong ($error)");
  }

endp:
  header('HTTP/1.1 303 See Other');
  header('Location: ./');
  exit();
}

# Escape the user name
$user = htmlspecialchars($user);

# Get the list of posts
try {
  $stmt = $pdo->prepare(dispsql);
  $stmt->execute();
} catch (PDOException $e) {
  $error = $e->getMessage();
  die("Something Wrong ($error)");
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Simple Bulletin Board</title>
</head>
<body>
<h1>Simple Bulletin Board</h1>
<div>
<?php while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { ?>
<div class="kakiko">
<div class="kakiko_header">
<span class="id"><?= $row['id'] ?></span>
<span class="name"><?= htmlspecialchars($row['name']) ?></span>
</div>
<div>
<pre class="msg"><?= htmlspecialchars($row['msg']) ?></pre>
</div>
<div class="date">
<?= $row['date'] ?>
</div>
</div>
<hr>
<?php } /* while */ ?>
</div>
<form action="./" method="POST">
<fieldset>
<legend>New Post(https://)</legend>
<div>
<label for="msg">Post Content:</label>
<div>
<textarea name="msg" id="msg" required></textarea>
</div>
</div>
<hr>
<input type="submit">
<input type="reset">
</fieldset>
</form>
<p><a href="./signout.php">Sign out</a></p>
</body>
</html>
/var/www/html/bbs/signin.php
<?php
const dsn = 'mysql:host=localhost;dbname=bbs';
const dbuser = 'bbsuser';
const dbpass = 'bbspasswd';
const insql = 'UPDATE user SET sid=? WHERE name=?';
const cksql = 'SELECT COUNT(*) FROM user WHERE name=?';
const hasql = 'SELECT hash FROM user WHERE name=?';
const sname = 'BBSLOGIN';

# If POST (= login information received)
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  if (empty($_POST['name']) || empty($_POST['pass']))
    goto endp;

  # Is there a match for the username?
  try {
    $pdo = new PDO(dsn, dbuser, dbpass);
    $stmt = $pdo->prepare(cksql);
    $stmt->execute([$_POST['name']]);
  } catch (PDOException $e) {
    $error = $e->getMessage();
    die("Something Wrong ($error)");
  }
  if ($stmt->fetchColumn() === '0') {
    $name = $_POST['name'];
    die("Unknown name. ($name)");
  }

  # Does the password match?
  try {
    $stmt = $pdo->prepare(hasql);
    $stmt->execute([$_POST['name']]);
  } catch (PDOException $e) {
    $error = $e->getMessage();
    die("Something Wrong ($error)");
  }
  $hash = $stmt->fetchColumn();

  if (!password_verify($_POST['pass'], $hash)) {
    die("Wrong Password");
  }

  # Start session
  session_name(sname);
  session_start();
  session_regenerate_id(true);
  $_SESSION['sid'] = exec('uuidgen');

  # Write back the session ID
  try {
    $stmt = $pdo->prepare(insql);
    $stmt->execute([$_SESSION['sid'], $_POST['name']]);
  } catch (PDOException $e) {
    $error = $e->getMessage();
    die("Something Wrong ($error)");
  }

endp:
  header('HTTP/1.1 303 See Other');
  header('Location: ./');
  exit();
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Sign in</title>
</head>
<body>
<h1>Sign in</h1>
<p>
Or <a href="./signup.php">sign up</a>
</p>
<form METHOD="POST">
<fieldset>
<legend>Sign in</legend>
<div>
<label for="name">Name</label>
<input type="text" name="name" id="name" required>
</div>
<div>
<label for="pass">Password:</label>
<input type="password" name="pass" id="pass" required>
</div>
<input type="submit">
</fieldset>
</form>
</body>
</html>
/var/www/html/bbs/signup.php
<?php
const dsn = 'mysql:host=localhost;dbname=bbs';
const dbuser = 'bbsuser';
const dbpass = 'bbspasswd';
const insql = 'INSERT INTO user (name,hash,sid) VALUES (:name,:hash,:sid)';
const cksql = 'SELECT COUNT(*) FROM user WHERE name=?';
const sname = 'BBSLOGIN';

# If POST (= registration information received)
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  if (empty($_POST['name']) || empty($_POST['pass']))
    goto endp;

  # Is the same username already in use?
  try {
    $pdo = new PDO(dsn, dbuser, dbpass);
    $stmt = $pdo->prepare(cksql);
    $stmt->execute([$_POST['name']]);
  } catch (PDOException $e) {
    $error = $e->getMessage();
    die("Something Wrong ($error)");
  }
  if ($stmt->fetchColumn() !== '0') {
    $name = $_POST['name'];
    die("The name is used. ($name)");
  }
  
  # Register a new user while performing the login process
  try {
    $stmt = $pdo->prepare(insql);
    $stmt->bindValue(':name', $_POST['name'], PDO::PARAM_STR);
    $stmt->bindValue(':hash',
      password_hash($_POST['pass'], PASSWORD_DEFAULT), PDO::PARAM_STR);
    $stmt->bindValue(':sid', $sid = exec('uuidgen'), PDO::PARAM_STR);
    $stmt->execute();
  } catch (PDOException $e) {
    $error = $e->getMessage();
    die("Something Wrong ($error)");
  }

  # Start session
  session_name(sname);
  session_start();
  session_regenerate_id(true);
  $_SESSION['sid'] = $sid;

endp:
  header('HTTP/1.1 303 See Other');
  header('Location: ./');
  exit();
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Sign up</title>
</head>
<body>
<h1>Sign up</h1>
<p>
Or <a href="./signin.php">sign in</a>
</p>
<form METHOD="POST">
<fieldset>
<legend>Sign up</legend>
<div>
<label for="name">Name:</label>
<input type="text" name="name" id="name" required>
</div>
<div>
<label for="pass">Password:</label>
<input type="password" name="pass" id="pass" required>
</div>
<input type="submit">
</fieldset>
</form>
</body>
</html>
/var/www/html/bbs/signout.php
<?php
const dsn = 'mysql:host=localhost;dbname=bbs';
const dbuser = 'bbsuser';
const dbpass = 'bbspasswd';
const insql = 'UPDATE user SET sid=NULL WHERE sid=?';
const sname = 'BBSLOGIN';

session_name(sname);
session_start();

if (empty($_SESSION['sid']))
  goto endp;

# Invalidate session ID
try {
  $pdo = new PDO(dsn, dbuser, dbpass);
  $stmt = $pdo->prepare(insql);
  $stmt->execute([$_SESSION['sid']]);
} catch (PDOException $e) {
  $error = $e->getMessage();
  die("Something Wrong ($error)");
}

# Delete cookies
$_SESSION = array();
if (ini_get('session.use_cookies')) {
  $params = session_get_cookie_params();
  setcookie(session_name(), '', time() - 42000, $params['path'],
    $params['domain'], $params['secure'], $params['http_only']);
}

# Destroy session
session_destroy();

endp:
header('HTTP/1.1 303 See Other');
header('Location: ./');

Conclusion

That's all.

I am not familiar with security best practices, so if you are an expert, please let me know.

Discussion