Entires duplicate automatically

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shehan31
Forum Commoner
Posts: 59
Joined: Sun Aug 29, 2010 5:24 am

Entires duplicate automatically

Post by shehan31 »

Hi everyone.
I have a login form with two parameters (username and password). password is with md5 encryption. Also I have devoloped a system to enter users and pass word into this MYSQL database. Problem is when I enter a new user name and a password through the system that i have devoloped, each entry duplicates by many numbers(ex:4 and next time greater than that). Can some one help.

Code: Select all

<html>
 <!--<img src="logoad.jpeg" width="100%" height="140" /> -->
<div id="logo"></div>

<style>
#logo{
	
}
</style>
</html>
<?php
$connect = mysql_connect ("localhost","root","") or die ("error");
mysql_select_db ("guestbook") or die ("eroor connecting Database");
$queryget = mysql_query ("select * from login " ) or die (" error with table");
 	while($row=mysql_fetch_assoc($queryget)){
		
		$user=$row['user'];
		$pass=$row['password'];
		if (isset($_POST['Confirm'])){
		  $user1=$_POST['Username'];
		  $password1= md5($_POST['Password']);
		  
		  
		  if($user1==$user){
		  	echo"The Username exsist. Can't complete the entry.";
			
		  }else{
		  	   $querypost = mysql_query (" INSERT IGNORE INTO login VALUES ('','$user1','$password1')"); 
		       echo"sucessfully completed";
		  }
		}
	}
	
 
echo"
 <div id='second_table' style='float:left; width:800px; position:absolute; top:185px; right:0px;'>

 <form action = 'adduser.php' method='post'>
  
   <tr>
        <td>
               
        <font size='5' face='times new roman' color='18ff19'>New_Username</font>
        </td>
        <td>
        <input type='text' name='Username' maxlength='200'>
        </td>
   </tr>
    <tr>
        <td></br></br>
               
         <font size='5' face='times new roman' color='18ff19'>New_Password</font>
        </td>
        <td>
        <input type='password' name='Password'  maxlength='200'>
        </td></br></br>
   </tr>
   <tr>
       <td align='center' valign='top' colspan='2'>
	   <input type='submit' name='Confirm' value='Confirm' >        
       </td>
           </tr>
   </form>
   ";

?>

Regards
Shehan31
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Entires duplicate automatically

Post by McInfo »

The script retrieves all rows from the login table. It checks each row for a match to the submitted name. If a row does not match, a new row is inserted. Remember, this check occurs for every row. If there are four rows in the table that do not match, four new rows will be inserted. If the next name submitted does not match any of the existing rows, at least eight new rows will be inserted. The logic needs to be reconsidered.

The problem starts with the query "SELECT * FROM login". Selecting all rows and looping through them with PHP to find a match is very inefficient compared to using a more complex query. The query shown below returns much less data than the simpler query, but returns just as much information about whether a user with the given name exists.

Code: Select all

SELECT COUNT(*) FROM `login` WHERE `user` = 'Bob'
"Bob" can be replaced with the name submitted by the user through the form. Be aware that a user can just as easily submit malicious SQL as a valid name, so it is important to filter or sanitize user input before inserting it into queries. Often, PHP's mysql_real_escape_string() function is used for this purpose.

Also be aware that PHP once used a now-deprecated feature called Magic Quotes that automatically adds backslashes to user input. If Magic Quotes is enabled, disable it or conditionally (based on get_magic_quotes_gpc()) remove added backslashes from user input (stripslashes()). Don't strip them if Magic Quotes is disabled, though, because that will remove backslashes that the user intended to submit.

In addition to using PHP logic to prevent duplicate rows, add a UNIQUE index to the login table on the user field. This will ensure that no two rows will contain the same value in the user field.

Depending on the collation of your database table, it may be important to consider the case of submitted names. If the table is case-sensitive, "Bob" will not match "bob", which can lead to duplication.

Some other things to think about are:
  • using PHP's MySQLi (improved) functions;
  • altering the flow of the program to avoid using die();
  • making the HTML structure valid;
  • and using CSS instead of <font> tags.
shehan31
Forum Commoner
Posts: 59
Joined: Sun Aug 29, 2010 5:24 am

Re: Entires duplicate automatically

Post by shehan31 »

Thank you. I will go through it. :o
McInfo wrote:The script retrieves all rows from the login table. It checks each row for a match to the submitted name. If a row does not match, a new row is inserted. Remember, this check occurs for every row. If there are four rows in the table that do not match, four new rows will be inserted. If the next name submitted does not match any of the existing rows, at least eight new rows will be inserted. The logic needs to be reconsidered.

The problem starts with the query "SELECT * FROM login". Selecting all rows and looping through them with PHP to find a match is very inefficient compared to using a more complex query. The query shown below returns much less data than the simpler query, but returns just as much information about whether a user with the given name exists.

Code: Select all

SELECT COUNT(*) FROM `login` WHERE `user` = 'Bob'
"Bob" can be replaced with the name submitted by the user through the form. Be aware that a user can just as easily submit malicious SQL as a valid name, so it is important to filter or sanitize user input before inserting it into queries. Often, PHP's mysql_real_escape_string() function is used for this purpose.

Also be aware that PHP once used a now-deprecated feature called Magic Quotes that automatically adds backslashes to user input. If Magic Quotes is enabled, disable it or conditionally (based on get_magic_quotes_gpc()) remove added backslashes from user input (stripslashes()). Don't strip them if Magic Quotes is disabled, though, because that will remove backslashes that the user intended to submit.

In addition to using PHP logic to prevent duplicate rows, add a UNIQUE index to the login table on the user field. This will ensure that no two rows will contain the same value in the user field.

Depending on the collation of your database table, it may be important to consider the case of submitted names. If the table is case-sensitive, "Bob" will not match "bob", which can lead to duplication.

Some other things to think about are:
  • using PHP's MySQLi (improved) functions;
  • altering the flow of the program to avoid using die();
  • making the HTML structure valid;
  • and using CSS instead of <font> tags.
Post Reply