如何自动获取登录用户的user_id并插入comment_table？

Hey there! Let's break down how to automatically get the logged-in user's user_id and insert it into your comment_table when they submit a comment. Here's a practical, step-by-step breakdown based on typical web application flows:

When a user logs in successfully, your backend will store their authenticated identity (including user_id) in a secure way. When they submit a comment, your backend retrieves this user_id from the secure storage, then combines it with the comment content to insert into comment_table—no need for the user to input their ID manually.

1. Store User Identity After Login #

First, you need to persist the logged-in user's user_id securely once they pass authentication:

• Using Server-side Sessions (common in PHP, Java Spring, etc.): After verifying the user's credentials, save their user_id to a server-side session. The frontend will receive a session ID via cookie, which it automatically sends with subsequent requests.
• Using JWT Tokens (popular for SPAs like React/Vue, or Node.js backends): Generate a JWT token containing the user_id and return it to the frontend. The frontend stores this token (in localStorage or an HTTP-only cookie) and includes it in the request header (e.g., Authorization: Bearer <your-token>) when submitting comments.

2. Handle Comment Submission #

When the user fills out the comment form and hits submit:

• Frontend: Only send the comment content (e.g., a content field) to the backend. Never let the frontend send user_id—this prevents malicious users from tampering with it.
• Backend: Extract the user_id from the session/JWT, then construct an insert query to add both the user_id and comment content to comment_table.

Backend Example (PHP + Session) #

// Start session to access logged-in user data
session_start();

// Retrieve user_id from session (ensures it's from authenticated user)
$user_id = $_SESSION['user_id'];
// Get comment content from submitted form
$comment_content = $_POST['content'];

// Use prepared statements to prevent SQL injection!
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'db_user', 'db_password');
$stmt = $pdo->prepare("INSERT INTO comment_table (user_id, content, created_at) VALUES (?, ?, NOW())");
$stmt->execute([$user_id, $comment_content]);

// Return success response to frontend
echo json_encode(['status' => 'success', 'message' => 'Comment added!']);

Backend Example (Node.js + JWT) #

const jwt = require('jsonwebtoken');
const mysql = require('mysql2/promise');

// Handler for comment submission endpoint
async function submitComment(req, res) {
  // Extract JWT token from request header
  const authHeader = req.headers.authorization;
  const token = authHeader?.split(' ')[1];

  if (!token) {
    return res.status(401).json({ error: 'Please log in first' });
  }

  try {
    // Verify token and extract user_id
    const decoded = jwt.verify(token, 'your_jwt_secret_key');
    const user_id = decoded.user_id;
    const { content } = req.body;

    // Insert into comment_table with parameterized query
    const db = await mysql.createConnection({
      host: 'localhost',
      user: 'db_user',
      password: 'db_password',
      database: 'your_database'
    });
    await db.execute(
      'INSERT INTO comment_table (user_id, content, created_at) VALUES (?, ?, NOW())',
      [user_id, content]
    );
    await db.end();

    res.status(200).json({ message: 'Comment submitted successfully' });
  } catch (err) {
    res.status(403).json({ error: 'Invalid or expired session' });
  }
}

• Never trust frontend-provided user IDs: Always retrieve user_id from server-side sessions or verified JWT tokens—this eliminates the risk of users pretending to be someone else.
• Always use prepared statements: Parameterized queries prevent SQL injection attacks, which are a common and dangerous database vulnerability.
• Validate the user: Before inserting, double-check that the user_id exists in user_table to avoid orphaned foreign keys in comment_table.

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