[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.