You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@httpd.apache.org by Tom Browder <to...@gmail.com> on 2017/04/25 17:03:00 UTC

[users@httpd] Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Host: httpd version 2.4.25, Debian 8, 64-bit

I am so close but getting the following error:

[dbd:error] [pid 14137:tid 140512755222272] (20014)Internal error:
AH00632: failed to prepare SQL statements: near "authn_query": syntax
error
[dbd:error] [pid 14137:tid 140512755222272] (20014)Internal error:
AH00633: failed to initialise
[authn_dbd:error] [pid 14137:tid 140512755222272] [client
76.3.0.179:43269] AH01653: Failed to acquire database connection to
look up user 'Browder', referer:
https://canterburycircle.us/login.html

The chunk of my http.conf that I believe may be the problem is here
It's an include file in the main httpd.conf file):

#### TLS SITES ####
<Macro VHOST_CLASS2_CERT_TLS ${PROJECT} ${TLD}>
  <VirtualHost *:80>
    ServerName  ${PROJECT}.${TLD}
    ServerAlias www.${PROJECT}.${TLD}
    # enforce ssl/tls
    Redirect Permanent / https://${PROJECT}.${TLD}/
  </VirtualHost>

  ##### BEGIN TLS OPERATIONS #####
  <VirtualHost *:443>
    SSLEngine on
    ServerName  ${PROJECT}.${TLD}
    ServerAlias www.${PROJECT}.${TLD}
    DocumentRoot /home/web-server/${PROJECT}.${TLD}/public
    <Directory /home/web-server/${PROJECT}.${TLD}/public>
      SSLRequireSSL
    </Directory>

    # cgi directory
    ScriptAlias     /cgi-bin/       /home/web-server/${PROJECT}.${TLD}/cgi-bin
    # data directory
    Alias           /data/          /home/web-server/${PROJECT}.${TLD}/data

    # server SSL/TLS certificate data
    SSLCertificateFile
/home/tbrowde/letsencrypt-certs/${PROJECT}.${TLD}/fullchain.cer
    SSLCertificateKeyFile
/home/tbrowde/letsencrypt-certs/${PROJECT}.${TLD}/${PROJECT}.${TLD}.key

    # dbd params in virtual host server context
    # for SQLite
    DBDriver sqlite3
    # params string for SQLite3 is passed through as is
    # pass in the dbd absolute file name
    DBDParams  /home/web-server/passwords/${PROJECT}.${TLD}.sqlite3
    # following are set to defaults
    DBDExptime 300
    DBDKeep 2
    DBDMax 10
    DBDMin 1
    DBDPersist On
    # for authentication
    # DBDPrepareSQL "SQL statement" label
    DBDPrepareSQL "SELECT password FROM authn WHERE user = '%s';" authn_query

    # The following belongs in the virtual host server context inside
    # a Directory directive (may be a macro later).
    <Directory /home/web-server/${PROJECT}.${TLD}/public/private>

      AuthType form
      AuthName ${PROJECT}.${TLD}
      AuthBasicProvider socache dbd
      AuthnCacheProvideFor dbd
      AuthnCacheContext server
      AuthDBDUserPWQuery authn_query

      Require valid-user
      # 60 min max
      SessionMaxAge 3600

    </Directory>

    # The following is the login handler, the login form needs to
    # point to this handler in its action!
    <Location /dologin>
      SetHandler form-login-handler
      AuthFormLoginRequiredLocation https://${PROJECT}.${TLD}/login.html
      AuthFormLoginSuccessLocation https://${PROJECT}.${TLD}/private/index.html

      #--- following varies for file or dbd
      AuthFormProvider dbd

      AuthType form
      AuthName ${PROJECT}.${TLD}
      Session On
      SessionCookieName session path=/

      # The following requires mod_crypto, pass phrase considerations
      # (use Last Pass to generate a strong one and put it in a file).
      SessionCryptoPassphraseFile
/home/web-server/passwords/${PROJECT}.${TLD}.secret
    </Location>

    # This is the location setting: When a user comes to this location
    # unauthorised, he will be redirected to the login form This happens
    # as the ErrorDoucment gets overwritten with the login page.
    <Location /private/index.html>
      Require valid-user
      AuthFormProvider dbd
      ErrorDocument 401 /login.html

      #--- following varies for file or dbd
      AuthType form

      AuthName ${PROJECT}.${TLD}
      AuthFormLoginRequiredLocation https://${PROJECT}.${TLD}/login.html
      Session On
      SessionCookieName session path=/

      # The following requires mod_crypto, pass phrase considerations
      # (use Last Pass to generate a strong one and put it in a file).
      SessionCryptoPassphraseFile
/home/web-server/passwords/${PROJECT}.${TLD}.secret
    </Location>

  </VirtualHost>
  ##### END TLS OPERATIONS #####
</Macro>
Use VHOST_CLASS2_CERT_TLS  canterburycircle us
UndefMacro VHOST_CLASS2_CERT_TLS

I can post the entire http.conf and its includes if needed.

Note that I can use sqlite3 directly and get a good select as shown in
the code above.

Best regards,

-Tom

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org


Re: [users@httpd] Re: Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Posted by Luca Toscano <to...@gmail.com>.
Hi Tom,

2017-04-28 1:16 GMT+02:00 Tom Donovan <do...@bellatlantic.net>:

> On 04/26/2017 06:49 AM, Tom Browder wrote:
>
>> On Wed, Apr 26, 2017 at 05:06 Tom Browder <tom.browder@gmail.com <mailto:
>> tom.browder@gmail.com>> wrote:
>>
>>     On Wed, Apr 26, 2017 at 04:04 Luca Toscano <toscano.luca@gmail.com
>>     <ma...@gmail.com>> wrote:
>>
>>             > I think I just discovered I what the problem is: I'm using
>> harp.js to
>>             > build my site and the <form...> is compiling incorrectly.
>>
>>             Well, that wasn't the problem.
>>
>>             The error is still:
>>
>>             [dbd:error] [pid 18921:tid 140512673658624] (20014)Internal
>> error:
>>             Â  AH00632: failed to prepare SQL statements: near
>> "authn_query": syntax error
>>
>>
>> I have no clue as to why dbd isn'initializing. Untill someone can tell me
>> exactly how to get it
>> working, I'm going to try the file method. Bummer!
>>
>> -Tom
>>
>
> I am able to reproduce your error, and it does seem to be a bug in mod_dbd
> when prepared statements are used inside <Directory> blocks for
> authentication.
>
> Instead of using DBDPrepareSQL to create a prepared statement, please try
> removing the DBDPrepareSQL line entirely and put your SQL statement
> directly in your AuthDBDUserPWQuery directive, like this:
>
>      AuthDBDUserPWQuery "SELECT password FROM authn WHERE user = %s"
>
> Note that Luca's advice was correct:  no single-quotes around the %s, and
> no terminating semicolon should be used in the SQL for httpd configuration
> directives, even though you would use them in interactive sqlite3 SQL
> commands.
>
> There should be no performance penalty for doing it this way.
> AuthDBDUserPWQuery automatically generates prepared statements.  Your
> original directives seem reasonable per the current documentation, so it's
> a bug - although I'm not sure (yet) if it's a doc bug or a code bug.



Thanks a lot for your review, I didn't check where the prepared statement
was used. As far as I can tell from
https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html#authdbduserpwquery,
 AuthDBDUserPWQuery requires a SQL statement and not a label, so I think
that the error message "failed to prepare SQL statements: near
"authn_query": syntax error" is related to the fact that mod_dbd can't make
any prepared statement out of the raw string "authn_query".

Am I missing something in the docs that talks about prepared statements and
AuthDBDUserPWQuery? (sorry in case)

About the syntax of the prepared statement: httpd leverages APR's dbd SQL
syntax, that is outlined in:
https://apr.apache.org/docs/apr/2.0/group___a_p_r___util___d_b_d.html#gacf21412447c4357c64d1e9200a0f5eec
(so no vendor specific statements).

The documentation definitely needs some improvement :/

Luca

Re: [users@httpd] Re: Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Posted by Tom Donovan <do...@bellatlantic.net>.
On 04/26/2017 06:49 AM, Tom Browder wrote:
> On Wed, Apr 26, 2017 at 05:06 Tom Browder <tom.browder@gmail.com <ma...@gmail.com>> wrote:
>
>     On Wed, Apr 26, 2017 at 04:04 Luca Toscano <toscano.luca@gmail.com
>     <ma...@gmail.com>> wrote:
>
>             > I think I just discovered I what the problem is: I'm using harp.js to
>             > build my site and the <form...> is compiling incorrectly.
>
>             Well, that wasn't the problem.
>
>             The error is still:
>
>             [dbd:error] [pid 18921:tid 140512673658624] (20014)Internal error:
>             �  AH00632: failed to prepare SQL statements: near "authn_query": syntax error
>
>
> I have no clue as to why dbd isn'initializing. Untill someone can tell me exactly how to get it
> working, I'm going to try the file method. Bummer!
>
> -Tom

I am able to reproduce your error, and it does seem to be a bug in mod_dbd when prepared statements 
are used inside <Directory> blocks for authentication.

Instead of using DBDPrepareSQL to create a prepared statement, please try removing the DBDPrepareSQL 
line entirely and put your SQL statement directly in your AuthDBDUserPWQuery directive, like this:

      AuthDBDUserPWQuery "SELECT password FROM authn WHERE user = %s"

Note that Luca's advice was correct:  no single-quotes around the %s, and no terminating semicolon 
should be used in the SQL for httpd configuration directives, even though you would use them in 
interactive sqlite3 SQL commands.

There should be no performance penalty for doing it this way.  AuthDBDUserPWQuery automatically 
generates prepared statements.  Your original directives seem reasonable per the current 
documentation, so it's a bug - although I'm not sure (yet) if it's a doc bug or a code bug.

-tom-





---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org


Re: [users@httpd] Re: Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Posted by Tom Browder <to...@gmail.com>.
On Wed, Apr 26, 2017 at 05:06 Tom Browder <to...@gmail.com> wrote:

> On Wed, Apr 26, 2017 at 04:04 Luca Toscano <to...@gmail.com> wrote:
>
>> > I think I just discovered I what the problem is: I'm using harp.js to
>>> > build my site and the <form...> is compiling incorrectly.
>>>
>>> Well, that wasn't the problem.
>>>
>>> The error is still:
>>>
>>> [dbd:error] [pid 18921:tid 140512673658624] (20014)Internal error:
>>>   AH00632: failed to prepare SQL statements: near "authn_query": syntax
>>> error
>>
>>
I have no clue as to why dbd isn'initializing. Untill someone can tell me
exactly how to get it working, I'm going to try the file method. Bummer!

-Tom

Re: [users@httpd] Re: Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Posted by Tom Browder <to...@gmail.com>.
On Wed, Apr 26, 2017 at 04:04 Luca Toscano <to...@gmail.com> wrote:

> > I think I just discovered I what the problem is: I'm using harp.js to
>> > build my site and the <form...> is compiling incorrectly.
>>
>> Well, that wasn't the problem.
>>
>> The error is still:
>>
>> [dbd:error] [pid 18921:tid 140512673658624] (20014)Internal error:
>>   AH00632: failed to prepare SQL statements: near "authn_query": syntax
>> error
>>
>
>  > DBDPrepareSQL "SELECT password FROM authn WHERE user = '%s';"
> authn_query
>
> Not a big expert with dbd but looking in
> https://httpd.apache.org/docs/current/mod/mod_session_dbd.html#dbdconfig
> it seems that you have a wrong prepare sql statement. Can you try something
> like:
>
> DBDPrepareSQL "SELECT password FROM authn WHERE user = %s" authn_query
>

Luca, I have to use the single quotes to get the sql to work.  The
semicolon is usually required by SQLite--I added it after I saw errors but
it didn't change anything.  But I will look at sql, single quotes, and
SQLite3 some more.

Do you see any other mistakes?

If you look back at the OP you will see the original error seems to be
failure to open the db. I'm going to try adding quotes on the params string
and also check file permissions on the dbd (just now thought of that).

Thanks very much.

-Tom

Re: [users@httpd] Re: Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Posted by Luca Toscano <to...@gmail.com>.
Hi Tom,

2017-04-26 3:23 GMT+02:00 Tom Browder <to...@gmail.com>:

> On Tue, Apr 25, 2017 at 14:47 Tom Browder <to...@gmail.com> wrote:
> >
> > On Tue, Apr 25, 2017 at 12:03 PM, Tom Browder <to...@gmail.com>
> wrote:
> > > Host: httpd version 2.4.25, Debian 8, 64-bit
> > >
> > > I am so close but getting the following error:
> > ...
> >
> > I think I just discovered I what the problem is: I'm using harp.js to
> > build my site and the <form...> is compiling incorrectly.
>
>
> Well, that wasn't the problem.
>
> The error is still:
>
> [dbd:error] [pid 18921:tid 140512673658624] (20014)Internal error:
>   AH00632: failed to prepare SQL statements: near "authn_query": syntax
> error
>

 > DBDPrepareSQL "SELECT password FROM authn WHERE user = '%s';" authn_query

Not a big expert with dbd but looking in
https://httpd.apache.org/docs/current/mod/mod_session_dbd.html#dbdconfig it
seems that you have a wrong prepare sql statement. Can you try something
like:

DBDPrepareSQL "SELECT password FROM authn WHERE user = %s" authn_query

?

Luca

[users@httpd] Re: Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Posted by Tom Browder <to...@gmail.com>.
On Tue, Apr 25, 2017 at 14:47 Tom Browder <to...@gmail.com> wrote:
>
> On Tue, Apr 25, 2017 at 12:03 PM, Tom Browder <to...@gmail.com> wrote:
> > Host: httpd version 2.4.25, Debian 8, 64-bit
> >
> > I am so close but getting the following error:
> ...
>
> I think I just discovered I what the problem is: I'm using harp.js to
> build my site and the <form...> is compiling incorrectly.


Well, that wasn't the problem.

The error is still:

[dbd:error] [pid 18921:tid 140512673658624] (20014)Internal error:
  AH00632: failed to prepare SQL statements: near "authn_query": syntax error
[dbd:error] [pid 18921:tid 140512673658624] (20014)Internal error:
  AH00633: failed to initialise
[authn_dbd:error] [pid 18921:tid 140512673658624] [client 76.3.0.179:43775]
  AH01653: Failed to acquire database connection to look up user
'Browder', referer: https://canterburycircle.us/login.html

I would appreciate any help.

Best regards,

-Tom

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org


[users@httpd] Re: Error trying to use 'mod_auth_form' and 'mod_dbd' with sqlite3

Posted by Tom Browder <to...@gmail.com>.
On Tue, Apr 25, 2017 at 12:03 PM, Tom Browder <to...@gmail.com> wrote:
> Host: httpd version 2.4.25, Debian 8, 64-bit
>
> I am so close but getting the following error:
...

I think I just discovered I what the problem is: I'm using harp.js to
build my site and the <form...> is compiling incorrectly.

I expect no replies until I can chase down the harp compile fix.

Thanks.

-Tom

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org