谢谢这本书 http://www.packtpub.com/postgresql-9-admin-cookbook/book
请注意,此处提供的函数有效地绕过了安全限制,这是有原因的。您的函数应根据严格的白名单条件检查提供的文件路径和表。该示例也对 SQL 注入开放,因为它没有正确引用其输入。
创建执行的函数COPY
command
CREATE OR REPLACE FUNCTION copy_from_csv_ignoring_security(table_name text, table_fieds text, file_path text, oids boolean DEFAULT false, header boolean DEFAULT false, delimeter text DEFAULT ','::text, "null" text DEFAULT ''::text, quote text DEFAULT '"'::text, escape text DEFAULT '"'::text, force_not_null text DEFAULT ''::text)
RETURNS void AS
$BODY$
declare statement text;
begin
statement := 'COPY ' || table_name || ' (' || table_fieds || ') ' || 'FROM ''' || file_path || ''' WITH ';
IF oids THEN
statement := statement || 'OIDS ';
end if;
statement := statement || 'DELIMITER ''' || delimeter || ''' ';
statement := statement || 'NULL ''' || "null" || ''' CSV ';
IF header THEN
statement := statement || 'HEADER ';
end if;
statement := statement || 'QUOTE ''' || "quote" || ''' ';
statement := statement || 'ESCAPE ''' || "escape" || ''' ';
IF force_not_null <> '' THEN
statement := statement || 'FORCE NOT NULL ''' || force_not_null || ''' ';
end if;
execute statement;
end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
赋予功能权限
revoke all on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) from public;
grant execute on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) to db_user;
从 PHP 执行
$dbh->exec('SELECT copy_from_csv_ignoring_security(...)');
===== 如果版本 >= 9.1.7 以上技巧不起作用。 =====
解决方案:
创建文件.pgpass http://www.postgresql.org/docs/9.2/static/libpq-pgpass.html(避免密码提示)位于运行此脚本的用户的主目录中。
#.pgpass contents (chmod 600 - requred)
host:port:db_name:user_name:password
创建php函数,执行元命令 http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-META-COMMANDS
function executeMetaCommand($dbUser, $dbName, $dbPort, $command)
{
$command = sprintf(
"psql -U %s -p %s -d %s -f - <<EOT\n%s\nEOT\n",
$dbUser, $dbPort, $dbName, $command
);
$streams = array(
array('pipe', 'r'),// stdin
array('pipe', 'w'),// stdout
array('pipe', 'w') // stderr
);
$process = proc_open($command, $streams, $pipes);
if (!is_resource($process)) {
throw new Exception("Cannot open process:\n$command");
} else {
list(, $stdout, $stderr) = $pipes;
$error = stream_get_contents($stderr);
fclose($stderr);
if (strlen($error) > 0) {
throw new Exception("Process error:\n$error");
} else {
$output = stream_get_contents($stdout);
fclose($stdout);
$returnCode = proc_close($process);
if ($returnCode === -1) {
throw new Exception("Process was completed incorrectly:\n$output");
} else {
return array(
$returnCode,
$output
);
}
}
}
}
//usage:
$command = sprintf("\\copy table(field1, field2) FROM '%s' WITH CSV", $filePath);
executeMetaCommand('postgres', 'test_db', '5432', $command);