SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Having trouble with insert statement

    Hi Guys,
    I'm trying to insert records into a table that has a unique auto incrementing number as it's key.

    How can I insert a record into this table without interfering with the key?

    It's a simple user table with user id (key), username, password.
    I just want to insert to username and password and have the unique key incremented without me having to touch it.

    Here's the php I've got at the moment:

    Code:
    	//Search through the database to see if the username the user has chosen already exists
    	if(mysql_num_rows(mysql_query("SELECT username FROM users WHERE username = '$username'"))){
    		printf("Sorry, the username $username is already taken<br />");
    	}
    	else{
    		printf("Congratulations, the username $username has not been taken yet");
    		
    	//If the user input passes the filters and tests, then it is inserted into the database
    	"INSERT INTO users (userid,username,password)
    	VALUES ('',$safeUsername,$safePassword)";
    	echo "Record Inserted";
    	};
    What should I change about my insert statement?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    $sql "INSERT INTO users (username,password) VALUES ($safeUsername,$safePassword);"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you'll probably need quotes around the username and password values
    Code:
    INSERT 
      INTO users 
         ( username
         , password )
    VALUES 
         ( '$safeUsername'
         , '$safePassword' )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made the changes, but those alone didn't work.
    It turns out that it's because I didnt' surround the query with mysql_query()
    Stupid newbie mistake.

    Tested it and that incombination with your advise seems to have done the trick.

    Thanks a lot

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by r937
    you'll probably need quotes around the username and password values
    Yep, you'll defiantly be needing those along with mysql_real_escape_string().

    If not using bound values or parameters always at least use mysql_real_escape_string() to sanitize user input. Never directly embed unchecked user data into a query.

  6. #6
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah I've already taken care of the cleansing of the user input,
    I've got something like the following:

    Code:
    //Assign variables to username and password
    $username=$_POST['username'];
    $password=$_POST['password'];
    
    //Encrypt password
    	$encryptedPassword = md5($password);
    	
    	//Remove sql commands from the username and password, if they are contained in either
    	$safeUsername = mysql_real_escape_string($username);
    	$safePassword = mysql_real_escape_string($encryptedPassword);
    That makes $safeUsername and $safePassword completely safe right?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •