Calling /user page with username instead of uid results in database exception due to failed string to int conversion

Question

When I use a username as an url parameter like: 'www.example.com/user/adam' instead of 'www.example.com/user/27' I get a database exception:

Error message PDOException: SQLSTATE[22018]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting the nvarchar value 'adam' to data type int.: SELECT base.[uid] AS [uid], base.[name] AS [name], base.[pass] AS [pass], base.[mail] AS [mail], base.[theme] AS [theme], base.[signature] AS [signature], base.[signature_format] AS [signature_format], base.[created] AS [created], base.[access] AS [access], base.[login] AS [login], base.[status] AS [status], base.[timezone] AS [timezone], base.[language] AS [language], base.[picture] AS [picture], base.[init] AS [init], base.[data] AS [data] FROM {users} base WHERE ( ([base].[uid] IN (:db_condition_placeholder_0)) ); Array ( [:db_condition_placeholder_0] => adam) in DrupalDefaultEntityController->load() (line 196 of C:\inetpub\wwwroot\includes\entity.inc).

I use pathauto which uses the following pattern for user account page aliases: users/[user:name] and that works fine. I just stumbled upon this error when testing the site before deployment. I'd rather not have this error visible to users which might try to navigate to /user instead of /users.

Since it only seems to be legal to use actual uid's when calling the /user/ subpage (aside from the login and logout pages etc.), what can I do to prevent drupal from trying to parse a username as an int and producing this error? Is there some redirect I can perform?

Answer 1

There are two sides to this question.

First, you should not get this error message. It should be good old 404 page or Drupal's error message about invalid argument. It's either a bug in database backend for MS SQL and belogs to their queue, or in your version of MS SQL RDBMS and belongs to Microsoft technical support. Or you simply need updates.

Second part is - out of the box only numerical URLs works.You neet to create aliases with username. It can be done automatically, Pathauto's default setting is exactly:

Pattern for user account page paths

users/[user:name]

Sadly, unless you get the first part fixed, it will only help with valid users, people will still get ugly message if they'll hit a name that does not exist, by typo in their address bar for example.