PDO 和 Microsoft SQL：必须声明表变量“@P1”

发布于 2024-09-11 07:38:35 字数 898 浏览 7 评论 0原文

我正在尝试使用 PDO 中的绑定从 Microsoft SQL 数据库中选择一些条目。我正在使用的代码看起来与我在文档中找到的代码类似。但是，当我运行它时，我收到以下警告：

警告：PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: 语法错误或访问冲突：1087 [Microsoft][SQL Native Client][SQL Server]必须声明表变量“@P1” 。 (SQLExecute[1087] at ext\pdo_odbc\odbc_stmt.c:254) 在第 40 行（长文件路径）

这是相关代码：

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM ? WHERE user='?'"; 
$sth = $db->prepare($sql);
$sth->bindValue(1,  $table, PDO::PARAM_STR);
$sth->bindValue(2, $user, PDO::PARAM_STR);
$sth->execute(); //                         <-- line 40
$data = $sth->fetch(PDO::FETCH_ASSOC);

这可能是相关的。当我尝试使用命名参数标记（：table、：user）而不是问号时，我得到：

警告：PDOStatement::bindValue() [pdostatement.bindvalue]：SQLSTATE[HY093]：参数号无效：第 39 行（长文件路径）中未定义参数

为什么它不像我准备好的语句？

原文

I'm trying to use binding in PDO to select some entries from a Microsoft SQL database. The code I'm using looks like it's similar to what I've found in the documentation. However, when I run it, I get the warning below:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1087 [Microsoft][SQL Native Client][SQL Server]Must declare the table variable "@P1". (SQLExecute[1087] at ext\pdo_odbc\odbc_stmt.c:254) in (long file path) on line 40

Here's the relevant code:

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM ? WHERE user='?'"; 
$sth = $db->prepare($sql);
$sth->bindValue(1,  $table, PDO::PARAM_STR);
$sth->bindValue(2, $user, PDO::PARAM_STR);
$sth->execute(); //                         <-- line 40
$data = $sth->fetch(PDO::FETCH_ASSOC);

This may be related. When I try to use named parameter markers (:table, :user) instead of question marks, I get this:

Warning: PDOStatement::bindValue() [pdostatement.bindvalue]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in (long file path) on line 39

Why doesn't it like my prepared statement?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

简单爱 2024-09-18 07:38:35

您无法将参数绑定到 SQL 中的表名。对于任何语言、任何数据库都是如此。

您必须在 prepare() 之前将表名称插入到查询字符串中。

此外，您也不应该将参数占位符放在引号内，即使它是字符串或日期值。引号内的参数占位符被解释为文字字符串。否则你怎么会输入字面上的问号呢？

我的写法如下：

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM $table WHERE user=?"; 
$sth = $db->prepare($sql);
$sth->execute(array($user));
$data = $sth->fetch(PDO::FETCH_ASSOC);

我不费心使用 bindParam() 或 bindValue()。通常，将数组参数中的参数值传递给 execute() 会更容易。

You can't bind parameters to table names in SQL. This is true in any language, any database.

You'll have to interpolate the table name into the query string before prepare().

Also you shouldn't put the parameter placeholder inside quotes, even if it's a string or date value. Parameter placeholders inside quotes are interpreted as literal strings. Otherwise how would you ever enter a literal question mark?

Here's how I would write this:

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM $table WHERE user=?"; 
$sth = $db->prepare($sql);
$sth->execute(array($user));
$data = $sth->fetch(PDO::FETCH_ASSOC);

I don't bother with using bindParam() or bindValue(). It's usually easier to just pass the parameter values in an array argument to execute().

回复收藏 0 原文

~没有更多了~