如何在Postgresql中参数化ALTER ROLE语句？ [英] How to parameterize an ALTER ROLE statement in Postgresql?
问题描述
我正在尝试为我的PSQL数据库建立一个PHP接口，并且我希望一些在PSQL上注册的本地用户登录到我的数据库。我首先要为每个用户创建一个用户名，并使用通用密码（例如 Password123），然后用户可以更改其密码。
I'm trying to make a PHP interface to my PSQL database, and I would like a few local users registered on PSQL to log into my DB. I would first create each username for each user, with a generic password like 'Password123', and then the user could latter change his/her password.
为此，我想到了使用简单的PHP表单：
To do that, I thought of using a simple PHP form:
<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);
}
?>
它确实按我的预期工作！
It does work just as I expected!
但是我读到，可能会对像这样的表达式进行一些SQL注入攻击，其中SQL查询内部有简单的变量插值。因此，我读到 PREPARE 语句是执行此类查询的最安全方法。我希望这样做：
But I read that there are some SQL Injection Attacks that could be made on expressions like this where there is a simple variable interpolation inside the SQL query. And so I read that PREPARE statements are the safest way to do such queries. I expected to do something like:
pg_prepare($link,"change_user","ALTER ROLE $1 WITH ENCRYPTED PASSWORD '$2';");
但是我遇到语法错误，即使我尝试 PREPARE pgAdminIII中的此命令。实际上， Postgres手册表示 PREPARE 只能准备任何SELECT，INSERT，UPDATE，DELETE或VALUES语句。
But I get a syntax error, even if I try to PREPARE this command in pgAdminIII. Indeed, Postgres manual indicates that PREPARE can only prepare "Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement."
然后我尝试使用 pg_escape_string（）函数：
$user = pg_escape_string($_POST["user"]);
...
当我使用普通密码时，如果我尝试设置'' 这样的密码，之后便无法更改。我尝试了 pg_escape_literal 相对于 pg_escape_string 而言，它是首选到手册上，但我的PHP版本是5.3.13，此命令仅适用于5.4.4及更高版本。
This works well when I use normal passwords, and if I try to set a password like ''", I cannot change it afterwards. I have tried pg_escape_literal which is preferred to pg_escape_string according to the manual, but my PHP is version 5.3.13, and this command is only for 5.4.4 onwards.
问题是：这是预防的最佳方法吗？
The question is: is this the best way to prevent injection attacks in this case? Does this really prevent attacks?
另一个问题：如果用户想在密码中使用撇号，是否有可能出现此错误？
Another question: if a user wants to use an apostrophe in his/her password, is it possible without this error?
推荐答案
请注意，我建议为此使用存储过程，如果需要的话，准备好的语句总是可以调用存储的
As a note, I would recommend using a stored procedure for this. A prepared statement if needed could always call the stored procedure.
下面是一些非常简单的示例代码，大部分从LedgerSMB复制（并进行了一些编辑）：
Here's some very simple sample code, largely copied from LedgerSMB (and edited a bit):
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特定部分。
Note that this is a security definer function. It is generally recommended that you set this function to be owned by a user which is not a database superuser. Such a user would need createrole privileges and access to whatever tables you want to manage here. Also note the warning heavily. In-db SQL-injection is possible if you don't appropriately escape your parameters. Note that this function can both create a user and change a password. The LSMB-specific portions regarding detecting whether the user is set up and handling the case if not are omitted here.
这篇关于如何在Postgresql中参数化ALTER ROLE语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
