There's no real security issue that I know of with allowing white space in a username or password, as long as you have the proper code on the backend to avoid SQL injection attacks. That includes either calling a stored procedure to access the database, or using <cfqueryparam> tags in direct SQL (with <cfquery>).
I personally use trim() for usernames and passwords, both when they are creating them, and when they are using them. When copying from MS word, a web page, or some other programs, a little white space can automatically be copied as well. However, people won't realize this in a password field which just shows **********.
This is pretty much my query to check a username/password:
However if you do want to accept white space before and after, then just don't run trim() on the values that are provided to the database.
<cfquery name="checkUser" datasource="db">
SELECT username, password FROM users
username = <cfqueryparam value="#trim( form.username )#" cfsqltype="CF_SQL_VARCHAR">
AND password = <cfqueryparam value="#trim( form.password )#" cfsqltype="CF_SQL_VARCHAR">
Hope that helps.