Make sure users can't have duplicate info

We have an issue with a new database we created. We want users to be able to create accounts but also not allow them to insert duplicate Usernames so that we don't have security problems. We used security assist to create the login page. We used the DataAssist insert record tools to insert it to the database, but we need to make sure that users can't have duplicate UserNames. How can we accomplish this? We had a DW insert record that checked for duplicate names but it doesn't work since we now have the DataAssist insert record...

Hello mrobben375515 to do this first go to your database table and add the unique option to the cell where the user name is then go to your registration page or where you add users and add a new recordset of table that has the users now filter it but form variable for the form that contains the login or unique name example if your form has login password adress name etc... you would filter the recordest with form variable login.

then in security assist add a new rule on the server side validation, choose validation type: number and server variable add your recordset total example $totalRows_userRecordset, this will ask you what kind of number select (internactionl 1000,00) on minimum place -1 and maximum 0 and on allow decimals 0, now go to the form field where users place their login and add next to it <span> Login name already in use </span> then select your span and go to the server behavoirs of security assist and select show if and add it there :)

Hope this helps
Sades

1. I made the filed unique in the data table. This alone will insure that we don't have dups, but the user only knows this after filling out the whole form. So we do was a SPRY note to display early in the registration process.
2. So we moved to your next suggestion, adding a behavior to open the user table. and filtering on the for variable.
3. We then added a new rule to SA for server side valadation, but this where is I am getting confused. I went to Security Assist/Access Rules Manager and create a new rule "ValidateUserLogin". I have selected the "allow" buttton, the value is the form field "login", the criteria is "<>" and the compare to is the table filed "login".
4. I added the line <span> Login name already in use </span> next to the form field "login".
5. I went to Security Behavior/Security Assist/Show Region and picked the Rule I created.

After posting, the form is not catching the duplicate name in the form entry process. When I hit "Register" I only get a white page with "Duplicate entry 'jrobben' for key 2" on it. Any suggestions?

I think what was being suggested is close to what you are doing but differs a little. Rather than using Security Assist to create the rule to check for the duplicate user you should be adding a server side validation with CSS Form Builder or Validation Toolkits server side validations.

The idea is that you have a recordset that is filtered on the entered value for the user, then with the server side validation you are checking that recordset to see if there are any rows in it. The recordset will only have rows in it if there are matches in the table. So this is why you apply the server validation for a number type and use the total rows of the recordset as the number to validate.

What this will do in the end is if the user enters a value that already exists this recordset will try to find a match, if one is found there will be 1 row in the recordset. Next the server validations will check the total rows to make sure that it is not greater than 0, if it is the validation will fail. You can then have your validation show if around the error message you want to display to the user to let them know that the value is already in use.

When adding a server side validation, do I use the "like as" to compare the two values?

1 value from the record set == 1 value from the form


Thanks

You will need to use the number validation and specify -1 as the minimum and 0 as the max. This way if there is even a single record in the recordset it will trigger this server validation.

So in the server validations box. It would be like this...

Validation trigger: SimpleRegistration_submit button press
Validation type: Number
Server variable: $totalRows_Recordset1
binded to Number ($totalRows_Recordset1)

I'm doing something wrong. This is my error message

"Notice: Undefined variable: totalRows_Recordset1 "

Also, I the name of my recordset is Recordset1. Filtered by UserEmail = post(formfieldname)

Thanks

<?php require_once('Connections/pawtabs_db.php'); ?>
<?php require_once("WA_ValidationToolkit/WAVT_Scripts_PHP.php"); ?>
<?php require_once("WA_ValidationToolkit/WAVT_ValidatedForm_PHP.php"); ?>
<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$WAFV_Redirect = "";
$_SESSION['WAVT_r_216_Errors'] = "";
if ($WAFV_Redirect == "") {
$WAFV_Redirect = $_SERVER["PHP_SELF"];
}
$WAFV_Errors = "";
$WAFV_Errors .= WAValidateNM($totalRows_checkRS . "",-1,0,0,",.",true,1);

if ($WAFV_Errors != "") {
PostResult($WAFV_Redirect,$WAFV_Errors,"r_216");
}
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$colname_checkRS = "-1";
if (isset($_POST['email'])) {
$colname_checkRS = (get_magic_quotes_gpc()) ? $_POST['email'] : addslashes($_POST['email']);
}
mysql_select_db($database_pawtabs_db, $pawtabs_db);
$query_checkRS = sprintf("SELECT UserID FROM users WHERE UserEmail = %s", GetSQLValueString($colname_checkRS, "text"));
$checkRS = mysql_query($query_checkRS, $pawtabs_db) or die(mysql_error());
$row_checkRS = mysql_fetch_assoc($checkRS);
$totalRows_checkRS = mysql_num_rows($checkRS);?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>

<span>
<?php
if (ValidatedField('r_216','r_216')) {
if ((strpos((",".ValidatedField("r_216","r_216").","), "," . "1" . ",") !== false || "1" == "")) {
if (!(false)) {
?>
email exists
<?php //WAFV_Conditional r.php r_216(1:)
}
}
}?>
</span>


<form method="post" name="submitReg" id="submitReg">
<input name="email" id="email" type="text" /><br />
<input name="password" id="password" type="text" /><br />
<input name="insertReg" id="insertReg" type="image" value="img/submit.jpg" border="0" />
</form>
<?php echo $totalRows_checkRS ?>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><a href="index.php">back home </a></p>
</body>
</html>
<?php
mysql_free_result($checkRS);
?>

It may of been my imagination, but the first time I ran this, it showed the email/password/submit like it should. When I entered a dup email, it gave me the error message. I'm thinking great! but now that message wont go away.

When I load the page I'm outputting the totalrows for my recordset. It's showing 0. Should the "Validation Show If" of hid this error message?

Is it getting stored somewhere?

I think you have got this mostly worked out, the only problem that I can see with your current page is that you have the validations above the recordset. This will not work correctly since the validations are referencing a variable that is created in the recordset code. To resolve this you should swap the validation server behavior code with the recordset code so the top of your page looks like this:

<?php require_once('Connections/pawtabs_db.php'); ?>

<?php require_once("WA_ValidationToolkit/WAVT_Scripts_PHP.php"); ?>

<?php require_once("WA_ValidationToolkit/WAVT_ValidatedForm_PHP.php"); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}



$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);



switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break; 

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

}



$colname_checkRS = "-1";

if (isset($_POST['email'])) {

$colname_checkRS = (get_magic_quotes_gpc()) ? $_POST['email'] : addslashes($_POST['email']);

}

mysql_select_db($database_pawtabs_db, $pawtabs_db);

$query_checkRS = sprintf("SELECT UserID FROM users WHERE UserEmail = %s", GetSQLValueString($colname_checkRS, "text"));

$checkRS = mysql_query($query_checkRS, $pawtabs_db) or die(mysql_error());

$row_checkRS = mysql_fetch_assoc($checkRS);

$totalRows_checkRS = mysql_num_rows($checkRS);?>

<?php 

if ($_SERVER["REQUEST_METHOD"] == "POST") {

$WAFV_Redirect = "";

$_SESSION['WAVT_r_216_Errors'] = "";

if ($WAFV_Redirect == "") {

$WAFV_Redirect = $_SERVER["PHP_SELF"];

}

$WAFV_Errors = "";

$WAFV_Errors .= WAValidateNM($totalRows_checkRS . "",-1,0,0,",.",true,1);



if ($WAFV_Errors != "") {

PostResult($WAFV_Redirect,$WAFV_Errors,"r_216");

}

}

?>