Author |
|
suspi
Joined: 29 Sep 2014 Posts: 3
|
Posted: Mon 29 Sep '14 15:56 Post subject: ODBC :: Empty username causes internal server error |
|
|
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 |
|
Back to top |
|
James Blond Moderator
Joined: 19 Jan 2006 Posts: 7364 Location: Germany, Next to Hamburg
|
Posted: Mon 29 Sep '14 17:36 Post subject: |
|
|
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? |
|
Back to top |
|
glsmith Moderator
Joined: 16 Oct 2007 Posts: 2268 Location: Sun Diego, USA
|
Posted: Mon 29 Sep '14 19:19 Post subject: |
|
|
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.
*/ |
|
Back to top |
|
suspi
Joined: 29 Sep 2014 Posts: 3
|
Posted: Tue 30 Sep '14 13:17 Post subject: |
|
|
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? |
|
Back to top |
|
James Blond Moderator
Joined: 19 Jan 2006 Posts: 7364 Location: Germany, Next to Hamburg
|
Posted: Tue 30 Sep '14 14:18 Post subject: |
|
|
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'" |
|
Back to top |
|
suspi
Joined: 29 Sep 2014 Posts: 3
|
Posted: Tue 30 Sep '14 20:01 Post subject: |
|
|
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. |
|
Back to top |
|