且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

SQLSTATE [42000]:语法错误或访问冲突:1064

更新时间:2021-10-16 22:15:08

这是导致错误的原因:

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