How to stop user from entering "fake" data?

[andy_b_1502]

Hi, i need to run this past you guy's. I need a way to effectivly check if a user has entered correct "real" details in my registeration form and check if email is already in my database to prevent the user from registering twice?

 

I need this as some unknown person is using the form and entering there details like this:

 

Username: 1

Password: 1

email: 1

 

It's really annoying as they are doing this several times so i have around 13 rows of just "1" in my table now! grr!! 

 

so far ive got:

 

 

Form:

<input name="upassword" type="password" class="style7" id="upassword">

<span class="style7">Email</span>

<span class="style7">:</span>

<input name="email" type="text" class="style7" id="email">

<span class="style7"></span>

<span class="style7"></span>

<input name="Submit" type="submit" class="style7" value="Register" />

</form>

 

 

Script:

<?php if ($db_found) {

$SQL = "INSERT INTO users (username, upassword, email) 
VALUES ('" .$username. "', '" .$upassword. "', '" .$email. "')";
$result = mysql_query($SQL);




header( 'Location: http://www.removalspace.com/index.php' );  exit();
}
else {
print "Database NOT Found ";
mysql_close($db_handle);
}
?>

 

But i need it to perform the checks aswell as drop the "real" data in to MySQL? Any ideas please?

[andy_b_1502]

If i use:

 

<?php $SQL = "INSERT INTO users (username, upassword, email) 
VALUES ('" .$username. "', '" .$upassword. "', '" .$email. "')";
$result = mysql_query($SQL);

$sql = mysql_query("SELECT * FROM users WHERE Email = $email");

  if($sql)
  {
    return true;
  }
  else
  {
    return false;
  }
  
}?>

 

i get: unexpected "}" so i delete that and get other errors.

[Psycho]

I don't see where you are accessing the values from the POST data, but you should be using trim() and mysql_real_escape_string() on that data. Then you need to implement as much validation as you want.

 

You should first validate that all required fields have valid values. What a valid value is - is up to you. You could do something such as making sure a name only contains alpha characters (and perhaps the dash and apostrophe). But, that only means the user needs to enter some meaningless letters to pass validation of the name. So, it doesn't really solve your problem of "fake" data, but it will prevent just a number being used as the name.

 

Once you have confirmed that the values pass basic validation, then you can verify that the email doesn't exist in your database.

 

Lastly, you could also require that the user confirm their email address. Create the user record with a unique code and set the record to unconfirmed, Then send an email to the email address the user entered with the unique code as a parameter. When the user gets the email and clicks the link set the user record to confirmed and let them access the content.

 

As to this

$sql = mysql_query("SELECT * FROM users WHERE Email = $email");

  if($sql)
  {
    return true;
  }
  else
  {
    return false;
  }

 

The email needs to be enclosed in quotes in the query. Plus, you don't check $sql, you should instead check the mysql_num_rows($sql) to make sure it is 0.

[xyph]

The easiest way to do this is to make fields like `username` and `email`  UNIQUE.

 

ALTER TABLE  `users` ADD UNIQUE (
`username`, `email`
)

 

You can then check if a user attempted to enter a duplicate entry. A common way is to first perform a select query, WHERE `username` = '{$_POST['username']}' OR `email` = '{$_POST['email']}'. A better way, IMO is to just perform the INSERT. MySQL will return a specific error if a duplicate row exists, and you can check for that.

 

<?php 

// Used for testing
$_POST['user'] = 'wrtauwth';
$_POST['email'] = 'aertywtrr';

// Create new database connection object.
$db = new MySQLi( 'localhost', 'root', '', 'db' );

$_POST['user'] = $db->escape_string($_POST['user']);

// Set up our query
$q = "INSERT INTO `users`
SET	`name` = '{$_POST['user']}',
	`email` = '{$_POST['email']}'
";

// Perform the query, save the results into $r
$r = $db->query( $q );

// Check if the query failed
if( $r === FALSE ) {
// Check if it was a duplicate error - http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html#error_er_dup_entry
if( $db->errno == '1062' ) {
	// Check if it was email or user. Not failproof, for example if $_POST['user'] = "key 'email'" and "key 'email'" already exists, the user will be told a duplicate email exists.
	if( strpos($db->error, "key 'email'") !== FALSE ) {
		echo 'Duplicate Email';
	} else {
		echo 'Duplicate Name';
	}
// Otherwise it was a bad query
} else {
	echo 'Bad query';
}
// Otherwise user was added
} else {
echo 'User inserted';
}


?>

[andy_b_1502]

Thannk you mjdamato and xyph.

 

@xyph when altering field username in phpmyadmin to unique, i get error message:

 

Error

 

SQL query:

 

ALTERTABLE`users`ADDUNIQUE (

 

`username`

)

 

MySQL said:

#1062 - Duplicate entry '1' for key 2 

 

@mjdamato your email validation of getting the user to click a link seems more the done thing these days but at this point in time also seems like a rigmarole to acheive in the time ive got.

[xyph]

@xyph when altering field username in phpmyadmin to unique, i get error message:

#1062 - Duplicate entry '1' for key 2 

 

You need to remove the duplicates before assigning the column as unique.