预处理语句不适用于ALTER表查询 [英] Prepared statement not working with ALTER table queries

问题描述

我正在编写一个简单的函数，使用带有PDO的PHP向表中添加一列以准备查询.

I am writing a simple function to add a column to a table using PHP with PDO to prepare the query.

连接($dbh)有效，其他不涉及参数的查询有效.但是，添加列的重要查询不会.

The connection ($dbh) works, other queries that do not involve parameters work. However, the important query that adds the column does not.

当我检查数据库时，会有一个新列，其中包含名为?的列(即只是一个问号)和我指定的所有属性.

When I check the database, there is a new column with the column named ? (i.e. just a question mark) and all the attributes I specified.

我检查以确保$column变量正确通过，此外，execute语句返回false，因此据称该语句失败，但仍会创建一个列.

I checked to make sure the $column variable is coming through correctly and it is, additionally, the execute statement returns false, so allegedly the statement fails, but somehow a column is still created.

错误信息不是很有帮助(至少对我来说):

The error info is not very helpful (to me at least):

Array ( [0] => 00000 ). 

我在代码中搜索了简单的拼写错误，但什么也没发现.有什么想法吗?

I scoured the code for simple typos, but can't spot anything. Any ideas?

$qry='ALTER TABLE `completed` ADD `:column` TINYINT(1) NOT NULL DEFAULT 0';
$stmt = $GLOBALS['dbh']->prepare($qry);
$stmt->bindParam(":column",$column,PDO::PARAM_STR);
$stmt->execute();
$arr = $stmt->errorInfo();
print_r($arr);
$stmt===TRUE ? $return=1 : $return=0;

推荐答案

http://dev.mysql.com/doc/refman/5.6/en/prepare.html 说:

参数标记只能在应显示数据值的地方使用， 不适用于SQL关键字，标识符等.

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

用标识符表示数据库名称，表名称，列名称，索引名称，分区名称等.

By identifiers they mean database names, table names, column names, index names, partition names, etc.

按数据值，它们表示数字文字，带引号的字符串文字或带引号的日期文字.

By data values, they mean a numeric literal, quoted string literal, or quoted date literal.

要添加新列，您需要在准备查询之前在SQL字符串中包括该列的名称.这意味着您有责任确保列名中没有有趣的字符，这些字符可能会造成SQL注入漏洞.

To add a new column, you need to include the name of that column in the SQL string before you prepare the query. This means it's up to you to ensure that there are no funny characters in the column name that could create an SQL injection vulnerability.

这篇关于预处理语句不适用于ALTER表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！