Re:Re: Does psqlodbc_11_01_0000-x64 support special characters?

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(at)gmail(dot)com> wrote:
>On Wed, Oct 12, 2022 at 7:16 AM gzh <gzhcoder(at)126(dot)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(at)126(dot)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!
>>