SSO 我调你老味 03: 我看 SQL 不错

前言

小白的朋友们发现这个 service 非常的新鲜、非常的美味，非常想要成为会员呐。

这个时候，她想起了古神的力量。

SQL!

首先，她去创建了一个数据库：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- initDb.sql
CREATE DATABASE credentials;

CREATE TABLE user_data (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL UNIQUE,
    user_password VARCHAR(255) NOT NULL,
    access_token VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
)

读

这个时候 app/api/auth/route.ts 就没办法写那么简单了，烦内。

首先设置一下服务器的相关参数：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


// app/config/database.ts
import { Sequelize } from "sequelize";

export const sequelize = new Sequelize(
  "credentials",
  "sql_username",
  "sql_password",
  {
    host: "localhost",
    dialect: "mysql",
    logging: false,
  }
);

然后用 sequelize 给数据库建模：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46


// app/models/userData.ts
import { DataTypes } from "sequelize";
import { sequelize } from "../config/database";

export const UserData = sequelize.define(
  "UserData",
  {
    id: {
      type: DataTypes.BIGINT,
      primaryKey: true,
      autoIncrement: true,
    },
    username: {
      type: DataTypes.STRING(100),
      allowNull: false,
      unique: true,
    },
    user_password: {
      type: DataTypes.STRING(255),
      allowNull: false,
    },
    access_token: {
      type: DataTypes.STRING(255),
      allowNull: true,
    },
    access_token_expiry_time: {
      type: DataTypes.DATE,
      allowNull: true,
    },
    created_at: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
    },
    updated_at: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
    },
  },
  {
    tableName: "user_data",
    timestamps: true,
    createdAt: "created_at",
    updatedAt: "updated_at",
    underscored: true,
  }
);

然后随便写一个读取的函数：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


// app/api/utils.ts
import { UserData } from "../../models/userData";
export async function findUser(username: string) {
  try {
    const user = await UserData.findOne({
      where: { username },
    });
    return user;
  } catch (e) {
    console.error("Error occurred: ", e);
  }
}

再在 auth/route.ts 这里调用一下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


// app/api/auth/route.ts

import { findUser } from "../utils";

export async function POST(request: NextRequest) {
  const json = await request.json();
  const user = await findUser(json.username);
  if (user === null) {
    return NextResponse.json({ status: "failed", message: "user out!" });
  }
  const recordedPassword = user?.get("password");

  // ...
}

然后因为直接使用 === 比对用户提供的密码以及数据库中保存的密码容易被时序攻击 (timing attack)，所以小白决定给之前的 auth 函数上点高级一点的玩意。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


// app/api/auth/route.ts

import * as bcrypt from "bcrypt";

async function authenticate(
  recordedHashedPassword: string,
  providedPlaintextPassword: string
) {
  return bcrypt.compare(providedPlaintextPassword, recordedHashedPassword);
}

接下来，按照之前写的逻辑进行判定就行了，小白开心坏了。

写

这玩意不是手拿把掐？（

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


// app/api/register/route.ts

import { UserData } from "@/app/models/userData";
import { findUser } from "../utils";
import * as bcrypt from "bcrypt";

export async function POST(request: NextRequest) {
  const json = await request.json();
  const { username, user_password } = json;
  if (username === null || user_password === null) {
    return NextResponse.json(
      { status: "failed", message: "Invalid username or password" },
      { status: 400 }
    );
  }
  const existedUser = await findUser(username);
  if (existedUser !== null) {
    return NextResponse.json(
      {
        status: "failed",
        message: "User with this username is existed. ",
      },
      { status: 400 }
    );
  }

  const hashedPassword = await bcrypt.hash(user_password, 10);

  await UserData.create({
    username: username,
    user_password: hashedPassword,
  });

  return NextResponse.json({ status: "success" });
}

一些改动

总之 accessToken 更安全地生成一个就行了嘛。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


// app/api/auth/route.js
import crypto from "crypto";

function generateAccessToken(length: number = 32) {
  const alphabet =
    "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
  let result = "";
  const randomBytes = crypto.randomBytes(length);

  for (let i = 0; i < length; i++) {
    result += alphabet[randomBytes[i] % alphabet.length];
  }

  return result;
}

这是好的。