ESP32向Apache传值至MySQL，PHP存值正常但页面显示异常

我用ESP32向Apache服务器发送数值，通过PHP脚本存入MySQL数据库。数据已经成功写入数据库，但PHP页面没有显示预期的“New Record created successfully”提示，不过ESP32的串口监视器能收到正确的响应payload。

相关代码与信息 #

ESP32代码 #

#include <WiFi.h>
#include <HTTPClient.h>

/************************* Wifi Prerequisites **************************/
const char* ssid = "*******";
const char* password = "********";

void wifiConnection(const char* ssid, const char* password);
/**********************************************************************/


/******************* HTTP Request Prerequisites ***********************/
String URL = "http://domain/test.php";
int http_code{};


int test_number{20};
String post_data;

void setup() {
  // put your setup code here, to run once:
  Serial.begin(115200);
  wifiConnection(ssid, password);
}

void loop() {
  // put your main code here, to run repeatedly:

  post_data = "number=" + String(test_number);
  HTTPClient http;
  http.begin(URL);
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");
  http_code = http.POST(post_data);
  
  String payload = http.getString();

  Serial.print("URL : "); Serial.println(URL);
  Serial.print("Data: "); Serial.println(post_data);
  Serial.print("httpCode: "); Serial.println(http_code);
  Serial.print("Payload: "); Serial.println(payload);
  Serial.println("-----------------------------------------");
}

void wifiConnection(const char* ssid, const char* password){
  Serial.print("Connecting to ");
  Serial.print(ssid);

  while(WiFi.status() != WL_CONNECTED){
    Serial.print(".");
    WiFi.begin(ssid, password);
    delay(1000);
  }

  Serial.println();
  Serial.print("Connected!");
}

串口监视器输出 #

串口显示：请求URL正确、发送的数据为number=20、http状态码为200、Payload包含"Database connection is OK"及Undefined variable $sql的错误信息。

PHP脚本 #

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";


$conn = mysqli_connect($servername, $username, $password, $dbname);

if(!$conn){
  die("Connection failed" . mysqli_connect_error());
}

echo "Database connection is OK<br>";

if(isset($_POST["number"])){
  $number = $_POST["number"];
  $sql = "INSERT INTO number (number) VALUES ($number);";
}



if(mysqli_query($conn, $sql)){
  echo "New Record created successfully";
} else{
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

数据库信息 #

数据库名为test，表名为number，包含一个数值类型的number字段，ESP32发送的数据已成功插入该表。

网页显示 #

网页仅输出"Database connection is OK"，并附带Undefined variable $sql的错误提示。

问题原因与修复方案 #

问题根源 #

1. 插入逻辑未完全包裹在isset($_POST["number"])条件内：当页面被直接访问（非ESP32 POST请求）时，$sql变量未定义，执行mysqli_query触发错误。
2. 直接拼接SQL存在注入风险，且变量作用域导致的逻辑漏洞。

修复后的PHP脚本 #

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if(!$conn){
  die("Connection failed" . mysqli_connect_error());
}

echo "Database connection is OK<br>";

// 仅当收到POST参数时执行插入逻辑
if(isset($_POST["number"])){
  $number = $_POST["number"];
  // 使用预处理语句避免SQL注入，同时解决变量未定义问题
  $sql = "INSERT INTO number (number) VALUES (?);";
  $stmt = mysqli_prepare($conn, $sql);
  mysqli_stmt_bind_param($stmt, "i", $number);
  
  if(mysqli_stmt_execute($stmt)){
    echo "New Record created successfully";
  } else{
    echo "Error: " . mysqli_error($conn);
  }
  mysqli_stmt_close($stmt);
} else {
  echo "No POST data received";
}

// 关闭数据库连接，优化资源占用
mysqli_close($conn);

修复说明 #

1. 将插入逻辑完全包裹在isset($_POST["number"])判断内，避免$sql未定义的错误。
2. 使用预处理语句替代直接拼接SQL，彻底杜绝SQL注入风险。
3. 增加无POST数据时的提示信息，逻辑更清晰。
4. 新增数据库连接关闭语句，优化服务器资源。

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