问题描述
我正在尝试为我的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中的此命令。实际上,表示 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 到手册上,但我的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语句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!