Member Avatar for munror

Hi everyone,

I have this select statement which works intermittently.
I'm using it to change reset the user password if they have forgotten it, however sometimes it shows that the password was reset and other times it says that the email address wasn't found in the database. This happens for the exact same email address which is definitely in the database.

[

strSQL = "SELECT broker_id, firstname, lastname FROM login_table WHERE email_address = '" & trim(Request.Form("email_address")) & "'"
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, con, 1

]

Any ideas? I'd appreciate any suggestions.


Cheers,

  • 2 Contributors
  • 2 Replies
  • 105 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by Baradaran

Dani AI

Generated

A few focused checks will usually find why the same email sometimes matches and sometimes does not. As described the intermittent match and as pointed out, case differences can be a factor — but there are other practical causes and easy diagnostics to run.

Log what the server actually receives and what the database contains. Record the exact Request.Form value (and its length/hex) and the query you execute. In MySQL you can inspect stored bytes to reveal hidden characters or encoding mismatches:

SELECT email, HEX(email) AS hex, LENGTH(email) AS bytes, CHAR_LENGTH(email) AS chars
FROM users
WHERE email LIKE '%the_address%';

Look for extra bytes, control characters or unexpected byte lengths (CHAR_LENGTH vs LENGTH matters for multi-byte charsets). To find rows that contain control characters do a quick regexp:

SELECT email, HEX(email)
FROM users
WHERE email REGEXP '[[:cntrl:]]';

If you find inconsistencies, normalize at write time rather than trying to patch selects. A single update (after backing up) can align current rows:

UPDATE users
SET email = TRIM(LOWER(email))
WHERE email <> TRIM(LOWER(email));

Other items to verify: ensure the connection character set matches the column charset (use SET NAMES or connection-string charset), check server/table/column collations (SHOW VARIABLES LIKE 'collation_%'; and information_schema.columns), and confirm your ASP code is not silently swallowing errors (check rs.EOF, recordcount where supported, and any RaisedErrors). Use parameterized commands from ASP to avoid quoting/escaping issues and to make comparisons predictable.

These steps will usually reveal whether the problem is input-related (hidden characters, case, encoding) or something else in the ASP-to-MySQL path.

Recommended Answers

All 2 Replies

Member Avatar for Baradaran

Hi,
have you tried to make the compare with either lcase or ucase of both items?

Member Avatar for Baradaran

The sql would look like this

strSQL = "SELECT broker_id, firstname, lastname FROM login_table WHERE trim(lcase(email_address)) = '" & trim(LCase(Request.Form("email_address"))) & "'"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.