在Postgresql中转义反斜杠 [英] Escaping backslash in Postgresql
问题描述
我试图在Postgresql中编写一个sql函数来解析文件路径。
我想只返回文件名。
我无法得到一个准确的文本字符串的功能。
这是功能:
功能:job_page(inputText文本)
DECLARE
$ 5文字;
BEGIN
$ 5 = quote_literal(inputText);
返回$ 5;
END
当我运行:
select job_page('\\CAD_SVR\CADJOBS\7512-CEDARHURST ELEMENTARY SCHOOL\7512-20.DWG')
我得到这个结果:
E'\\\CAD_SVRCADJOBSé2-CEDARHURST ELEMENTARYSCHOOLé2-20.DWG'
Postgresql解释斜杠其次是某些字符作为特殊字符。
我如何逃避?
您应该使用转义字符串语法:
选择E'\\\CADCADVR\\CADJOBS\ \7512-CEDARHURST ELEMENTARY SCHOOL\\\7512-20.DWG';
\CAD_SVR\CADJOBS\7512-CEDARHURST ELEMENTARY SCHOOL\7512-20.DWG
在任何情况下都可以使用。
或者您可以设置standard_conforming_strings = on
使用:
选择'\CAD_SVR\CADJOBS\7512-CEDARHURST / ELEMENTARY SCHOOL\7512-20.DWG' ;
\CAD_SVR\CADJOBS\7512-CEDARHURST / ELEMENTARY SCHOOL\7512-20.DWG
quote_literal
函数只能在您构建 exec
调用的查询时使用在pl / pgsql函数中。要在客户端中构建查询,您应该使用客户端的库引用函数,如PHP中的 PQescapeStringConn
或PHP中的 pg_escape_string
。但最好的选择是使用准备好的语句并使用字符串作为参数,这样可以消除所有的引用,并且更安全。
I'm trying to write an sql function in Postgresql that will parse a file path. I want to return just the file name.
I cannot get past getting an accurate text string in the function.
Here is the function:
Function: job_page("inputText" text)
DECLARE
$5 text;
BEGIN
$5 = quote_literal("inputText");
return $5;
END
When I run this:
select job_page('\\CAD_SVR\CADJOBS\7512-CEDARHURST ELEMENTARY SCHOOL\7512-20.DWG')
I get this result:
"E'\\CAD_SVRCADJOBSé2-CEDARHURST ELEMENTARY SCHOOLé2-20.DWG'"
Postgresql interprets the slash followed by certain characters as a special character.
How do I escape?
You should use escape string syntax:
select E'\\CAD_SVR\\CADJOBS\\7512-CEDARHURST ELEMENTARY SCHOOL\\7512-20.DWG';
\CAD_SVR\CADJOBS\7512-CEDARHURST ELEMENTARY SCHOOL\7512-20.DWG
This will work in any case.
Or you can set standard_conforming_strings=on
and use:
select '\CAD_SVR\CADJOBS\7512-CEDARHURST/ ELEMENTARY SCHOOL\7512-20.DWG';
\CAD_SVR\CADJOBS\7512-CEDARHURST/ ELEMENTARY SCHOOL\7512-20.DWG
quote_literal
function should be used only when you will be constructing a query for exec
call in pl/pgsql function. For constructing a query in a client you should use a client's library quoting function, like PQescapeStringConn
in libpq or pg_escape_string
in PHP. But the best option is to use prepared statements and use a string as an argument, which eliminates all quoting and is much safer too.
这篇关于在Postgresql中转义反斜杠的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!