SQLSTATE[42000]：语法错误或访问冲突：1064 你的 SQL 语法有错误 — PHP — PDO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4544051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO
提问by willium
I've looked through all the other StackOverflow (and google) posts with the same problem, but none seemed to address my problem.
我已经浏览了所有其他 StackOverflow（和谷歌）帖子，但都没有解决我的问题。
I am using PDO and PHP.
我正在使用 PDO 和 PHP。
My code:
我的代码：
$vals = array(
':from' => $email,
':to' => $recipient,
':name' => $name,
':subject' => $subject,
':message' = >$message
);
print_r($vals);
try {
$pdo = new PDOConfig();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM messages WHERE `message` LIKE :message";
$q = $pdo->prepare($sql);
$q->execute(array(':message' => $vals[':message']));
$resp = $q->fetchAll();
foreach ($resp as $row) {
throw new Exception('Please do not post the same message twice!');
}
$sql = "INSERT INTO messages (from, to, name, subject, message) VALUES (:from, :to, :name, :subject, :message)";
$q = $pdo->prepare($sql);
$q->execute($vals);
}
catch(PDOException $e) {
echo $e->getMessage();
}
and the first print_r gives
第一个 print_r 给出
Array ( [:from] => [email protected]
[:to] => [email protected]
[:name] => abc
[:subject] => abc
[:message] => abc )
which is expected (none are null)
这是预期的（没有一个是空的）
but it outputs the error
但它输出错误
SQLSTATE[42000]: Syntax error or access violation: 1064 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 'from, to, name, subject, message) VALUES ('[email protected]', '[email protected]' at line 1
SQLSTATE[42000]：语法错误或访问冲突：1064 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在“从、到、名称、主题、消息”附近使用的正确语法（第 1 行的“[email protected]”、“[email protected]”）
No idea how to fix this. any ideas?
不知道如何解决这个问题。有任何想法吗？
回答by Jan Krüger
fromis a keyword in SQL. You may not used it as a column name without quoting it. In MySQL, things like column names are quoted using backticks, i.e. `from`.
from是 SQL 中的关键字。您不能在不引用它的情况下将其用作列名。在 MySQL 中，诸如列名之类的内容使用反引号引用，即`from`.
Personally, I wouldn't bother; I'd just rename the column.
就我个人而言，我不会打扰；我只是重命名该列。
PS. as pointed out in the comments, tois another SQL keyword so it needs to be quoted, too. Conveniently, the folks at drupal.org maintain a list of reserved words in SQL.
附注。正如评论中所指出的，to是另一个 SQL 关键字，因此也需要引用它。方便的是，drupal.org 上的人在 SQL 中维护了一个保留字列表。
回答by uKolka
I've got this exact error, but in my case I was binding values for the LIMITclause without specifying the type. I'm just dropping this here in case somebody gets this error for the same reason. Without specifying the type LIMIT :limit OFFSET :offset;resulted in LIMIT '10' OFFSET '1';instead of LIMIT 10 OFFSET 1;. What helps to correct that is the following:
我有这个确切的错误，但在我的情况下，我在LIMIT没有指定类型的情况下绑定了子句的值。我只是把它放在这里，以防有人因为同样的原因得到这个错误。不指定类型LIMIT :limit OFFSET :offset;导致LIMIT '10' OFFSET '1';而不是LIMIT 10 OFFSET 1;。有助于纠正这一点的是以下内容：
$stmt->bindParam(':limit', intval($limit, 10), \PDO::PARAM_INT);
$stmt->bindParam(':offset', intval($offset, 10), \PDO::PARAM_INT);
回答by Szymon Baranowski
Same pdo error in sql query while trying to insert into database value from multidimential array:
尝试从多维数组插入数据库值时，sql 查询中出现相同的 pdo 错误：
$sql = "UPDATE test SET field=arr[$s][a] WHERE id = $id";
$sth = $db->prepare($sql);
$sth->execute();
Extracting array arr[$s][a]from sql query, using instead variable containing it fixes the problem.
arr[$s][a]从 sql 查询中提取数组，使用包含它的变量来解决问题。
回答by Kazim Noorani
ALTER TABLE `{$installer->getTable('sales/quote_payment')}`
ADD `custom_field_one` VARCHAR( 255 ) NOT NULL,
ADD `custom_field_two` VARCHAR( 255 ) NOT NULL;
Add backtick i.e. " ` " properly. Write your getTable name and column name between backtick.
正确添加反引号，即“`”。在反引号之间写下您的 getTable 名称和列名称。
