Обсуждение: Does psqlodbc_11_01_0000-x64 support special characters?
PostgreSQL version: 13.5
Operating system: windows 10
Description:
I wrote a VBA application to connect to PostgreSQL database by psqlodbc.
The application works fine when there are no special characters in the password.
When the password contains special characters (e.g. * , $ %),
the application responds with an error below:
Number: -2147467259
Description: password authentication failed for user 'testdb'
I made an sample as below:
VBA
----------------------------- START ---------------------------------
Sub dbconnTest()
Dim rs As ADODB.Recordset
Dim sql As String
Dim i As Integer
Dim rcnt As Integer
Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ"
Set rs = New ADODB.Recordset
sql = "SELECT * FROM testtbl"
rs.ActiveConnection = cnn
rs.Source = sql
rs.Open
cnt = rs.Fields.Count
rcnt = 2
Do Until rs.EOF
For i = 0 To cnt - 1
Cells(rcnt, i + 1).Value = rs.Fields(i)
Next
rcnt = rcnt + 1
rs.MoveNext
Loop
Set rs = Nothing
Set cnn = Nothing
End Sub
----------------------------- END ---------------------------------
I found that the password can't contain the % character, and the other special characters (* , $) are no problem.
At 2022-10-12 16:28:51, "gzh" <gzhcoder@126.com> wrote:
PostgreSQL version: 13.5
Operating system: windows 10
Description:
I wrote a VBA application to connect to PostgreSQL database by psqlodbc.
The application works fine when there are no special characters in the password.
When the password contains special characters (e.g. * , $ %),
the application responds with an error below:
Number: -2147467259
Description: password authentication failed for user 'testdb'
I made an sample as below:
VBA
----------------------------- START ---------------------------------
Sub dbconnTest()
Dim rs As ADODB.Recordset
Dim sql As String
Dim i As Integer
Dim rcnt As Integer
Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ"
Set rs = New ADODB.Recordset
sql = "SELECT * FROM testtbl"
rs.ActiveConnection = cnn
rs.Source = sql
rs.Open
cnt = rs.Fields.Count
rcnt = 2
Do Until rs.EOF
For i = 0 To cnt - 1
Cells(rcnt, i + 1).Value = rs.Fields(i)
Next
rcnt = rcnt + 1
rs.MoveNext
Loop
Set rs = Nothing
Set cnn = Nothing
End Sub
----------------------------- END ---------------------------------
Thanks for any help!
On Wed, Oct 12, 2022 at 7:16 AM gzh <gzhcoder@126.com> wrote: > > I found that the password can't contain the % character, and the other special characters (* , $) are no problem. You need to percent-encode the password if you wish to use the % symbol in the password. There are other reserved characters that you should percent-encode. See https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING and https://www.rfc-editor.org/rfc/rfc3986#section-2.1 . Jeff > At 2022-10-12 16:28:51, "gzh" <gzhcoder@126.com> wrote: > > > PostgreSQL version: 13.5 > > Operating system: windows 10 > > Description: > > > I wrote a VBA application to connect to PostgreSQL database by psqlodbc. > > The application works fine when there are no special characters in the password. > > When the password contains special characters (e.g. * , $ %), > > the application responds with an error below: > > > Number: -2147467259 > > Description: password authentication failed for user 'testdb' > > > I made an sample as below: > > > VBA > > ----------------------------- START --------------------------------- > > > Sub dbconnTest() > > Dim rs As ADODB.Recordset > > Dim sql As String > > Dim i As Integer > > Dim rcnt As Integer > > > > Set cnn = New ADODB.Connection > > cnn.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ" > > > > Set rs = New ADODB.Recordset > > sql = "SELECT * FROM testtbl" > > > > rs.ActiveConnection = cnn > > rs.Source = sql > > rs.Open > > > > cnt = rs.Fields.Count > > rcnt = 2 > > > > Do Until rs.EOF > > For i = 0 To cnt - 1 > > Cells(rcnt, i + 1).Value = rs.Fields(i) > > Next > > > > rcnt = rcnt + 1 > > rs.MoveNext > > Loop > > > > Set rs = Nothing > > Set cnn = Nothing > > End Sub > > > ----------------------------- END --------------------------------- > > > > Thanks for any help! >
Dear Jeff
I appreciate your reply.
My PostgreSQL is deployed on Amazon RDS, so the password of PostgreSQL is random and has various reserved characters.
I don't know if the reserved characters below are complete, and there are some characters (e.g. * , $) I tried without problems.
Could you tell me which characters require percent-encoding for PostgreSQL password?
space → %20
! → %21
" → %22
# → %23
$ → %24
% → %25
& → %26
' → %27
( → %28
) → %29
* → %2A
+ → %2B
, → %2C
- → %2D
. → %2E
/ → %2F
: → %3A
; → %3B
< → %3C
= → %3D
> → %3E
? → %3F
@ → %40
[ → %5B
\ → %5C
] → %5D
^ → %5E
_ → %5F
` → %60
{ → %7B
| → %7C
} → %7D
~ → %7E
Kind regards,
gzh
At 2022-10-12 22:01:15, "Jeffrey Walton" <noloader@gmail.com> wrote: >On Wed, Oct 12, 2022 at 7:16 AM gzh <gzhcoder@126.com> wrote: >> >> I found that the password can't contain the % character, and the other special characters (* , $) are no problem. > >You need to percent-encode the password if you wish to use the % >symbol in the password. There are other reserved characters that you >should percent-encode. See >https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING >and https://www.rfc-editor.org/rfc/rfc3986#section-2.1 . > >Jeff > >> At 2022-10-12 16:28:51, "gzh" <gzhcoder@126.com> wrote: >> >> >> PostgreSQL version: 13.5 >> >> Operating system: windows 10 >> >> Description: >> >> >> I wrote a VBA application to connect to PostgreSQL database by psqlodbc. >> >> The application works fine when there are no special characters in the password. >> >> When the password contains special characters (e.g. * , $ %), >> >> the application responds with an error below: >> >> >> Number: -2147467259 >> >> Description: password authentication failed for user 'testdb' >> >> >> I made an sample as below: >> >> >> VBA >> >> ----------------------------- START --------------------------------- >> >> >> Sub dbconnTest() >> >> Dim rs As ADODB.Recordset >> >> Dim sql As String >> >> Dim i As Integer >> >> Dim rcnt As Integer >> >> >> >> Set cnn = New ADODB.Connection >> >> cnn.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ" >> >> >> >> Set rs = New ADODB.Recordset >> >> sql = "SELECT * FROM testtbl" >> >> >> >> rs.ActiveConnection = cnn >> >> rs.Source = sql >> >> rs.Open >> >> >> >> cnt = rs.Fields.Count >> >> rcnt = 2 >> >> >> >> Do Until rs.EOF >> >> For i = 0 To cnt - 1 >> >> Cells(rcnt, i + 1).Value = rs.Fields(i) >> >> Next >> >> >> >> rcnt = rcnt + 1 >> >> rs.MoveNext >> >> Loop >> >> >> >> Set rs = Nothing >> >> Set cnn = Nothing >> >> End Sub >> >> >> ----------------------------- END --------------------------------- >> >> >> >> Thanks for any help! >>
On Thu, Oct 13, 2022 at 12:13 AM gzh <gzhcoder@126.com> wrote:
>
> My PostgreSQL is deployed on Amazon RDS, so the password of PostgreSQL is random and has various reserved characters.
>
> I don't know if the reserved characters below are complete, and there are some characters (e.g. * , $) I tried
withoutproblems.
>
> Could you tell me which characters require percent-encoding for PostgreSQL password?
>
>
> space → %20
>
> ! → %21
>
> " → %22
>
> # → %23
>
> $ → %24
>
> % → %25
>
> & → %26
>
> ' → %27
>
> ( → %28
>
> ) → %29
>
> * → %2A
>
> + → %2B
>
> , → %2C
>
> - → %2D
>
> . → %2E
>
> / → %2F
>
> : → %3A
>
> ; → %3B
>
> < → %3C
>
> = → %3D
>
> > → %3E
>
> ? → %3F
>
> @ → %40
>
> [ → %5B
>
> \ → %5C
>
> ] → %5D
>
> ^ → %5E
>
> _ → %5F
>
> ` → %60
>
> { → %7B
>
> | → %7C
>
> } → %7D
>
> ~ → %7E
https://www.rfc-editor.org/rfc/rfc3986#section-2.2
Jeff
Thank you for the information.
After testing, I found that I only need to escape the following 7 characters.
% → %25
" → %22
' → %27
+ → %2B
; → %3B
= → %3D
{ → %7B
At 2022-10-13 13:27:16, "Jeffrey Walton" <noloader@gmail.com> wrote: >On Thu, Oct 13, 2022 at 12:13 AM gzh <gzhcoder@126.com> wrote: >> >> My PostgreSQL is deployed on Amazon RDS, so the password of PostgreSQL is random and has various reserved characters. >> >> I don't know if the reserved characters below are complete, and there are some characters (e.g. * , $) I tried without problems. >> >> Could you tell me which characters require percent-encoding for PostgreSQL password? >> >> >> space → %20 >> >> ! → %21 >> >> " → %22 >> >> # → %23 >> >> $ → %24 >> >> % → %25 >> >> & → %26 >> >> ' → %27 >> >> ( → %28 >> >> ) → %29 >> >> * → %2A >> >> + → %2B >> >> , → %2C >> >> - → %2D >> >> . → %2E >> >> / → %2F >> >> : → %3A >> >> ; → %3B >> >> < → %3C >> >> = → %3D >> >> > → %3E >> >> ? → %3F >> >> @ → %40 >> >> [ → %5B >> >> \ → %5C >> >> ] → %5D >> >> ^ → %5E >> >> _ → %5F >> >> ` → %60 >> >> { → %7B >> >> | → %7C >> >> } → %7D >> >> ~ → %7E > >https://www.rfc-editor.org/rfc/rfc3986#section-2.2 > >Jeff