[RESOLVED] Simple Mysql Query Keeps returning blank

[KingWylim]

So this code seems to execute properly in all of my other query's and seems to execute this one since my mysql call function does not return any error's. However I can not seem to get my fname and lname to be pulled from the database to be displayed no matter how I try to call it. Any suggestions of what I may be doing wrong here.

I have dug through the rest of my code and do not see any other errors in it but would be happy to post it if I'm told that this query looks ok. And yes I have made sure that these two fields have data in them and can be decrypted through the phpmyadmin and it works perfectly there.

$query = sprintf("SELECT AES_DECRYPT(fname, '%s'), AES_DECRYPT(lname, '%s') FROM user WHERE email = AES_ENCRYPT('%s', '%s')", $salt, $salt, $input[0], $salt);
$newBox = new db_call;
$getName = $newBox->execute($query);

while($name = mysql_fetch_assoc($getName)){			
	$feedback = "<center>Loged in as ".$name["fname"]." ".$name["lname"]."</center>";
}
return $feedback;

[bradgrafelman]

What does $query look like if you echo it out? Also, what does this mysterious db_call::execute() method do (other than apparently return a MySQL result resource no matter what, since you never check to see if it returned anything else - pretty nifty trick if you ask me, since even the built-in MySQL PHP extensions don't even do that)?

[KingWylim]

If the query fails it gives my the mysql error and stops. So the query is working I'm assuming now I think the problem must be somewhere else in my code.

The mysterious mysql class and function. Downside of this function you can't use mysql_real_escape_string(); or at least I havn't figured out how to get that to work yet since you have to have a db connection established before you can call that function. I suppose I could use pconnect but I really don't like doing that.

class db_call {
var $s = "localhost";
var $db = "*";
var $un = "*";
var $pw = "*";

//Handles all Database query's.
function execute($query){
	$link = mysql_connect($this->s, $this->un, $this->pw)or die("Error: " . mysql_error());
	mysql_select_db($this->db, $link)or die ("Error: " . mysql_error());
	$result = mysql_query($query)or die ("Error: " . mysql_error());

	return $result;
}
}

Login function I left out the containing class

	//Confirms your a valid user.
	function login($email, $password, $ipaddress){
		$email = strtolower($email);
		$input = array($email, $password);

	//Get the salt for AES encryption for MySQL db
	$newBox = new db_call;
	$salt = $newBox->get_salt();

	//Hash's the Password.
	$hash = $this->hash_generator($input[0], $input[1]);

	$query = sprintf("SELECT is_confirmed FROM user WHERE email = AES_ENCRYPT('%s', '%s') AND password = AES_ENCRYPT('%s', '%s')", $input[0], $salt, $hash, $salt);
	$newBox = new db_call;
	$result = $newBox->execute($query);
	$num_rows = mysql_num_rows($result);

	while($row = mysql_fetch_assoc($result)) {
		$is_confirmed = $row["is_confirmed"];
	}

	if ($num_rows == 0){
		$query = sprintf("SELECT AES_DECRYPT(email, '%s') FROM attempts WHERE ipaddress = '%s'", $salt, $ipaddress);
		$newBox = new db_call;
		$result2 = $newBox->execute($query);
		$num_rows = mysql_num_rows($result2);

		if($num_rows <= 3) {
			//Logs failed login attempt
			$query = sprintf("INSERT INTO attempts (ipaddress, email) VALUES ('%s', AES_ENCRYPT('%s', '%s'))", $ipaddress, $input[0], $salt);
			$newBox = new db_call;
			$result3 = $newBox->execute($query);
			$feedback = "Your username and password did not match please try again. <br />";
			return $feedback;
		} else {
			$query = sprintf("INSERT INTO banned (ipaddress, email) VALUES ('%s', AES_ENCRYPT('%s', '%s'))", $ipaddress, $input[0], $salt);
			$newBox = new db_call;
			$result4 = $newBox->execute($query);
			$feedback = "Your account has been banned for to many attempted logins. <br />";
			return $feedback;
		}
	}

	if($is_confirmed == 1) {
		$query = sprintf("SELECT userid FROM user WHERE email = AES_ENCRYPT('%s', '%s') LIMIT 1", $input[0], $salt);
		$newBox = new db_call;
		$getid = $newBox->execute($query);
		while($newrow = mysql_fetch_assoc($getid)){
			$userid = $newrow["userid"];
		}
		$this->user_set_tokens($email, $userid);
		$query = sprintf("SELECT AES_DECRYPT(fname, '%s'), AES_DECRYPT(lname, '%s') FROM user WHERE email = AES_ENCRYPT('%s', '%s')", $salt, $salt, $input[0], $salt);
		$newBox = new db_call;
		$getName = $newBox->execute($query);

		while($name = mysql_fetch_assoc($getName)){			
			$feedback = "<center>Loged in as ".$name["fname"]." ".$name["lname"]."</center>";
		}
		return $feedback;
	} else {
		$feedback = "ERROR -- You may not have confirmed your account yet.<br />";
		return $feedback;
	}
}

[bradgrafelman]

Your problem is that your function is named incorrectly. Why call it execute() when it really should be named connectAndSelectDBAndExecute() ?

In other words, your function is doing too much. Connecting to the DB shouldn't be an action that must be coupled with executing a query. After all, you admitted that the current approach leaves a gaping security hole in that you can't properly sanitize user-supplied input before putting it into a SQL query.

EDIT: Another problem I just noticed...

KingWylim wrote:

So the query is working I'm assuming

Why assume anything? Why not output the SQL query string, visually inspect it, and perhaps even try executing it manually outside of PHP to see if it returns the results you expect.

Otherwise, if we're just going to be working on assumptions, then lets assume that your problem is caused by lunar tides and that you simply need to wait for the next phase of the moon. 🙂

[KingWylim]

I'll keep that in mind and make the changes for that to fix it shortly but can you see any kind of a problem on why the fname and lname should not be returning through my query. I have tested the $salt to make sure it works and it works on all other querys but for some reason it is returning blank for fname and blank for lname and suggestions. I know that there is data stored in the db which is my name Alex fname and Autrey lname. But for some reason it is not pulling the data which is odd.

I'm working on doing the query and echoing it in a separate file now I'll add it to this comment shortly.

[bradgrafelman]

Try that last bit I added in after replying:

bradgrafelman;10993201 wrote:

Why not output the SQL query string, visually inspect it, and perhaps even try executing it manually outside of PHP to see if it returns the results you expect.

[bradgrafelman]

Apologies for the double post, but I just noticed a glaring error here:

$name["fname"]." ".$name["lname"]

You never SELECT anything named fname or lname in your query, thus those indexes will never exist. Assuming any rows are being returned at all, try doing a [man]print_r/man on $name if you don't believe me. Perhaps you meant to give those two AES_DECRYPT() function calls aliases of fname and lname, respectively?

[KingWylim]

Hmm. Very strange indeed if i change the function mysql_fetch_assoc to mysql_fetch_array and specify $row[0] and $row[1] it works properly when I use mysql_fetch_assoc it returns an array like:

array("AES_DECRYPT(fname, 'salt')" => "Alex", "AES_DECRYPT(lname, 'salt')" => "Autrey");

any suggestions of why it's returning the AES_DECRYPT when using mysql_assoc instead of just fname?

[bradgrafelman]

KingWylim;10993208 wrote:

any suggestions of why it's returning the AES_DECRYPT when using mysql_assoc instead of just fname?

Yeah, you aren't SELECT'ing fname, you're SELECT'ing "AES_DECRYPT(fname, 'salt')".