Thread: Question about openSSL

> 1.)     I went to the OpenSSL ste, and tried to download
> opnSSL, but I only saw Linux files. Is OpenSSL supported ni Windows?

Yes. They do a good job of hiding their binaries thouhg. You can get
them from http://www.openssl.org/related/binaries.html.


> 2.)     I am using the Npgsql.NET data provider to connect,
> but it doesn't support SSL yet, when will it support SSL?

AFAIK, it does, an dhas for quite long. That's why you need a
Mono.Security DLL file, included in the binary package of npgsql.

//Magnus

Greg Quinn wrote:
> 1.)     I went to the OpenSSL ste, and tried to download opnSSL, but I
> only saw Linux files. Is OpenSSL supported ni Windows?

http://www.openssl.org/related/binaries.html

>
> 2.)     I am using the Npgsql.NET data provider to connect, but it
> doesn’t support SSL yet, when will it support SSL?

You should ask this on the Npgsql.Net projects mailing list.

Joshua D. Drake


>
>
>
> Thanks
>
>
>


--

             === The PostgreSQL Company: Command Prompt, Inc. ===
       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
       Providing the most comprehensive  PostgreSQL solutions since 1997
                      http://www.commandprompt.com/

Greg Quinn wrote:
> 1.)     I went to the OpenSSL ste, and tried to download opnSSL, but I
> only saw Linux files. Is OpenSSL supported ni Windows?
>
> 2.)     I am using the Npgsql.NET data provider to connect, but it
> doesn’t support SSL yet, when will it support SSL?
>
>
>
> Thanks
>
>
>
Yes, it works on win32 and you can use the two dlls that are included
with any of the GUI clients out there or the ones that come with the
win32 build of Postgresql.

You will need to copy the two dlls (ssleay32.dll,libeay32.dll) to the
same directory where you have your npgsql.net assembly.
You could also put them in your system32 directory, but this will cause
all applications to use those copies which could cause problems.

MS changed the way the DLL search works in later SPs of windows 2000 and
XP.  It always checks the system32 directory first, then the application
directory and this is opposite of how it used to work.  Before it always
checked the app directory first, and this was nice because you could
always include your one copies of the DLLs to make sure your app always
had the correct version, but some stupid malware writer used this to
their advantage and MS had to make the change.

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tony Caduto
> Sent: 19 June 2006 03:51
> To: Greg Quinn; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Question about openSSL
>
> Greg Quinn wrote:
> > 1.)     I went to the OpenSSL ste, and tried to download
> opnSSL, but I
> > only saw Linux files. Is OpenSSL supported ni Windows?
> >
> > 2.)     I am using the Npgsql.NET data provider to connect, but it
> > doesn't support SSL yet, when will it support SSL?
> >
> >
> >
> > Thanks
> >
> >
> >
> Yes, it works on win32 and you can use the two dlls that are included
> with any of the GUI clients out there or the ones that come with the
> win32 build of Postgresql.
>
> You will need to copy the two dlls (ssleay32.dll,libeay32.dll) to the
> same directory where you have your npgsql.net assembly.
> You could also put them in your system32 directory, but this
> will cause
> all applications to use those copies which could cause problems.
>
> MS changed the way the DLL search works in later SPs of
> windows 2000 and
> XP.  It always checks the system32 directory first, then the
> application
> directory and this is opposite of how it used to work.
> Before it always
> checked the app directory first, and this was nice because you could
> always include your one copies of the DLLs to make sure your
> app always
> had the correct version, but some stupid malware writer used this to
> their advantage and MS had to make the change.

Err, not quite correct. The app directory is *always* the first choice
in either safe or unsafe mode, except if using LoadLibraryEx's alternate
search order. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc
/base/dynamic-link_library_search_order.asp.

We rely on that to allow side-by-side PostgreSQL 8.0 and 8.1
installations on Windows. The former installed libpq et al. in the
system32 directory, whilst 8.1+ have private copies.

Regards, Dave.

> You will need to copy the two dlls
> (ssleay32.dll,libeay32.dll) to the same directory where you
> have your npgsql.net assembly.

If we're talking npgsql (http://pgfoundry.org/projects/npgsql), that
information is incorrect. Npgsql uses a managed implementation of SSL,
so it does *not* use the OpenSSL libraries.

If we're talking some other .net dataprovider (IIRC there is at least
one other out there somewhere, though I haven't used it and can't
remember exactly what it's called), then it may or may not be using
openssl.

//Magnus

Well, then what do I need to get NpGSQL connecting to my server with SSL?

Do I just turn SSL on in the database and in my connection string set SSL to true?

The other provider is CoreLab .NET for PostGreSQL. It seems pretty good, but don't see the point in
paying for a provider when I can get one for free.

----- Original Message -----
From: Magnus Hagander
To: <pgsql-general@postgresql.org>
Sent:  Mon, 19 Jun 2006 09:48:30 +0200
Subject: RE: [GENERAL] Question about openSSL

> You will need to copy the two dlls
> (ssleay32.dll,libeay32.dll) to the same directory where you
> have your npgsql.net assembly.

If we're talking npgsql (http://pgfoundry.org/projects/npgsql), that
information is incorrect. Npgsql uses a managed implementation of SSL,
so it does *not* use the OpenSSL libraries.

If we're talking some other .net dataprovider (IIRC there is at least
one other out there somewhere, though I haven't used it and can't
remember exactly what it's called), then it may or may not be using
openssl.

//Magnus

> Well, then what do I need to get NpGSQL connecting to my
> server with SSL?
>
> Do I just turn SSL on in the database and in my connection
> string set SSL to true?

Yes. You need to have the Mono.Security.DLL (or similar named - and this
is needed on MS .Net as well!), btu that's it. It's included in the
npgsql binary distributions.


> The other provider is CoreLab .NET for PostGreSQL. It seems
> pretty good, but don't see the point in paying for a provider
> when I can get one for free.

IIRC there is another free one around soemwhere, but it's nowhere near
as full-featured as npgsql - at least it wasn't when I looked at it.

//Magnus

Dave Page wrote:
>
> Err, not quite correct. The app directory is *always* the first choice
> in either safe or unsafe mode, except if using LoadLibraryEx's alternate
> search order. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc
> /base/dynamic-link_library_search_order.asp.
>
> We rely on that to allow side-by-side PostgreSQL 8.0 and 8.1
> installations on Windows. The former installed libpq et al. in the
> system32 directory, whilst 8.1+ have private copies.
>
> Regards, Dave.
>
>
Well, I guess you don't need openssl with npgsql. I stand corrected.

As far as the search order goes, the link you gave does not validate the
behavior I see on my fully patched version of XP and fully patch versions
of win2k in a corp environment.

The MSDN docs are not always correct or fully updated.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

> -----Original Message-----
> From: Tony Caduto [mailto:tony_caduto@amsoftwaredesign.com]
> Sent: 19 June 2006 14:27
> To: Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Question about openSSL
>
> Dave Page wrote:
> >
> > Err, not quite correct. The app directory is *always* the
> first choice
> > in either safe or unsafe mode, except if using
> LoadLibraryEx's alternate
> > search order. See
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-
> us/dllproc
> > /base/dynamic-link_library_search_order.asp.
> >
> > We rely on that to allow side-by-side PostgreSQL 8.0 and 8.1
> > installations on Windows. The former installed libpq et al. in the
> > system32 directory, whilst 8.1+ have private copies.
> >
> > Regards, Dave.
> >
> >
> Well, I guess you don't need openssl with npgsql. I stand corrected.

<shrug>I didn't mention npgsql.

> As far as the search order goes, the link you gave does not
> validate the
> behavior I see on my fully patched version of XP and fully
> patch versions
> of win2k in a corp environment.
>
> The MSDN docs are not always correct or fully updated.

Well, before we re-hashed pgInstaller to work the way it now does,
thorough testing was undertaken on 2K, XP and 2K3, with and without
various levels of service pack and in all cases we found that the DLL in
the app dir was used over a pre-existing shared one, exactly as
documented by Microsoft. We *had* to be sure that was working correctly,
otherwise 8.1 installs would be using 8.0 DLLs and chaos would have
reigned!

You might not see it on the lists because Magnus and I tend to work via
IM on pgInstaller, but we both do significant testing using VMs running
various builds of Windows. Both of us having MSDN subscriptions make
life so much easier!

Regards, Dave.

Hi,

I'm using PQexecParams so I can upload filenames including path that
have '\' characters.  It works everywhere else I used it but not with
this query.

I get ERROR:  there is no parameter $1.

INSERT INTO FileVersionHistory(applicationID, filename, createFile,
changeSetID, userName) VALUES (1,$1::text,FALSE,9,'postgres');

outTemp[0]=deletedFiles.fileList[fileListIndex].filename;
outLengths[0]=strlen(deletedFiles.fileList[fileListIndex].filename);
formats[0]=0;
result = PQexecParams(pgConn, query,1,0,outTemp,outLengths,formats,0);

There obviously is a $1 parameter.

Ideas?

Kevin Jenkins <gameprogrammer@rakkar.org> writes:
> I get ERROR:  there is no parameter $1.

> INSERT INTO FileVersionHistory(applicationID, filename, createFile,
> changeSetID, userName) VALUES (1,$1::text,FALSE,9,'postgres');

> outTemp[0]=deletedFiles.fileList[fileListIndex].filename;
> outLengths[0]=strlen(deletedFiles.fileList[fileListIndex].filename);
> formats[0]=0;
> result = PQexecParams(pgConn, query,1,0,outTemp,outLengths,formats,0);

> There obviously is a $1 parameter.

Hm, nothing obviously wrong there.  What PG version is this exactly?
Can you supply a self-contained test case?

            regards, tom lane

Tom Lane wrote:
> Kevin Jenkins <gameprogrammer@rakkar.org> writes:
>> I get ERROR:  there is no parameter $1.
>
>> INSERT INTO FileVersionHistory(applicationID, filename, createFile,
>> changeSetID, userName) VALUES (1,$1::text,FALSE,9,'postgres');
>
>> outTemp[0]=deletedFiles.fileList[fileListIndex].filename;
>> outLengths[0]=strlen(deletedFiles.fileList[fileListIndex].filename);
>> formats[0]=0;
>> result = PQexecParams(pgConn, query,1,0,outTemp,outLengths,formats,0);
>
>> There obviously is a $1 parameter.
>
> Hm, nothing obviously wrong there.  What PG version is this exactly?
> Can you supply a self-contained test case?
>
>             regards, tom lane

I made the smallest test case that I can and uploaded it here:
http://www.rakkarsoft.com/PostgreBug.zip

I'm sorry in advance I couldn't make it smaller but hopefully this is
still manageable.

It assumes you have C:\Program Files\PostgreSQL\8.1 in the project
settings for the include path.

To run,
1. Open the solution
2. Build the project "Autopatcher"
3. Build the solution "AutopatcherPostgreSQL"
4. Put a breakpoint on line 346 of PostgreSQLRepository.cpp in the
solution AutopatcherPostgreSQL (in the directory
"DependentExtensions\AutopatcherPostgreSQL")

The code at that breakpoint should be at:
sprintf(query, "INSERT INTO FileVersionHistory(applicationID,
filename, createFile, changeSetID, userName) VALUES (%i,
$1::text,FALSE,%i,'%s');", applicationID, changeSetId, userName);

5. Run the project autopatcher.
6. Hit 's' for server.  It will assume the username is postgre (if
this is wrong then just modify AutopatcherTest.cpp - it should be
obvious).
7. Enter the password
8. Hit 'c' for create database
9. Hit 'a' for add application.  Name it whatever you want.
10. Hit 'u' for update revision.  Use a directory with some temporary
files when prompted
11. Delete one of the files in the directory you specified in 10.
12. Hit 'u' again with the same arguments.

At this point you should hit the breakpoint.

What this does is populates a table with the files in that directory.
  You then delete one of the files, it detects it, and adds it to the
table.

The bug is that, at the line of the breakpoint, it returns the error I
described rather than correctly processing the INSERT.

Kevin Jenkins <gameprogrammer@rakkar.org> writes:
> Tom Lane wrote:
>> Hm, nothing obviously wrong there.  What PG version is this exactly?
>> Can you supply a self-contained test case?

> I made the smallest test case that I can and uploaded it here:
> http://www.rakkarsoft.com/PostgreBug.zip
> I'm sorry in advance I couldn't make it smaller but hopefully this is
> still manageable.

I tried to extract a non-Windows-dependent test case out of this,
without much luck.  Anyone else care to try?

I made the attached by plugging your PQexecParams call into
src/test/examples/testlibpq3.c, and it works fine after having
manually created the FileVersionHistory table.

            regards, tom lane

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>


static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn       *conn;
    PGresult   *res;
    char query[512];
    const char *outTemp[3];
    int outLengths[3];
    int formats[3];

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = test";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }


    sprintf(query, "INSERT INTO FileVersionHistory(applicationID, filename, createFile, changeSetID, userName) VALUES
(%i,$1::text,FALSE,%i,'%s');", 
            42, 77, "user");
    outTemp[0]="foo";
    outLengths[0]=strlen(outTemp[0]);
    formats[0]=0;
    res = PQexecParams(conn, query,1,0,outTemp,outLengths,formats,1);

    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}

Previously I was getting a bad result when calling PQexecParams with
binary results because PostgreSQL stores its data big endian.  So I
had to do ntohl on the result to get it to little endian.

My question is, do I also need to do htonl then, as in this scenario?

outStr[0]="blah";
outLengths[0]=htonl((int)strlen("blah"));
formats[0]=1;
PQexecParams(pgConn, query,1,0,outStr,outLengths,formats,1);

On Thu, Jun 22, 2006 at 08:52:04AM -0700, Kevin Jenkins wrote:
> Previously I was getting a bad result when calling PQexecParams with
> binary results because PostgreSQL stores its data big endian.  So I
> had to do ntohl on the result to get it to little endian.

Clarification: PostgreSQL stores data in host byte order but returns
it in network byte order if you request binary format.

> My question is, do I also need to do htonl then, as in this scenario?
>
> outStr[0]="blah";
> outLengths[0]=htonl((int)strlen("blah"));
> formats[0]=1;
> PQexecParams(pgConn, query,1,0,outStr,outLengths,formats,1);

Only the data needs byte order conversion; if you convert lengths
then you'll probably get a database error or segmentation fault
(assuming you're on a machine where host and network byte order
differ).  I tested the above and PQexecParams failed with

  ERROR:  invalid byte sequence for encoding "SQL_ASCII": 0x00

A packet sniff of the connection showed a large amount of data being
transferred (0x04000000 bytes instead of 0x00000004 bytes), so the
length needs to be in host byte order.

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> ... the length needs to be in host byte order.

Right.  The only place where a libpq client would be dealing with
network-byte-order data is within binary-format values for data fields
(in SELECT results) or binary-format parameters (when transmitting to
the database).  Metadata like field lengths is just in the local native
representation.

            regards, tom lane