Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

MySQL failed to select...help?

Hi,

This is going to drive me nuts.
I can't seem to select a particular email address in mySQL database.

I have this simple :

SELECT * FROM `User` WHERE email = "xxxxxxxx@xxxxx.ca";

Which returns a value.

But not this :

SELECT * FROM `User` WHERE email = "xx00xx@xxxxx.ca";

No error messages. Simply 0 result set. However, since the email address is top in the database, i know it exists. I am staring at it.
What could possibly be a common mistake I am doing?

Thanks,

Phil
0
NeverEndingFlashStories
Asked:
NeverEndingFlashStories
  • 4
  • 3
1 Solution
 
NeverEndingFlashStoriesAuthor Commented:
When going through the phpadmin, it looks like there is a new line after the email address.

I can delete the field through phpadmin, but i need to fix my select statement so that it will remove these kinds of emails when an admin goes through the CMS.

Is there any way to do a select that also selects any kinds of whitespace?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Just for test purpose, try:

SELECT * FROM `User` WHERE Trim(email) = "xx00xx@xxxxx.ca";

or even:

SELECT * FROM `User` WHERE email = "%xx00xx@xxxxx.ca%";

It is never a good idea to have function on the left side of = in Where clause. My though was to see if the email in the database accidentally is padded by blank spaces.
0
 
NeverEndingFlashStoriesAuthor Commented:
Hi eghtebas,

Thanks. Neither of those actually worked. It still returned empty result set. What are the other possible gotchas ?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mike EghtebasDatabase and Application DeveloperCommented:
"xx00xx@xxxxx.ca" is some variation to "xxxxxx@xxxxx.ca"

Are there some other variations so you can test to see how they behave. This way we may see the cause and effect a bit clearly.

This again is shooting in the dark. But this time it may reveal something new.
0
 
NeverEndingFlashStoriesAuthor Commented:
Hi Eghtebas,

I have tested SELECT against other values in the database.

Apparently, users with emails entered by a mass subscription script are all not selectable. Furthermore, using phpadmin edit mode, and phpstorm database tables edit mode, i can confirm that these users have 2 lines in their email fields.

On the other hand, users who self registered, can be selected, and have only 1 line for their emails.

I've dug out the script the original coder used, and it looks like he used the following script to mass subscribe users :

<?php 
	include 'common.php';

	$handle = @fopen("/var/www/staff/email.txt", "r");
	global $db;
	if ($handle) {
		while (($buffer = fgets($handle, 4096)) !== false) {
			$sql = "INSERT INTO User (fName, email, type, institution) VALUES(
				'User',
				'".$buffer."',
				'0',
				'Brock'
			);";
			//echo $sql;
			$db->query($sql);
			
		}
		if (!feof($handle)) {
			echo "Error: unexpected fgets() fail\n";
		}
		fclose($handle);
	}

Open in new window


What seems to be the problem with this insert Script?
How can i do a select statement that will select these emails irrelevant of whether there is an extra line or not in the email field?
If that is not possible, is there a way to remove the extra lines only from emails that has an extra empty line?

Thank you
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
But in that case I am surprised why the following doesn't work?

SELECT * FROM `User` WHERE email = "xx00xx@xxxxx.ca%";

------------------
Also, I think after identifying return character in MySQL (FYI, I use SQL Server not familiar much with MySQL), we can do something like:

SELECT * FROM `User` WHERE LEFT(email, InStr(email,ReturnChar) -1) = "xx00xx@xxxxx.ca";

I need your help to adapt something like LEFT(email, InStr(email,ReturnChar )-1) in MySQL.
0
 
NeverEndingFlashStoriesAuthor Commented:
Thank you !!!

This worked :

SELECT * FROM `User` WHERE LEFT(email, InStr(email,'\n') -1) = "xx00xx@xxxx.ca" ORDER BY `email` ASC
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now