PostgreSQL无法按外部用户作者名过滤帖子问题（已解决）

问题核心 #

你的书评作者过滤功能仅对本地创建的作者名生效，外部用户输入的作者名无法匹配查询，根源在于URL参数编码/解码不匹配，以及潜在的字符串前后空格干扰：

1. 前端直接将作者名拼接进URL，若作者名含空格、特殊字符（如John Doe），浏览器会自动转义空格为%20，但后端直接获取的转义后字符串（John%20Doe）与数据库存储的原始值（John Doe）无法匹配。
2. 外部用户输入的作者名可能带有前后空格，存入数据库后未做处理，导致与URL传递的无空格字符串不匹配。

修复步骤 #

1. 前端对作者名做URL编码 #

修改index.ejs中生成作者链接的代码，用encodeURIComponent处理作者名，确保特殊字符正确转义：

<a class="authorTag" href="/author/<%= encodeURIComponent(post.author) %>"><h3 class="author"><%= post.author %>'s</a> Review of:</h3>

2. 后端解码参数并清理空格 #

修改/author/:auth路由，先解码URL参数，再去除前后空格，保证与数据库存储值一致：

//Filter by author of Review
app.get("/author/:auth", async (req, res) =>{
    let author = decodeURIComponent(req.params.auth).trim();
    let data =  await db.query("SELECT * FROM posts WHERE author ILIKE ($1) AND author IS NOT NULL AND author != ''", [author]);
    let posts = data.rows;
    console.log(author);
    res.render("index.ejs", {
        posts: posts,
    });
});

3. 插入/更新时自动清理作者名空格（可选但推荐） #

避免用户输入的前后空格存入数据库，在新增和更新接口中对author字段做trim处理：

// Add entry to database
app.post("/submit", async (req, res) =>{
    let author = req.body.author.trim();
    let book = req.body.book;
    let review = req.body.review;
    let rating = req.body.rating;

    const result = await axios.get(`https://openlibrary.org/search.json?q='%'||${book}||'%'&limit=1`);
    let coverID = result.data.docs[0].cover_edition_key;
    
    const fullDate = new Date()
    const day = fullDate.getDate()
    const month = fullDate.getMonth() + 1
    const year = fullDate.getFullYear()
    let date = `${day}/${month}/${year}`

    await db.query("INSERT INTO posts (author, date, descr, rating, book_auth, cover_id) VALUES ($1, $2, $3, $4, $5, $6)",
        [author, date, review, rating, book, coverID] );

    res.redirect("/");
});

//Update post
app.post("/edit:id", async (req, res) =>{
    let id = req.params.id;
    let author = req.body.author.trim();
    let book = req.body.book;
    let review = req.body.review;
    let rating = req.body.rating;
    await db.query("UPDATE posts SET author = ($1), descr = ($2), rating = ($3), book_auth = ($4) WHERE id = ($5)", [author, review, rating, book, id]);
    res.redirect("/");
})

验证逻辑 #

外部用户输入带空格的作者名（如Jane Smith）后，前端会编码为Jane%20Smith拼入URL；后端接收后解码为Jane Smith并清理空格，与数据库存储值完全匹配，即可正确查询到对应书评。

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