0

I'm having this odd problem where I can store an email "test@gmail.com" into my DB that is either a varchar() or text() but I cannot select the row based off of the email. If I select the column via another identifier (say, a primary key called 'email_id') then I can display the email with the period in it. I can also select any row with email column with a varchar / text that does not have a period in it, such as "test@gmailcom".

I've replicated this on a test DB as well as my stage DB. This is also not a code issue because I've tested and replicated this when inserting directly into the DB from the query panel.

I've tried googling it but to no avail as well.

EDIT:

Am I using varchar() and/or text() correctly? Should it be something else? I've seen a few SO posts (cannot find them) stating to use VarChar.

For the record, I'm using mysql WorkBench for inserting / selecting, if that makes any difference.

EDIT 2:

Here are two records:

client_id (PK, AI, INT, UNSIGNED), email (varchar(500), not null)

in the table of client_login.

client_id = 1, email = 'test@gmail.com'

and 

client_id = 2, email = 'test@gmailcom'

The following select

select * from client_login where email='test@gmail.com'

returns 0 rows

select * from client_login where email='test@gmailcom'

returns 1 row

EDIT 3:

I can also do 

select * from client_login where client_id=1

and it will show me the row where email = 'test@gmail.com', which is odd because it's as if the period is in the row but it's not actually a period...

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
Jacob Kranz
  • 921
  • 3
  • 11
  • 24
  • 1
    Can you show the select statement you tried? – Tad Nov 27 '12 at 23:49
  • `VARCHAR(254)` should be good for the `email` column. Are you sure you're escaping the dot while inserting in the database? – inhan Nov 27 '12 at 23:58
  • Yea, I've tried that. Even so, do I need to escape the periods? http://stackoverflow.com/questions/1086918/what-characters-have-to-be-escaped-to-prevent-mysql-injections – Jacob Kranz Nov 28 '12 at 00:05
  • Are you sure it's a period character? What do you get with `SELECT HEX(email) FROM client_login WHERE client_id=1`? It should be `7465737440676D61696C2E636F6D` with the `2E` near the end representing ASCII `.`. Generally `VARCHAR` and `CHAR` behave the same way superficially, with `CHAR` simply having trailing spaces and being fixed length. `VARCHAR`is preferable in almost all circumstances. – tadman Nov 28 '12 at 01:34

2 Answers2

2

Frankly, I don't believe the problem is as stated.

Here's your "problem" running in SQLFiddle, showing that it works just fine.

I think the data in the email column is not what you think it is, especially the "dot" character.
Try updating it with hand-typed data.

To assist debugging this problem, try outputting the column value as hex using the HEX() function:

select HEX(email)

and carefully inspect the output.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • I'm doing all hand-typed data. Trust me, I wish I wasn't getting this either. I'm pretty sure you're right though in that the dot character is not what is being inputted. Any idea how I would go about trying to figure out what it actually is? – Jacob Kranz Nov 28 '12 at 01:47
  • Thanks. I've selected the hex at it is: "7465737440676D61696CA9636F6D". I've found an SO post about converting it back [here](http://stackoverflow.com/questions/8339138/how-to-convert-a-string-to-hex-and-vice-versa) but not sure if that'll help. How do I go about verifying that my periods are actually periods? – Jacob Kranz Nov 28 '12 at 03:36
  • The "dot" is hex `A9` or decimal `169`, but it should be hex `2E` or decimal `46`. It is probably that mysql is rendering extended ASCII chars as a dot. Update that row with a freshly typed in value and try to figure out how it got in there and prevent it happening again – Bohemian Nov 28 '12 at 04:30
  • I have an odd question: my collation is latin1-default. Would this have to do with the hex not being `2E` or `46`? I'm not well-versed in collations and not even sure how the collation became latin1 – Jacob Kranz Nov 28 '12 at 05:33
  • It was due to the column having an incorrect collation as well as one or two other collations being wrong. What a mess to fix though. – Jacob Kranz Nov 28 '12 at 07:58
0

Try to change the SQL mode of the server

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Andy Refuerzo
  • 3,312
  • 1
  • 31
  • 38