相同代码本地正常运行但服务器端异常，求排查解决方法

本地端响应：

服务器端响应：

以下是相关业务逻辑代码：

async fullSignup(req, res, next) {
    const adminDetailSchema = Joi.object({
      // admin signUp valildation
      username: Joi.string().min(3).max(30).required().regex(/^\w+$/).messages({
        "string.base": "Username must be a string",
        "string.empty": "Username cannot be empty",
        "string.min": "Username must have at least {{#limit}} characters",
        "string.max": "Username must have at most {{#limit}} characters",
        "string.pattern.base": 
          "Username must only contain letters, numbers, or underscores",
        "any.required": `Username is a required field`,
      }),
      email: Joi.string().email().required().messages({
        "string.base": `Email should be a type of 'text'`,
        "string.empty": `Email cannot be empty`,
        "string.email": `Please enter a valid email address`,
        "any.required": `Email is a required field`,
      }),
      password: Joi.string()
        // .pattern(new RegExp(/^(?=.*[A-Z])(?=.*[!@#$&*])(?=.*[0-9]).{8,}$/))
        .min(5)
        .required()
        .messages({
          // "string.pattern.base": `Password must be between 8 characters and can only contain letters, numbers and 1-capital letter, 1-special character`,
          "string.min": "Password must have at least {{#limit}} characters",
          "string.empty": `Password cannot be empty`,
          "any.required": `Password is a required field`,
        }),
      // admin details valildation
      name: Joi.string().required().messages({
        "string.base": "name filed must be a string",
        "string.empty": "name cannot be empty",
        "any.required": `name is a required field`,
      }),
      company_name: Joi.string().required().messages({
        "string.base": "company_name filed must be a string",
        "string.empty": "company_name cannot be empty",
        "any.required": `company_name is a required field`,
      }),
      phone_number: Joi.string()
        .pattern(/^\+?[1-9]\d{1,9}$/)
        .min(10)
        .message("phone_number length must be at least 10 Digits")
        .required()
        .messages({
          "string.base": "phone_number filed must be a string",
          "string.empty": "phone_number cannot be empty",
          "string.pattern.base": "Please enter a valid phone number",
          "any.required": `phone_number is a required field`,
        }),
      address: Joi.string().required().messages({
        "string.base": "address filed must be a string",
        "string.empty": "address cannot be empty",
        "any.required": `address is a required field`,
      }),
      city: Joi.string().required().messages({
        "string.base": "city filed must be a string",
        "string.empty": "city cannot be empty",
        "any.required": `city is a required field`,
      }),
      pincode: Joi.number().optional().messages({
        "number.base": "pincode filed must be a number",
        // "number.empty": "pincode cannot be empty",
        // "any.required": `pincode is a required field`,
      }),
      image: Joi.string()
        .pattern(/\.(jpg|jpeg|png)$/i)
        // .required()
        .max(5242880) // 5MB in bytes
        .messages({
          "string.pattern.base": "Image must be a valid JPEG, JPG, or PNG file",
          "string.max": "Image size must be less than 5MB",
        }),
    }).options({ abortEarly: false });

    const { error } = adminDetailSchema.validate(req.body);

    if (error) {
      return next(error);
    }

    let {
      email,
      password,
      username,
      name,
      company_name,
      phone_number,
      address,
      city,
      pincode,
    } = req.body;

    // ! signup data

    try {
      // ? check valid email
      let doc = await emailValidator.validate(email);
      if (!doc.valid)
        return next(CustomeErrorHandler.validation("Enter valid email!"));

      // ? query of username validation
      let usernameQuery = await client.query(
        `SELECT username FROM accounts WHERE username = '${username}'`
      );
      if (usernameQuery.rows.length > 0) {
        return next(
          CustomeErrorHandler.alreadyExist("username is already exist")
        );
      }

      // ? query of email validation
      let emailQuery = await client.query(
        `SELECT email FROM accounts WHERE email = '${email}'`
      );
      if (emailQuery.rows.length > 0) {
        return next(CustomeErrorHandler.alreadyExist("email is already exist"));
      }
    } catch (error) {
      return next(error);
    }

    let adminSignup;
    try {
      // * password hash
      const hashedPassword = await bcrypt.hash(password, 10);

      // * role
      // * admin : 1
      // * partner : 0

      // ? query of insert user data
      let query = await client.query(
        `INSERT INTO accounts(username, email, password, role, created_on, updated_on) VALUES('${username}', '${email}', '${hashedPassword}', '${USER_ROLE.ADMIN}', current_timestamp, current_timestamp) RETURNING *`
      );
      adminSignup = query.rows[0];
      delete adminSignup.phone;
      const insertAdminQuery = await client.query(
        `INSERT INTO admins(account_id, name, company_name, phone_number, address, city, pincode, created_on, updated_on) VALUES('${adminSignup.account_id}', '${name}', '${company_name}', '${phone_number}', '${address}', '${city}', '${pincode}', current_timestamp, current_timestamp) RETURNING *`
      );

      document = insertAdminQuery.rows[0];
    } catch (error) {
      return next(error);
    }

    return res.status(200).json({
      apiCode: "WM036?",
      statusCode: 200,
      msg: commonMessages.createSuccfully,
      data: { ...adminSignup, originalPassword: password, ...document },
    });
  },

已重新安装所有依赖库并恢复服务器，但问题仍未解决。

解决方案 #

1. 修复SQL语法错误与注入风险 #

你当前直接将用户输入拼接到SQL语句中，一旦输入包含单引号等特殊字符，就会触发SQL语法错误，这大概率是服务器端失败的核心原因。同时这种写法存在严重的SQL注入漏洞，必须改用参数化查询：

// 验证用户名示例
const usernameQuery = await client.query(
  `SELECT username FROM accounts WHERE username = $1`,
  [username] // 参数数组，自动处理字符串转义
);

// 插入用户数据示例
const query = await client.query(
  `INSERT INTO accounts(username, email, password, role, created_on, updated_on) VALUES($1, $2, $3, $4, current_timestamp, current_timestamp) RETURNING *`,
  [username, email, hashedPassword, USER_ROLE.ADMIN]
);

所有涉及用户输入的SQL查询都要改成这种参数化方式，彻底避免语法错误和安全风险。

2. 排查服务器环境配置差异 #

• 数据库连接: 确认服务器上的数据库连接地址、用户名、密码是否正确，数据库服务是否正常运行，服务器是否有访问数据库的权限（比如防火墙规则）。
• 环境变量: 检查USER_ROLE、commonMessages等常量是否在服务器环境中正确配置，避免出现未定义的错误。
• 依赖版本: 对比本地与服务器的依赖版本（查看package-lock.json），确保Joi、bcrypt、email-validator等核心依赖版本一致，部分版本差异可能导致运行逻辑不同。

3. 添加详细错误日志 #

服务器端看不到具体错误信息很难定位问题，在所有catch块中添加日志输出：

catch (error) {
  console.error("注册接口错误详情:", error); // 打印到服务器日志
  return next(error);
}

查看服务器的进程日志或系统错误日志，获取具体的错误类型（比如数据库连接失败、SQL语法错误、依赖缺失等）。

4. 验证Joi规则的兼容性 #

检查Joi的验证规则在服务器环境中是否存在触发条件：

• 比如phone_number的正则/^\+?[1-9]\d{1,9}$/，如果服务器端接收的手机号长度超过10位（带国家码的长号），会触发验证失败，可调整正则或测试输入数据。
• 确认image字段的验证逻辑，服务器端是否接收了符合要求的图片路径或base64字符串，避免长度或格式不符合规则。

5. 排查emailValidator的问题 #

服务器端的emailValidator可能因为网络或配置问题无法正常工作，可暂时注释该部分逻辑，测试是否能完成注册：

// 暂时注释邮箱验证，测试流程
// let doc = await emailValidator.validate(email);
// if (!doc.valid)
//   return next(CustomeErrorHandler.validation("Enter valid email!"));

如果注释后服务器端正常运行，说明emailValidator在服务器环境中存在问题，可更换其他邮箱验证方式或检查其配置。

内容的提问来源于stack exchange，提问作者Henry