SQL INSERT接口空表单提交报错：如何构建规范的查询错误信息？

Great question—handling database errors properly is crucial for both user experience and security in your API. Let’s break down a structured approach to tackle this specific scenario (NOT NULL constraints on fields A, B, C) and generalize it for other common issues:

1. Catch Errors Early: Client-Side Validation #

Before the request even hits your server, validate the form inputs on the frontend. This saves unnecessary database calls and gives users immediate feedback. For example:

// Frontend form submission handler
function submitForm(e) {
  e.preventDefault();
  const fieldA = document.getElementById('fieldA').value.trim();
  const fieldB = document.getElementById('fieldB').value.trim();
  const fieldC = document.getElementById('fieldC').value.trim();

  const errors = [];
  if (!fieldA) errors.push("Field A cannot be empty");
  if (!fieldB) errors.push("Field B cannot be empty");
  if (!fieldC) errors.push("Field C cannot be empty");

  if (errors.length > 0) {
    // Display errors to user (e.g., in a toast or error list)
    showErrorMessages(errors);
    return;
  }

  // Proceed to send request to API
  fetch('/submit-form', { method: 'POST', body: JSON.stringify({ A: fieldA, B: fieldB, C: fieldC }) });
}

Note: Never rely solely on client-side validation—users can bypass it via browser dev tools.

2. Enforce Rules: Server-Side Validation #

Validate the incoming request data before executing any SQL queries. This acts as a second line of defense. Using Node.js/Express as an example:

// Server-side route handler
app.post('/submit-form', async (req, res) => {
  const { A, B, C } = req.body;
  const errors = [];

  if (!A || A.trim() === '') errors.push("Field A is required");
  if (!B || B.trim() === '') errors.push("Field B is required");
  if (!C || C.trim() === '') errors.push("Field C is required");

  if (errors.length > 0) {
    return res.status(400).json({
      success: false,
      errors: errors
    });
  }

  // Proceed to execute SQL INSERT
  try {
    await db.query('INSERT INTO your_table (A, B, C) VALUES ($1, $2, $3)', [A, B, C]);
    res.status(201).json({ success: true, message: "Record created successfully" });
  } catch (err) {
    // Handle database errors here (see next section)
    handleDatabaseError(err, res);
  }
});

3. Parse Database Errors Gracefully #

Even with validation, edge cases can slip through (e.g., a bug in validation logic). When the database throws an error (like NOT NULL violation), parse it to return user-friendly messages instead of raw SQL errors.

Most databases use standardized error codes:

• PostgreSQL: 23502 for NOT NULL violations
• MySQL: 1048 for NOT NULL violations

Here’s how to map these to meaningful messages:

function handleDatabaseError(err, res) {
  // Example for PostgreSQL
  if (err.code === '23502') {
    // Extract the missing field from the error message (e.g., "null value in column \"a\" violates not-null constraint")
    const missingField = err.message.match(/column \"(\w+)\"/)?.[1];
    const errorMessage = missingField ? 
      `Field ${missingField.toUpperCase()} cannot be empty` : 
      "One or more required fields are missing";
    
    return res.status(400).json({ success: false, errors: [errorMessage] });
  }

  // Handle other error types (e.g., duplicate keys, foreign key violations)
  // ...

  // Generic fallback for unexpected errors (don't expose raw details!)
  res.status(500).json({ success: false, errors: ["An unexpected error occurred. Please try again later."] });
}

Critical: Never send raw database error details (like stack traces or full SQL error messages) to the frontend—this can leak sensitive information about your database structure.

4. Use a Consistent Error Response Format #

Stick to a predictable JSON structure for error responses so your frontend can easily parse and display them. Example:

{
  "success": false,
  "errors": ["Field A cannot be empty", "Field C cannot be empty"]
}

Key Takeaways #

• Layer validation: Combine client-side (immediate feedback) and server-side (security) checks.
• Avoid raw SQL errors: Map database error codes to user-friendly messages.
• Hide sensitive details: Never expose internal database structure or error stacks to users.
• Be consistent: Use a standard error format across your API.

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