如何在Postgresql中参数化ALTER ROLE语句 - How to parameterize an ALTER ROLE statement in Postgresql?

我正在尝试为我的PSQL数据库制作一个PHP接口，我希望一些在PSQL上注册的本地用户登录到我的数据库。我将首先为每个用户创建每个用户名，使用通用密码（如"Password123"），然后用户可以更改他/她的密码。

为此，我想使用一个简单的PHP形式：

<form action="" method="post">
    <table>
    <tr> <td> User: </td> <td> <input type="text" name="user" /> </td> </tr>
    <tr> <td> Old password: </td> <td> <input type="password" name="old" /> </td> </tr>
    <tr> <td> New password: </td> <td> <input type="password" name="new1" /> </td> </tr>
    <tr> <td> Repeat new password: </td> <td> <input type="password" name="new2" /> </td> </tr>
     <tr> <td> <input type="submit" name="submit" value="Change password" /> </td> </tr>
</form>
<?php
    if ($_POST) {
        $user = $_POST["user"];
        $old = $_POST["old"];
        $new1 = $_POST["new1"];
        $new2 = $_POST["new2"];
        $link = pg_connect("dbname=mydb host=localhost user=$user password=$old connect_timeout=1");
        if (!$link) { 
            die("Error connecting to mydb: ".pg_last_error($link)); 
        }
        if ($new1 <> $new2) {
            pg_close($link);
            die("New passwords do not match.");
        }
        $res = @pg_query($link,"ALTER ROLE $user WITH ENCRYPTED PASSWORD '$new1';");
        if ($res) {
            echo "Password successfully changed!<br>";
        } else {
            echo "Failed to change password...<br>";
        }
        pg_close($link);
    }
?>

它确实如我预期的那样工作！

但我读到有一些SQL注入攻击可以对这样的表达式进行，其中SQL查询中有一个简单的变量插值。所以我读到PREPARE语句是进行此类查询的最安全方法。我希望做这样的事情：

pg_prepare($link,"change_user","ALTER ROLE $1 WITH ENCRYPTED PASSWORD '$2';");

但是我得到一个语法错误，即使我尝试在 pgAdminIII 中PREPARE此命令。事实上，Postgres手册指出PREPARE只能准备"任何选择，插入，更新，删除或值语句"。

然后我尝试使用 pg_escape_string() 函数：

$user = pg_escape_string($_POST["user"]);
...

当我使用普通密码时，这很有效，如果我尝试像''"这样的密码，之后我无法更改它。我已经尝试了pg_escape_literal根据手册首选pg_escape_string，但我的 PHP 是版本 5.3.13，此命令仅适用于 5.4.4 及更高版本。

问题是：在这种情况下，这是防止注射攻击的最佳方法吗？这真的可以防止攻击吗？

另一个问题：如果用户想在他/她的密码中使用撇号，是否可以没有这个错误？

正如您所发现的，预准备语句不能用于像ALTER USER这样的"实用程序语句"，它们超出了这个问题的范围。

用户和密码必须正确引用，要正确引用，需要考虑几个问题。

用户是标识符，密码

是字符串文字，这就是为什么标识符没有被单引号包围而密码被包围的原因。它们不遵循相同的语法规则，不能使用相同的函数引用。

PHP-5.4.4 为标识符提供了pg_escape_identifier，但旧版本不提供引用标识符的内容。 pg_escape_string不适合这样做，如其描述和用户评论中所述。PostgreSQL 本身提供了quote_ident函数，但为了调用它，应该进行单独的查询。基本上这可能看起来像这样：

$pgr=pg_query_params($dbconn, 'SELECT quote_ident($1)',
                     array(pg_escape_string($_POST["user"])));
// error check on $pgr omitted for brievity
list($quoted_user) = pg_fetch_array($pgr);
$quoted_password = pg_escape_string($_POST["password"]);
$res=pg_query($dbconn, 
              "ALTER USER $quoted_user WITH ENCRYPTED PASSWORD '$quoted_password'");

但是，您可能会质疑让用户选择他们想要的任何密码而不进行筛选是否是一个好主意。

在它引起的问题中，立即引发的问题之一是您的连接呼叫：

$link = pg_connect("dbname=mydb host=localhost user=$user password=$old connect_timeout=1");

这里没有引用$old中的密码，如果它包含空格字符，这将失败。这就是您提到的此问题的可能原因：如果我尝试设置像"这样的密码，之后我无法更改它。

为了处理pg_connect调用中的特殊字符，文档是这样说的：

每个参数设置的格式为关键字 = 值。周围的空间等号是可选的。写入空值或值包含空格，用单引号括起来，例如，关键字 = 'a 值'。值中的单引号和反斜杠必须转义带反斜杠，即 ''' 和 ''。

因此，如果你准备接受任何东西，你应该提供一个函数来执行这种转义。

就个人而言，我会首先在用户提供的密码中禁止这些字符，理由是这是一个不必要的问题来源。以及由于编码问题而导致的非 US-ASCII 字符。

请注意，我建议为此使用存储过程。如果需要，预准备语句始终可以调用存储过程。

下面是一些非常简单的示例代码，主要从 LedgerSMB 复制（并进行了一些编辑）：

CREATE OR REPLACE FUNCTION save_user(
    in_username text,
    in_password TEXT
) returns bool
SET datestyle = 'ISO, YMD' -- needed due to legacy code regarding datestyles
AS $$
    DECLARE
        stmt text;
        t_is_role bool;
    BEGIN
        -- WARNING TO PROGRAMMERS:  This function runs as the definer and runs
        -- utility statements via EXECUTE.
        -- PLEASE BE VERY CAREFUL ABOUT SQL-INJECTION INSIDE THIS FUNCTION.
       PERFORM rolname FROM pg_roles WHERE rolname = in_username;
       t_is_role := found;
       IF t_is_role is true and t_is_user is false and in_pls_import is false THEN
          RAISE EXCEPTION 'Duplicate user';
        END IF;
        if t_is_role and in_password is not null then
                execute 'ALTER USER ' || quote_ident( in_username ) ||
                     ' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password)
                     || $e$ valid until $e$ ||
                      quote_literal(now() + '1 day'::interval);
        elsif  t_is_role is false THEN
            -- create an actual user
                execute 'CREATE USER ' || quote_ident( in_username ) ||
                     ' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password)
                     || $e$ valid until $e$ || quote_literal(now() + '1 day'::interval);
       END IF;
       return true;
    END;
$$ language 'plpgsql' SECURITY DEFINER;

请注意，这是一个安全定义器函数。通常建议将此功能设置为由非数据库超级用户的用户拥有。此类用户需要 createrole 权限和对要在此处管理的任何表的访问权限。还要注意警告。如果您没有适当地转义参数，则可以进行数据库内 SQL 注入。请注意，此函数既可以创建用户，也可以更改密码。此处省略了有关检测用户是否已设置以及处理案例（如果未设置）的 LSMB 特定部分。