PHP新手，我的SQL查询出了什么问题？

I'm trying to execute a query using PHP via its PostgreSQL-specific built-in functions, however I can't figure out why my queries aren't being run. Could someone please point me in the right direction regarding what could be wrong with the following code:

我正在尝试使用PHP通过其PostgreSQL特定的内置函数执行查询，但是我无法弄清楚为什么我的查询没有运行。有人可以指出我正确的方向，以下代码可能有什么问题：

//create our final insert statement as a string
$final_insert_query = $beginningOfInsertStmt . $oneRowFormatted . ");";
    echo $final_insert_query; // the output of this is listed below
    // run the query and store result in $result           
    $result = pg_query($dbconn, $final_insert_query);
    if (!$result) {
       echo "An error occurred!!!!.";
       exit;
    }
    else{
    echo $result . " was SUCCESSFUL!";
    }

The output of this script is currently:

此脚本的输出目前是：

INSERT INTO POP_HOUSING_ESTIMATE_STATE VALUES(1, 'South', 'East South Central',
'Alabama', 4784762, 4803689, 2173898, 2182088);

An error occurred!!!!.

Thus, the $final_insert_query variable should contain the following string (which appears to be a valid SQL INSERT statement by all means):

因此，$ final_insert_query变量应包含以下字符串（无论如何它似乎都是有效的SQL INSERT语句）：

INSERT INTO POP_HOUSING_ESTIMATE_STATE VALUES(1, 'South', 'East South Central',
'Alabama', 4784762, 4803689, 2173898, 2182088);

I should also mention that I have successfully inserted the same exact line via the terminal directly into my PosgreSQL database. What could be the problem here?

我还要提一下，我已经通过终端成功地将同一行完全插入到我的PosgreSQL数据库中。这可能是什么问题？

2 个解决方案

#1

There are several things wrong here:

这里有几个问题：

Your code is probably vulnerable to SQL injection. See PHP manual on SQL injection and this site. Please use parameterised queries ("prepared statements").

您的代码可能容易受到SQL注入攻击。请参阅SQL注入和本站点的PHP手册。请使用参数化查询（“预备语句”）。
You aren't getting the full error so it's going to be very hard for you to diagnose the fault. As per the manual for pg_query you can use pg_last_error to get the error details.

您没有收到完整的错误，因此您很难诊断出错误。根据pg_query的手册，您可以使用pg_last_error来获取错误详细信息。

I suspect the immediate problem is likely to be case sensitivity; you probably created your table as "POP_HOUSING_ESTIMATE_STATE" but you're querying it as POP_HOUSING_ESTIMATE_STATE. Note the different quoting. See Identifiers and keywords in the PostgreSQL manual for a description of PostgreSQL's case folding behaviour. Such case folding is required by ANSI SQL, though PostgreSQL folds to lower case instead of the standard-specififed upper-case.

我怀疑当前的问题很可能是区分大小写;您可能已将表创建为“POP_HOUSING_ESTIMATE_STATE”，但您将其查询为POP_HOUSING_ESTIMATE_STATE。注意不同的引用。有关PostgreSQL案例折叠行为的说明，请参阅PostgreSQL手册中的标识符和关键字。 ANSI SQL需要这种大小写折叠，尽管PostgreSQL折叠为小写而不是标准规范的大写。

#2

If you're able to paste your SQL string directly into postgresql, then you can rule out your SQL syntax. That leaves your connection. Several things could go wrong here... no network path, bad password, typo in server name, neglected to run pg_connect(), etc.

如果您能够将SQL字符串直接粘贴到postgresql中，那么您可以排除SQL语法。这留下你的联系。这里有几件事可能出错...没有网络路径，密码错误，服务器名称错字，忽略运行pg_connect（）等。

echo pg_last_error($dbconn) immediately after you've run $dbconn=pg_connect($conn) (however you've named it in your code).

运行$ dbconn = pg_connect（$ conn）后立即回显pg_last_error（$ dbconn）（但是你已在代码中命名）。

#1