Postman调用GET/POST报错：receipt表不存在，求代码排查方案

使用Postman执行GET或POST请求时持续报错，提示'receipt'表不存在，代码用于添加收据数据，后续将在Android Studio中生成收据列表。以下是相关代码文件：

MySQL脚本文件 #

CREATE DATABASE my_database;
USE my_database;

CREATE TABLE receipt(
    id int AUTO_INCREMENT,          /* receipt id */
    name varchar(30) not null,      /* store name */
    total float(5,2) not null,      /* receipt total */
    date date not null,             /* date of purchase */
    time time not null,             /* time of purchase */ 
    Primary Key(id)
);

CREATE TABLE items (
  id INT AUTO_INCREMENT,
  receipt_id INT NOT NULL,          /* foreign key linking to the receipt table */
  item_name VARCHAR(50) NOT NULL,
  item_price FLOAT(5,2) NOT NULL,
  item_quantity INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (receipt_id) REFERENCES receipt(id)
);

UPDATE receipt
SET total = (SELECT SUM(item_price*item_quantity) FROM items WHERE receipt_id = Receipt.id)

index.js 文件 #

// We now import the connection object we exported in db.js.
const db = require("../controllers/db");

// More libraries…
const express = require("express");
const bodyParser = require("body-parser");

const router = express.Router();

router.use(bodyParser.json()); // Automatically parse all POSTs as JSON.
router.use(bodyParser.urlencoded({ extended: true })); // Automatically parse URL parameters

// Add a new receipt
router.post("/addreceipt", function (req, res) {
    let receipt = req.body;
    let sql = `INSERT INTO receipt (name, total, date, time) VALUES ('${receipt.name}', ${receipt.total}, '${receipt.date}', '${receipt.time}')` ;
    db.query(sql, function (err, result) {
    console.log("Result: " + JSON.stringify(result));
    if (err) {
    return res.send(err);
    } else {
    let returnedObject = {
    message: "receipt added successfully"
    };
    return res.json(returnedObject);
    }
    });
    });


// Get all receipts
router.get("/get-receipt/:id", function (req, res) {
    let id = req.params.id;
    let sql = `SELECT * FROM receipt WHERE id = ?`;
    db.query(sql, [id], function (err, result) {
    console.log("Result: " + JSON.stringify(result));
    if (err) {
    return res.send(err);
    } else {
    let returnedObject = {
    receipt: result[0]
    };
    return res.json(returnedObject);
    }
    });
    });
    
    


// Get items of specific receipt
router.get("/get-items/:receipt_id", function (req, res) {
    let receipt_id = req.params.receipt_id;
    let sql = `
        SELECT * FROM items WHERE receipt_id = ${receipt_id}
    `;
    db.query(sql, function (err, result) {
        console.log("Result: " + JSON.stringify(result));
        if (err) {
            return res.send(err);
        } else {
            let returnedObject = {
                items: result
            };
            return res.json(returnedObject);
        }
    });
});

// Add item to receipt
    router.post("/add-item", function (req, res) {
    let item = req.body;
    let sql = ` 
    INSERT INTO items (receipt_id, item_name, item_price, item_quantity)
    VALUES (${item.receipt_id}, '${item.item_name}', ${item.item_price}, ${item.item_quantity})
    `;
    db.query
    (sql, function (err, result) {
        console.log("Result: " + JSON.stringify(result));
        if (err) {
            return res.send(err);
        } else {
            let updateTotal = `UPDATE receipt SET total = (SELECT SUM(item_price*item_quantity) FROM items WHERE receipt_id = ${item.receipt_id})`;
            db.query(updateTotal, function(err, result){
                if (err) {
                    return res.send(err);
                }
            });
            let returnedObject = {
                message: "Item added successfully"
            };
            return res.json(returnedObject);
        }
    });
});


// Hello World
router.get("/health", function (req, res) {
    return res.send("ok"); // For plain text, use res.send
});

// Export the created router
module.exports = router;

db.js 文件 #

// Importing the mysql library:
//
// Similar to import in Java, however, you can name the library object whatever
// you want on the left hand side.
// I recommend you `const` all your imports.
const mysql = require("mysql");

let connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "my_database"
});

connection.connect(function (err) {
    if (err) {
        console.error("Failed to connect to database- throwing error:");
        throw err;
    }
    console.log("Connected to database succesfully.");
});

// Each Javascript file has an optional export. This export can be anything: here I made the connection object the export.
module.exports = connection;

1. 确认SQL脚本执行状态

   • 打开MySQL客户端，执行USE my_database; SHOW TABLES;，查看是否存在receipt和items表。
   • 脚本末尾的UPDATE receipt语句会报错：刚创建的receipt表为空，子查询返回NULL，但total字段设置了NOT NULL，导致脚本执行失败。虽然前面的CREATE TABLE已执行成功，但一次性执行整个脚本时容易误以为表未创建。建议删除这条UPDATE语句，你的add-item接口中已包含更新逻辑。

2. 验证数据库连接配置

   • 检查db.js中的参数：确保database字段为my_database，user、password、host与你的MySQL服务匹配。
   • 启动Node服务时，查看控制台是否打印Connected to database succesfully.，未打印则说明连接失败，需修正配置。

3. 重启Node服务

   • 若先启动服务再执行SQL脚本创建表，服务不会自动识别新表，必须重启服务才能生效。

4. 修复SQL注入风险（可选但建议）

   • 现有接口存在SQL注入漏洞，比如addreceipt用字符串拼接生成SQL，建议改用参数化查询，参考get-receipt的写法：

     let sql = `INSERT INTO receipt (name, total, date, time) VALUES (?, ?, ?, ?)`;
     db.query(sql, [receipt.name, receipt.total, receipt.date, receipt.time], function (err, result) {
         // 处理逻辑
     });
     

内容的提问来源于stack exchange，提问作者Salma Ahmed Abdo