PHP PDO选择名称中带有单引号的列 [英] PHP PDO selecting columns with single quotes in their name

问题描述

在phpmyadmin中，我可以通过SQL运行它:

In phpmyadmin, I can run this through SQL:

SELECT * FROM mytable WHERE `Bob's Stuff` > 1

但是，当我运行完全相同的查询时，会出现一个php脚本:

But, when I run the exact same query a php script:

$stmt->prepare("SELECT * FROM mytable WHERE `Bob's Stuff` > :int");
$stmt->bindValue(':int', $int);
$stmt->execute();

我收到此错误:

致命错误:带有消息的未捕获异常'PDOException' 'SQLSTATE [42000]:语法错误或访问冲突:1064您有一个 您的SQL语法错误；检查与您的手册相对应的手册 MySQL服务器版本的正确语法，可在第10行的':int AND Bob's Stuff> ='1')'附近使用 script.php:208堆栈跟踪:#0 script.php(208):PDOStatement-> execute()#1 {main}被抛出 第208行的script.php

Fatal error: Uncaught exception 'PDOException' with message '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 ':int AND Bob's Stuff >= '1')' at line 10' in script.php:208 Stack trace: #0 script.php(208): PDOStatement->execute() #1 {main} thrown in script.php on line 208

如果我将查询列更改为任何其他列名，则查询工作正常.

If I change the query column to any other column name, the query works fine.

是否可以使用带有撇号的列名?

Is it possible to have column names with apostrophes?

推荐答案

将该列名也用作参数，

$tableName = "`Bob\'s Stuff`";
$stmt->prepare("SELECT * FROM mytable WHERE :tableName > :int");
$stmt->bindValue(':tableName', $tableName);
$stmt->bindValue(':int', $int);
$stmt->execute();

顺便说一句，建议下次不要创建像Bob's Stuff这样的列.使其像这样BobStuff这样的单词.

By the way, one suggestion is next time don't create columns like Bob's Stuff. Make it single word like this BobStuff.

这篇关于PHP PDO选择名称中带有单引号的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！