Empty result set

Hi,

I'm new to the forums and could really do with some help on the following...

I've got a mysql query which i'm running to log a user in to a secure section of a website. I've successfully managed this on another website, however with the query i'm running it simply just doesn't return any results even when i know it should, i've used the correct details etc.

Here is my query, which i'm running from the SQL tab within phpmyadmin.

SELECT customer_id, c_name FROM tdb_customers WHERE (email='$e' AND password=SHA1('$p')) AND active='active'

$e and $p are email and password which have been defined earlier in my php login script, but in order to make it work i'm using some information straight from the database:

SELECT customer_id, c_name FROM tdb_customers WHERE (email='[e-mail]' AND password=SHA1('test')) AND active='active'

when i run the query, i get the good old message:

MySQL returned an empty result set (i.e. zero rows).

I've tried breaking down the query and only running certain things such as only select the email address, and this works fine, i actually get a result. So i've come to the conclusion that its the password field which is causing me the problem.

I've used SHA1 to encrypt the users password in a 32 character string. Using a very similar query on a different site, it will 'decode' the password and return the users details as expected.

So im absolutely lost as to where i'm going wrong.

Any help would be greatly appreciated!

Cheers, Dan

[whiteyoh]

Hi,

Mysql cant read php. what exactly is $e? If thats your php variable you need to create a php script thats accessed from a browser.

If your information is encrypted then it will not pick up anything as all. I use MD5 encryption which is 1 way only. the php hashes it and then stores that in the database. When validating, ie when logging in, it hashes it, and checks that against the mysql entry.

mysql only stores and lists data, it doesnt understand code.

hope this help. or do you need an example?

Hi,

thanks for the reply. Sorry what i should have said was that the first snippet of code i gave was part of a php script, however to make use of it and run a query through phpmyadmin i changed the two variables $e and $p to real data, like so:

SELECT customer_id, c_name FROM tdb_customers WHERE (email='[e-mail]' AND password=SHA1('test')) AND active='active'

$e and $p (email address & password) were just variables that i set up in my php script and would be whatever the users details were if you get me?

I have delved slightly into MD5 for encryption, however since i got pretty much the exact script as above working fine for another project i thought i'd stay with SHA, as as far i know it works in the same way as MD5?

I hope i've explained myself better this time around, if your still not sure what im going on about i'll try explain further, however if your not sure on the way im doing it, i'm definitely open to your way of doing it (MD5).

Cheers Dan

[whiteyoh]

ill have a play around with it.

Here is my MD5 stuff, ENJOY.

if they seem insecure, i use a script in all of the logged in pages which checks for a valid session
5 files....

1. The registration form. on clicking register, it calls file 2.
2. register-exec.php process the login
3. login-form.php
4. Login-exec.php

5. forgot-password.php this doesnt remind the, its generates a new random password

REGISTER FORM

<form id="loginForm" name="loginForm" method="post" action="register-exec.php">
  <table width="300" border="0" align="center" cellpadding="2" cellspacing="0">
    <tr>
      <th>First Name </th>
      <td><input name="fname" type="text" class="textfield" id="fname" /></td>
    </tr>
    <tr>
      <th>Last Name </th>
      <td><input name="lname" type="text" class="textfield" id="lname" /></td>
    </tr>
    <tr>
      <th width="124">Login</th>
      <td width="168"><input name="login" type="text" class="textfield" id="login" /></td>
    </tr>
    <tr>
<tr>
      <th width="124">Email</th>
      <td width="168"><input name="email" type="text" class="textfield" id="email" /></td>
    </tr>
    <tr>
      <th>Password</th>
      <td><input name="password" type="password" class="textfield" id="password" /></td>
    </tr>
    <tr>
      <th>Confirm Password </th>
      <td><input name="cpassword" type="password" class="textfield" id="cpassword" /></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Register" />
	  <input type="button" value="Cancel" onclick="self.close()" /></td>
    </tr>
  </table>
</form>

REGISTER_EXEC.PHP USING MD5

<?php
	//Start session
	session_start();
	
	//Include database connection details
	require_once('config.php');
	
	//Array to store validation errors
	$errmsg_arr = array();
	
	//Validation error flag
	$errflag = false;
	
	//Connect to mysql server
	$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
	if(!$link) {
		die('Failed to connect to server: ' . mysql_error());
	}
	
	//Select database
	$db = mysql_select_db(DB_DATABASE);
	if(!$db) {
		die("Unable to select database");
	}
	
	//Function to sanitize values received from the form. Prevents SQL injection
	function clean($str) {
		$str = @trim($str);
		if(get_magic_quotes_gpc()) {
			$str = stripslashes($str);
		}
		return mysql_real_escape_string($str);
	}
	
	//Sanitize the POST values
	$fname = clean($_POST['fname']);
	$lname = clean($_POST['lname']);
	$login = clean($_POST['login']);
	$email = clean($_POST['email']);
	$password = clean($_POST['password']);
	$cpassword = clean($_POST['cpassword']);
	
	//Input Validations
	if($fname == '') {
		$errmsg_arr[] = 'First name missing';
		$errflag = true;
	}
	if($lname == '') {
		$errmsg_arr[] = 'Last name missing';
		$errflag = true;
	}
	if($login == '') {
		$errmsg_arr[] = 'Login ID missing';
		$errflag = true;
	}
		if($email == '') {
		$errmsg_arr[] = 'email missing';
		$errflag = true;
	}
	if($password == '') {
		$errmsg_arr[] = 'Password missing';
		$errflag = true;
	}
	if($cpassword == '') {
		$errmsg_arr[] = 'Confirm password missing';
		$errflag = true;
	}
	if( strcmp($password, $cpassword) != 0 ) {
		$errmsg_arr[] = 'Passwords do not match';
		$errflag = true;
	}
	
	//Check for duplicate login ID
	if($login != '') {
		$qry = "SELECT * FROM members WHERE login='$login'";
		$result = mysql_query($qry);
		if($result) {
			if(mysql_num_rows($result) > 0) {
				$errmsg_arr[] = 'Login ID already in use';
				$errflag = true;
			}
			@mysql_free_result($result);
		}
		else {
			die("Query failed");
		}
	}
	
	//If there are input validations, redirect back to the registration form
	if($errflag) {
		$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
		session_write_close();
		header("location: register-form.php");
		exit();
	}

	//Create INSERT query
	$qry = "INSERT INTO members(firstname, lastname, login, email, passwd) VALUES('$fname','$lname','$login','$email','".md5($_POST['password'])."')";
	$result = @mysql_query($qry);
	
	//Check whether the query was successful or not
	if($result) {
		header("location: register-success.php");
		exit();
	}else {
		die("Query failed");
	}
?>

LOGIN-FORM.PHP

<form id="loginForm" name="loginForm" method="post" action="login-exec.php">
  <table width="300" border="0" align="center" cellpadding="2" cellspacing="0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <img src="../images/logo.jpg" align="50%" />
    <tr>
      <td width="112"><b>Login</b></td>
      <td width="188"><input name="login" type="text" class="textfield" id="login" /></td>
    </tr>
    <tr>
      <td><b>Password</b></td>
      <td><input name="password" type="password" class="textfield" id="password" /></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Login" /></td>
    </tr>

  </table>
<A HREF="javascript:popUp('forgot-password.php')">Forgot password?</a>
</form>

LOGIN-EXEC.PHP

<?php
	//Start session
	session_start();
	
	//Include database connection details
	require_once('../config.php');
	
	//Array to store validation errors
	$errmsg_arr = array();
	
	//Validation error flag
	$errflag = false;
	
	//Connect to mysql server
	$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
	if(!$link) {
		die('Failed to connect to server: ' . mysql_error());
	}
	
	//Select database
	$db = mysql_select_db(DB_DATABASE);
	if(!$db) {
		die("Unable to select database");
	}
	
	//Function to sanitize values received from the form. Prevents SQL injection
	function clean($str) {
		$str = @trim($str);
		if(get_magic_quotes_gpc()) {
			$str = stripslashes($str);
		}
		return mysql_real_escape_string($str);
	}
	
	//Sanitize the POST values
	$login = clean($_POST['login']);
	$password = clean($_POST['password']);
	
	//Input Validations
	if($login == '') {
		$errmsg_arr[] = 'Login ID missing';
		$errflag = true;
	}
	if($password == '') {
		$errmsg_arr[] = 'Password missing';
		$errflag = true;
	}
	
	//If there are input validations, redirect back to the login form
	if($errflag) {
		$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
		session_write_close();
		header("location: login-form.php");
		exit();
	}
	
	//Create query
	$qry="SELECT * FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'";
	$result=mysql_query($qry);
	
	//Check whether the query was successful or not
	if($result) {
		if(mysql_num_rows($result) == 1) {
			//Login Successful
			session_regenerate_id();
			$member = mysql_fetch_assoc($result);
			$_SESSION['SESS_MEMBER_ID'] = $member['member_id'];
			$_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
			$_SESSION['SESS_LAST_NAME'] = $member['lastname'];
			session_write_close();
			header("location: member-index.php");
			exit();
		}else {
			//Login failed
			header("location: login-failed.php");
			exit();
		}
	}else {
		die("Query failed");
	}
?>

FORGOT PASSWORD

<?php

session_start();  // Start Session

session_register("session");
include '../connect.php';
// This is displayed if all the fields are not filled in
$empty_fields_message = "<p>Please go back and complete all the fields in the form.</p>Click <a class=\"two\" href=\"javascript:history.go(-1)\">here</a> to go back";
// Convert to simple variables 
$email = $_POST['email'];
if (!isset($_POST['email'])) 

{
?>
<head>
<link href="loginmodule.css" rel="stylesheet" type="text/css" />
</head>
<body>

<h2>Forgot your password?</h2>
<form method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
    <p class="style3"><label for="email">Email:</label>
    <input type="text" title="Please enter your email address" name="email" size="30"/></p>
    <p class="style3"><label title="Reset Password">&nbsp</label>
    <input type="submit" value="Submit" class="submit-button"/></p>
</form>
<?php
}
elseif (empty($email)) {
    echo $empty_fields_message;
}
else {
$email=mysql_real_escape_string($email);
$status = "OK";
$msg="";
//error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
if (!stristr($email,"@") OR !stristr($email,".")) {
$msg="Your email address is not correct<BR>"; 
$status= "NOTOK";}

echo "<br><br>";
if($status=="OK"){  $query="SELECT * FROM members WHERE email = '$email'";
$st=mysql_query($query);
$recs=mysql_num_rows($st);
$row=mysql_fetch_object($st);
$em=$row->email;// email is stored to a variable
 if ($recs == 0) {  echo ""; exit;}
function makeRandomPassword() { 
          $salt = "abchefghjkmnpqrstuvwxyz0123456789"; 
          srand((double)microtime()*1000000);  
          $i = 0; 
          while ($i <= 7) { 
                $num = rand() % 33; 
                $tmp = substr($salt, $num, 1); 
                $pass = $pass . $tmp; 
                $i++; 
          } 
          return $pass; 
    } 
    $random_password = makeRandomPassword(); 
    $db_password = md5($random_password); 
     
    $sql = mysql_query("UPDATE members SET passwd='$db_password'  
                WHERE email='$email'"); 
     
    $subject = "Your password at yourdomain.com"; 
    $message = "Hi, we have reset your password. 
     
    New Password: $random_password 
     
    http://www.silverlinksoftware.com/admin/login-form.php
    Once logged in you can change your password 
     
    Thanks! 
    Site admin 
     
    This is an automated response, please do not reply!"; 
     
    mail($email, $subject, $message, "From: your domainWebmaster<your email>\n 
        X-Mailer: PHP/" . phpversion()); 
    echo "Your password has been sent! Please check your email!<br />"; 
    echo "<br><br>Click here to href=\"javascript:window.close();\">close</p>";
 } 
 else {echo "<center><font face='Verdana' size='2' color=red >$msg <br><br><input type='button' value='Retry' onClick='history.go(-1)'></center></font>";}
}
?>
</body>

Hi, whiteyoh

thanks for all that code, alot of it is very similar to what i've already got. I'll try the MD5 method,

Just one question before i do so, regarding the database structure.

Atm i have a field called password and type=char(32)

Do i need to change anything, or should i be ok with what i've got?

[whiteyoh]

id say thats totally fine. all you would need to do is rather than say $password in php, you would say (might be slightly wrong syntax) $password(md5.(password)

that will create the hashed password which is then handed over to mysql for storage.

Ok thanks, i'll give that a go, might not be able to reply until later on as i have other work to be doing, i'll just PM you if required.

Thanks again!

Hi just a quick update, tried the MD5 function and all is working fine!

Cheers for your help whiteyoh.

[whiteyoh]

Happy days!!!!

[tough]

select name, REPLACE(format, ' – ', '/') as format, CAST(price AS UNSIGNED INTEGER) as price, CONCAT('http://www.example.com/',url_key) as url, id, delivery, sku from Database1 where delivery <> 'finished' INTO OUTFILE 'test.txt' FIELDS TERMINATED BY '\t';

this query returns empty result while

select name, REPLACE(format, ' – ', '/') as format, CAST(price AS UNSIGNED INTEGER) as price, CONCAT('http://www.example.com/',url_key) as url, id, delivery, sku from Database1 where delivery <> 'finished';

returns with results,
I am a new to databases and developement so need help.