SQLSTATE [42000]:语法错误或访问冲突:1064 [英] SQLSTATE[42000]: Syntax error or access violation: 1064

问题描述

我正在写一些软件.读者的摘要版本:用户选择一个软件包，输入他们的姓名，电子邮件和所需的子域.然后检查子域，看是否有人已经注册了它，以及它是否仅为字母数字.我已经使用OO mysqli完成了所有这些工作，但是我决定转向PDO.

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.

错误的确切措辞:

SQLSTATE [42000]:语法错误或访问冲突:1064您的SQL语法有一个错误；请参阅第1914页的"SQLSTATE错误".检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的'-> subdomain'附近使用

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

当我实例化Admin对象时，一切都很好.但是，当我调用createAccount()函数时，所有情况都变得松散了.堆栈跟踪无处不在，我几乎无法弄清何时开始对此进行故障排除.我在这里检查了其他答案，它们看起来都太局限了，所以这是产生它的代码，然后是所有包含错误的方法.我们走了...

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....

首先，产生错误的代码:

First, the code that produces the error:

include 'classes/Admin.class.php';
$admin = new Admin('[e-mail]');

try {
    $admin->createAccount('John', 'Smith', 'test', 'pro');
}
catch(Exception $e) {
    echo '<pre />';
    echo $e->getMessage();
    die(print_r($e->getTrace()));
}

管理员类构造器

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);
    }

在Admin类内部，检查子域

Inside Admin class,checking the subdomain

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类执行，绑定和查询

PDO class execute, bind, and query

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);
}

此错误在我的代码中非常普遍，因此我认为它仅在PDO类中有效，但是我的Session类工作得很好.另外，我的查询仅使用mysqli即可完美运行，因此我对语法很有信心.任何帮助将不胜感激.

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.

根据请求，创建帐户功能:

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();
}

这是createAccount调用的设置函数(没有所有表结构):

Here's the setup function that is called by createAccount (sans all of the table structures):

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();

}

推荐答案

这是导致错误的原因:

$this->data->query('CREATE TABLE $this->subdomain');
$this->data->bind(':subdomain', $this->subdomain);

正如Michael Berkowski和Andrewsi在评论中指出的那样，您不能将值绑定到:subdomain占位符，因为它在查询中没有被指出，即使它是 PDO占位符也只能用于值，而不是数据库，表或列名.

As Michael Berkowski and andrewsi noted in comments, you cannot bind value to :subdomain placeholder 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.

如果您希望动态创建这种SQL查询，则需要在反引号内加上数据库名称，表名称或列名称(以防您的列和名称包含SQL保留关键字，这些关键字可能会破坏查询)和转义值，但是如果已经使用PDO，则不能使用MySQLi.

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 values that are placed, but you cannot use MySQLi for that if already using PDO.

因为PDO并未附带real_escape_string()方法，而方法实际上只是不需要转义(除非您确实有名为Ye'name的列，这完全是愚蠢的恕我直言)，所以使用preg_match()或preg_replace()的简单过滤器就足够了:

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'name which is totally stupid IMHO), so simple filter using preg_match() or preg_replace() is good enough:

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
}

在PHP中对"(双引号)字符串使用'(单引号-撇号)的几个例子:

Just few examples of using ' (single quote - apostrophe) against " (double quotes) strings in 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.

这篇关于SQLSTATE [42000]:语法错误或访问冲突:1064的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！