MySQL/PHP Query question

I’ve decided to create a comment script for a beginning project. Right now, I’m working on the registering part of the script. My problem is checking to see if the username already exists within the database. Here’s the code I’m using right now:


<?
$user = $_POST'username'];
$pass = $_POST'password'];
$check = $_POST'passcheck'];
$conn = mysql_connect(/* connect to mysql */);
if(!$conn){
 echo("Unable to connect to MySQL: ". mysql_error());
 exit;
}
$db = mysql_select_db(/* try to connect to database */);
if(!$db){
 echo("Unable to select database: ". mysql_error());
 exit;
}
 
/* ::: LOOK BELOW ::: */
$result = mysql_query("SELECT * FROM information WHERE user = '". $user ."'");
if($result != ""){
 echo("Username already exists in database.  Please choose a different username to continue.");
 exit;
}
/* ^^^^  LOOK ABOVE ^^^^ */
?>

I can connect to MySQL and the database just fine.

Two suggestions:

  1. Instead of using ($result !="") use:

(mysql_num_rows($result)>0)

  1. (Only try 2 if 1 does not work) Put double quotes instead of single quotes around the user in the query.

Now, I’m no PHP-guru, actually I’ve never used it before … but from my experience using perl/cgi I tend to be a bit paranoid. But safely so. The cardinal rule is never trust the user; taint check, taint check, taint check! Not being familiar with PHP, like I said, I don’t know if it’s possible to do any sort of attack against a script of this nature, but I suspect there is. (Granted the short snippet you show may not suceptible, it’s still a good idea to check your input.) E.g., if someone is entering a user-name make sure it only contains allowed characters and is correctly formatted. In perl, anyway, you’d be suprised how easy it is to pass ‘rm -r .*’ as an argument to something and watch for disastrous effects!

Just a friendly security reminder … it never hurts to start good habbits early (since you said you were learning PHP). Maybe someone more experienced can comment about specific security woes that you’ll have to look out for. But remember that paranoia is good.

Change

if($result != ""){
 echo("Username already exists in database.  Please choose a different username to continue.");
 exit;

to

 if($result){
  echo("Username already exists in database.  Please choose a different username to continue.");
 exit;

If you need any more MySQL or PHP help, email me, go to my team’s website (which was built by me using PHP/MySQL), or check out this project I’m heading, supermod.org.

Thank you all for your help. But while waiting for a reply, I was messing around with it, trying to see if I could fix it myself. I still have the same trouble, and here’s my code now:


$result = mysql_query("SELECT `user` FROM information WHERE `user` = '". $user ."'");
if(!$result){
 $result = mysql_query("INSERT INTO `information` (`user`, `pass`) VALUES('". $user ."', '". $pass ."')");
 if(!$result){
  echo("Unable to add user: ". $user ." to database.  Please try again.");
  exit;
 } else{
  echo("Thank you for registering <b>". $user ."</b>!  Please enjoy your stay here.");
 }
} else{
 echo("The username <b>". $user ."</b> was already found in the database.  Please go back and fix this problem.");
}

Anyone have any idea?

Did you try the mysql_num_rows suggestion above?

I just tried it, and it works perfectly. Thank you very much (man I wish I would’ve done that before…heh).