php SQLSTATE[42000]：语法错误或访问冲突：1064
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18988935/
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
提问by brokekidweb
I am writing some software right now. Reader's digest version: users select a package, enter their name, email, and desired subdomain. Subdomain is then checked to see if someone already registered it, and if it is only alphanumeric. I had all of this working using OO mysqli, but I have decided to make the move to PDO.
我现在正在写一些软件。读者摘要版本：用户选择一个包，输入他们的姓名、电子邮件和所需的子域。然后检查子域以查看是否有人已经注册了它，以及它是否只是字母数字。我使用 OO mysqli 完成了所有这些工作，但我决定转向 PDO。
Exact wording of 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 '->subdomain' at line 1
SQLSTATE[42000]：语法错误或访问冲突：1064 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“->子域”附近使用的正确语法
When I get to instantiating my Admin object, all is well. However, when I call the createAccount()function, all hell breaks loose. The stack trace is all over the place, and I can barely figure out when to begin troubleshooting this. I have checked the other answers here, and they all seem too localized, so here's the code that produces it, then all of the methods that contain errors. Here we go....
当我开始实例化我的 Admin 对象时，一切都很好。但是，当我调用该createAccount()函数时，一切都崩溃了。堆栈跟踪到处都是，我几乎不知道什么时候开始解决这个问题。我在这里检查了其他答案，它们似乎都太本地化了，所以这里是生成它的代码，然后是所有包含错误的方法。开始了....
First, the code that produces the error:
首先，产生错误的代码：
include 'classes/Admin.class.php';
$admin = new Admin('[email protected]');
try {
$admin->createAccount('John', 'Smith', 'test', 'pro');
}
catch(Exception $e) {
echo '<pre />';
echo $e->getMessage();
die(print_r($e->getTrace()));
}
Admin Class Constructor
管理类构造函数
public function __construct($email) {
$this->email = $email;
include 'Database.class.php';
include 'PasswordHash.php';
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "ems");
$this->data = new Database;
$this->hasher = new PasswordHash(8, false);
}
Inside Admin class,checking the subdomain
在 Admin 类中，检查子域
private function checkSubdomain() {
$this->data->query('SELECT * FROM clients WHERE subdomain = :subdomain');
$this->data->bind(':subdomain', $this->subdomain);
$this->data->execute();
return ($this->data->rowCount() == 0) && (ctype_alnum($this->subdomain));
}
PDO class execute, bind, and query
PDO 类执行、绑定和查询
public function execute() {
return $this->stmt->execute();
}
public function query($query) {
$this->stmt = $this->dbh->prepare($query);
}
public function bind($param, $value, $type = null) {
if(is_null($type)) {
switch(true) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
default:
$type = PDO::PARAM_STR;
}
}
$this->stmt->bindValue($param, $value, $type);
}
This error is rampant through my code, so I think its only in the PDO class, but my Session class worked just fine. Also, my queries worked perfectly using only mysqli, so I am confident in my syntax. Any help is greatly appreciated.
这个错误在我的代码中很猖獗，所以我认为它只在 PDO 类中，但我的 Session 类工作得很好。此外，我的查询仅使用 mysqli 即可完美运行，因此我对自己的语法充满信心。任何帮助是极大的赞赏。
By request, the create account function:
根据请求，创建帐户功能：
public function createAccount($firstname, $lastname, $subdomain, $package)
{
$this->firstname = $firstname;
$this->lastname = $lastname;
$this->subdomain = $subdomain;
$this->package = $package;
//does the subdomain exist, or is it invalid?
if(!$this->checkSubdomain())
throw new Exception('This domain is not available. It can only contain letters and numbers and must not already be taken.');
//now comes the table creation, provided everything is in order
$this->setup();
}
Here's the setup function that is called by createAccount (sans all of the table structures):
这是由 createAccount 调用的设置函数（没有所有表结构）：
private function setup() {
$isError = false;
$queries = array(
//all of these are CREATE statements, removed for security reasons
);
//need a database that matches with the client subdomain
$this->data->query('CREATE TABLE $this->subdomain');
$this->data->bind(':subdomain', $this->subdomain);
//PDO secured...execute
$this->data->execute();
$this->data->beginTransaction();
foreach($queries as $query) {
$this->data->query($query);
if(!$this->data->execute()) { //we hit some sort of error, time to GTFO of here
$isError = true;
$this->data->cancelTransaction();
//die('Error with: ' . $query);
break;
}
}
if(!$isError) {
$this->data->endTransaction();
mkdir('a/' . $this->subdomain, 0755);
$this->generatePass();
//this is where I insert the user into the admin table using PDO, removed for security
$this->data->execute();
}
$this->data->close();
}
回答by Ivan Hu?njak
This is what is causing the error:
这就是导致错误的原因：
$this->data->query('CREATE TABLE $this->subdomain');
$this->data->bind(':subdomain', $this->subdomain);
As Michael Berkowski and andrewsi noted in comments, you cannot bind value to :subdomainplaceholder since it is not noted as that in query and even if it is PDO placeholders can only be used for values not database, table or column names.
正如 Michael Berkowski 和 andrewsi 在评论中指出的那样，您不能将值绑定到:subdomain占位符，因为它没有在查询中注明，即使它是PDO 占位符也只能用于值而不是数据库、表或列名称。
If you want to have that kind of SQL queries dynamically created you need to enclose database, table or column names in backtick quotes(in case your columns and names contain SQL reserved keywords that may break the query) and escape valuesthat are placed, but you cannot use MySQLifor that if already using PDO.
如果您想动态创建这种 SQL 查询，您需要将数据库、表或列名括在反引号中（以防您的列和名称包含可能破坏查询的 SQL 保留关键字）并转义放置的值，但是MySQLi如果已经使用，则不能用于此目的PDO。
Since PDO does not come with real_escape_string()method which would do just that, and in practice it is not needed to escape values like that (unless you really have columns named like Ye'namewhich is totally stupid IMHO), so simple filter using preg_match()or preg_replace()is good enough:
由于 PDO 没有提供real_escape_string()可以做到这一点的方法，并且在实践中不需要转义这样的值（除非你真的有名为 like 的列Ye'name，恕我直言，这完全是愚蠢的），所以使用preg_match()or 的简单过滤器preg_replace()就足够了：
if (preg_match('/^[\w_]+$/i', $this->subdomain)) {
// note the ` (backtick), and using " (double quotes):
$this->data->query("CREATE TABLE `{$this->subdomain}`");
} else {
// throw exception or error, do not continue with creating table
}
Just few examples of using '(single quote - apostrophe) against "(double quotes) strings in PHP:
在 PHP 中'对"（双引号）字符串使用（单引号 - 撇号）的几个例子：
$a = 1;
$b = 2;
echo '$a + $b'; // outputs: $a + $b
echo "$a + $b"; // outputs: 1 + 2
$c = array(5, 10);
echo '$c[0] = {$c[0]}'; // outputs: $c[0] = {$c[0]}
echo "$c[0] = {$c[0]}"; // outputs: $c[0] = 5
The {}inside double quotes string is used for arrays and object property access and can be used around regular variables.
Escaping $in double quotes is done by \$otherwise it will assume a variable call.
该{}内部双引号字符串用于数组和对象属性访问，并且可以左右使用常规变量。双引号中的
转义$是通过\$否则它将假设变量调用完成的。
回答by zipzit
I've hit this error, but with a slightly different cause. You gotta leave spaces in the SELECT statement 'SELECT '.$userfield.' FROM '.$usertable. ' WHERE 1' works great, but 'SELECT'.$userfield.'FROM'.$usertable.'WHERE 1' fails miserably.
我遇到了这个错误，但原因略有不同。您必须在 SELECT 语句 'SELECT '.$userfield.' 中留下空格。FROM '.$usertable. ' WHERE 1' 效果很好，但 'SELECT'.$userfield.'FROM'.$usertable.'WHERE 1' 失败了。
$stmt = $dbh->query(
'SELECT ' . $userfield . ' FROM ' . $usertable . ' WHERE 1 '
);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
info offered in case anybody hits this entry with a 42000 failure code lookup.
提供的信息以防有人通过 42000 故障代码查找点击此条目。
