php - 使用自动递增时出现语法错误

Question

在 mysql 中，我有一个包含列的表:

ID: INT(11) AUTO INCREMENT 
keyowner: VARCHAR(255)
key: VARCHAR(5)

在 php 中，我有以下代码:

<?php
function get_client_ip() {
$ipaddress = '';
if (getenv('HTTP_CLIENT_IP'))
    $ipaddress = getenv('HTTP_CLIENT_IP');
else if(getenv('HTTP_X_FORWARDED_FOR'))
    $ipaddress = getenv('HTTP_X_FORWARDED_FOR');
else if(getenv('HTTP_X_FORWARDED'))
    $ipaddress = getenv('HTTP_X_FORWARDED');
else if(getenv('HTTP_FORWARDED_FOR'))
    $ipaddress = getenv('HTTP_FORWARDED_FOR');
else if(getenv('HTTP_FORWARDED'))
   $ipaddress = getenv('HTTP_FORWARDED');
else if(getenv('REMOTE_ADDR'))
    $ipaddress = getenv('REMOTE_ADDR');
else
    $ipaddress = 'UNKNOWN';
return $ipaddress;
}
$username = "root";
$host = "localhost";
$pass = "pass";
$db = "api";
$owner = get_client_ip();
$key1 = file_get_contents("afile");
$conn = new mysqli($host, $username, $pass, $db);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$query = "INSERT INTO api
      (key_owner, key)
      VALUES
      ($owner, $key1)";

if ($conn->query($query) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $query . "<br>" . $conn->error;
}

$conn->close();
?>

运行代码时，显示错误:

Error: INSERT INTO api (key_owner, key) VALUES (an_ip, OF580) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key) VALUES (an_ip, afile)' at line 2

Answer 1

AS keyowner: varchar(255) key: varchar(5) 。您需要在您的值周围添加引号以插入数据和key 是 reserver keyword在 mysql 中，它必须在 backtick 中。

为了防止 sql 注入(inject)，在插入数据库之前使用 mysqli_real_escape_string

$key1  = mysqli_real_escape_string($conn,$key1 );
$owner = mysqli_real_escape_string($conn, $owner);
    $query = "INSERT INTO api
          (`key_owner`, `key`)
          VALUES
          ('".$owner."', '".$key1."')";

已更新

因为你的表名是keys。 keys 也是保留关键字，所以你需要在它周围加上反引号，这样最终的查询就是

$key1  = mysqli_real_escape_string($conn,$key1 );
$owner = mysqli_real_escape_string($conn, $owner);
    $query = "INSERT INTO `keys`
          (`key_owner`, `key`)
          VALUES
          ('".$owner."', '".$key1."')";