Topic: ODBC :: Empty username causes internal server error

[suspi]

I am using user authentication with the following components:

Apache/2.4.10 (Win32) + mod_auth_basic + mod_authn_dbd + mod_dbd + ODBC Driver + Microsoft SQL Server 2008(SP3).

It works fine except when I do not provide any username when browser asks for username and password. In this case I receive the "Internal Server Error" as the response instead of "Unauthorized" response.

The most interesting part of the error.log is:

[Sat Sep 27 21:23:17.639860 2014] [authn_dbd:error] [pid 1208:tid 1636] [client 127.0.0.1:1363] AH01656: Query execution error looking up '' in database [[dbd_odbc] SQLBindParameter returned SQL_ERROR (-1) at apr_dbd_odbc.c:604 [Microsoft][ODBC SQL Server Driver]Invalid precision value HY104 ]

The AuthDBDUserPWQuery directive in my httpd.conf is:

AuthDBDUserPWQuery "SELECT password FROM member WHERE CONVERT(VARCHAR(10), member_Index) = %s"

And the member_Index column in the database is smallint.

Is it a bug or am I missing something? Any ideas are welcome.

Thanx,

Radek

[James Blond]

That is becaue if there is no username %s is empty / nothing. So the query is not valid.

Can't you change the query that it works with an empty value?

[glsmith]

This is as designed.

dbd_odbc returns the error from the sql server to mod_authn_dbd
mod_authn_dbd returns AUTH_GENERAL_ERROR to mod_auth_basic
In mod_auth_basic all AUTH_GENERAL_ERRORs returns HTTP_INTERNAL_SERVER_ERROR to the server.

This I assume is to reflect the original error which was in the sql server as that is what erred on the empty username. This is commented in the auth_basic module before returning the 500.
/* We'll assume that the module has already said what its error
* was in the logs.
*/

[suspi]

I understand that an error raised somewhere between dbd_odbc and SQL Server is propagated to the user as the 500 - Internal server error. It is designed well.

I am trying to figure out what is causing the error. Assuming that the authentication mechanism is treating the username as a string and the username is empty it should use either an empty string '' or a NULL as an actual value for the %s parameter. In both cases the SQL query is valid.

I have no idea how to change the SQL query to handle the situation when "nothing" is passed as the username.

Any ideas?

[James Blond]

I'm not used to MSSQL, but in MySQL I would use single qoutes around the value

"SELECT password FROM member WHERE CONVERT(VARCHAR(10), member_Index) = '%s'"

[suspi]

Surrounding %s with ' does not work at all. Empty username causes the same error and a valid username ends with:
[Sun Sep 28 10:58:47.351564 2014] [auth_basic:error] [pid 1824:tid 1636] [client 127.0.0.1:2461] AH01618: user XY not found: /

I think dbd_odbc driver uses prepared SQL statements and it provides single quotes around string parameters automatically on its own. This is OK but something is wrong with empty values. I have tried many variants of the query but still no luck.