[RESOLVED] Check User Existence

[TheBigB]

Hi guys,

Problem's this: I can't work with sql in php...
So, I was wondering if anyone could help me out with a simple(?) user existence script.

DB: subscription
Table: users
Field: username

I need something like a function which returns true or false
This is the code that I already have, but it isn't working

<?
	function querydb($query, $host, $user, $pass, $db)
		{
			$connid = @mysql_connect($host, $user, $pass) or die("Connection to the server failed");
			@mysql_select_db($db) or die ("Connection to database failed");
			$retval = @mysql_query($query) or die ("Query excecution failed");
			return $retval;
		}
		
	function checkuser($username)
		{
			$username = strtolower($username);
			$q = "SELECT * FROM users WHERE username = '$username'";
			$ret = querydb($q, 'localhost', 'root', '', 'subscription');
			while ($row = mysql_fetch_assoc($ret)) {
				$myret = $row["username"];
			}
			if ($myret != ''){
				//echo "Username is unavailable";
				echo $myret;
			}
			else{
				//echo "Username is available";
				echo $myret;
			}
		}
?>

Thanks for any help

[RobDog888]

Wouldnt it be that if $row contained anything other then a nullstring it would exist?

[penagate]

Don't call mysql_connect each time you execute a query, its pointless. Call it once at the start of your script and use the connection handle it returns for future queries.

$conn = mysql_connect($host, $user, $pass); 

Use is_resource to validate the results of MySQL API calls:

if (!is_resource($conn)) {
  die('Connection to database failed');
} 

Use SELECT DISTINCT in your query as there is only ever going to be one user with the same username (hopefully - you should make the username field an index as well):

$resultset = mysql_query(
  "select distinct password from users where username = '$username'"
);

if (count($resultset)) {
  // user exists; validate their user's password
  $row = mysql_fetch_assoc($resultset);
  if(md5($password) == $row['password'])) {
    // great success
  }
  else {
    // incorrect password
  }
}
else {
  // user does not exist - show an error
} 

Note that you should never use die() in a production situation; it is clumsy and unprofessional. Instead, you should use a graceful error handling system in its place.

[TheBigB]

Wouldnt it be that if $row contained anything other then a nullstring it would exist?

yeah, you're right
i was trying to turn it around to see if it'd make any difference, apparently not.
anyway, i would have found out sooner or later, but thanks.

@penegate:
it's not working yet:

<?
    $conn = mysql_connect('localhost', 'root', '');
    if (!is_resource($conn)) {
        die('Connection to database failed');
    }  
        
    function checkuser($username)
        {
            mysql_select_db('subscription');
            $username = strtolower($username);
            $q = "SELECT distinct id FROM users WHERE username = '$username'";
            $resultset = mysql_query($q);
            $row = mysql_fetch_assoc($resultset);
            if (is_array($row) == TRUE) {
                echo "Username unavailable";
            }
            else {
              echo "Username available";
            }  
        }
?>

$resultset always gives me Resource ID #3 (if it exists or not)
$row gives me 'Array ' when the user exists
i tried changing the if statement to accept 'Array ', but it didn't work

[TheBigB]

ok forget it;
figured i could use is_array so i did that
working code in previous post

[sunburnt]

Is there any reason not to do it like so (as I have done in a project I am working on?)

$res = mysql_query("SELECT DISTINCT `mcs_users`.* FROM `mcs_users` WHERE `mcs_users`.`name` = '$name' AND `mcs_users`.`password` = SHA1('$password')");

if (!$res || mysql_num_rows($res) == 0)
{
   // login failed
}
else
{
   // store user info in session
} 

[TheBigB]

Guess that would work too,
but as both work, what does it matter?

[sunburnt]

Guess that would work too,
but as both work, what does it matter?

I was just wondering if there was something insecure or iffy about the method I used, since I don't have that much experience with database/web design.

[penagate]

It's OK. However, I can't tell, from looking at the code, whether you've escaped the variables in the SQL or not. That is bad; unescaped data is a SQL injection vulnerability. You should use parameterised queries to avoid this. On PHP 4, look into MDB2; on PHP 5, use PDO, or mysqli if you're only ever going to connect to a MySQL source.